Hey All,
I have a question regarding referencing a form using dates between.
Currently I have it setup as:
Between [Start Date] And [End Date]
on the query.
I want to reference a form, which i figured would be like this
Between [Forms]![frmSBReports]![txtStartDate] And [Forms]![frmSBReports]![txtEndDate]
But this doesnt work! :confused: What did I do wrong here? Any help as always is greatly appreciated!
Thanks!
Mateo
Hi,
My situation is hard to explain but i will give it a shot. What i have is a db with employees and its being used to track training sessions. These employees are split into 6 teams and those teams are split into 7 teams each.
I have written two queries, first asks the user what team to display. This data is used in the second query. The second query asks for what training item we are searching for. The result of the 2nd query is displayed in a form. Displaying of the information on a form is important. This way the user can verify that the members on this list are the ones he is updating. Now I wrote a third query that works off of the 2nd and updates the datecompleted field with todays date.
Problem is when i post a button on the form to show all these employees complete it referes back to the first query and asks all the filtering questions again.
I would like to know if there is any way to run an update query from a form that has already been filtered. Or if there is a way to reference these records in a new query so as to avoid all the filtereing again
hope i explained it well
Thanks
Ricky
I created tables that have lookup fields referencing another field. Actually I have several tables that all have relationships and object dependencies in my database. Now that I am trying to create some different reports, when I run the report I get the ID rather than the contents of the field. Also, I have a 'Report Dashboard' so to speak that I can run different reports from. On the form I have Combo boxes that reference one of the tables. I can generate the report showing the information, however I tried using a text box (criteria)(=[Forms]![Reports Form]![Combo47]+" County") in the header of the report to reference the selection made in the form combo box. When I run the report I generate the ID in the header.how do I get rid of the lookup fields in the tables, or is there another work around?
View 5 Replies View RelatedHey guys,
a quick question.
I have a form called "FormA"
within that form i have a tab control
and one of the pages is called "Page1"
inside that page1 i have a subform named "subform1"
and finally inside subform1 i have a field named "field"
what would be the correct syntax to reference the value of the field???
been messing with this for awhile and am getting a headace, was hoping someone can help
thanks alot
Ricky
Hi all,
Firstly I am trying to retrieve data from three tables in a format like this
Table Data Fields Descirption
Table 1 A,B PC Details
Table 2 B,C Software/PC
Table 3 C,1,2 Software Details
Where the B's are linked as are the C's and these both form primary keys in their respective tables.
My form has on sub form in it. The main for refers to Table 1, displaying Fields A and B. My subform is linked through child/master fields by B and is used to edit which software titles are installed on each PC. However, as C, the primary key for the software, is a code, it is not obvious what it refers to exactly. Therefore I use a combo box who's row source is query containing the software title (1) and the column widths precceding the software title are set to 0 so that software title (1) is visible in the field in the subform. Hence, although you change the title name on the form, the actual change is made in Table 2 altering the Software Code (C). This is fine, except that there is more than one version (2) of some of the software titles. The versions are shown in the drop down list for the software title combo as an extra colum, so you know you are choosing the correct one. But i also want a seperate field which actually shows the version chosen. I cannot link a normal text box to the same query as it doesn't have the relevant properties, but I can do it with a combo as I am effectively doing the same as for the software title.
This is all very well, I could just lock the combo box and be done with it, but it doesn't look very professional and is a bit confusing to the user. Is there away that I can get around this problem?
Cheers,
Matt
Hello,
I am trying to write a simple query that references calculations in the same query - but I keep getting the "enter parameter value" box.
Basically, I have some columns in a query calculating average and sum values for a column. I would then like to multiply some of those columns together in a new column, using an expression like this: =[AvgOfColumn1]*[SumOfColumn2]. Access doesn't want to do this for me. It seems very simple so I'm sure I'm missing something!
Thanks!
I have a split database that is working pretty well, but I've been asked to add a functionality to it. I have a table, product1, that contains basic product information. One of the values is a "class", in that if a product was created at a certain time, it fits into a class; the other value in this table that is applicable is the time the product was created.
I have another table, class1, that lists the beginning and end times of the class periods and the class numbers.
So, my users will "ingest" a product, including the time the product was created. I need the query (i think so anyway) to fill in the "class" value in product1, by determining which class it is in by seeing which class beginning and end time it is between in class1 and using the class number found in class1.
I have a form that I want to filter by different fields on different occasions. i.e., I might look at all records that have a review date of earlier than today on one occasion and I might look at all records that have a Yes in a Yes/No box on another occasion (date then being irrelevant.)
I have finished the form which has a query as it's Record Source.
The only way I can figure out how to accomplish my goal is to copy/paste the form with a new name, copy/paste the query that is it's record source, with a new name, and then put the criteria I want in the new query and make that query the new form's record source.
Is there a more elegant way I can do this. For example. I have a form consisting of buttons that I use as a Navigation Form. Could I, in the VBA that opens the form when I click on it's button, stipulate which Record Source the form would use?
I have a form and subform with the following names:
frmBasic
sfrmCustomers
I have a unbound txt field called txtCustName on the mainform
I want to populate this field from a field on the subform called CustName when the record is selected.
I also have an unbound combo box with the cust ids using the row source from the tables record source from the main form.
I tried the following Code:
Private Sub cboCustNum_AfterUpdate()
Me.Filter = "[Cust_ID]=" & cboCustNum
Me.FilterOn = True
Me.txtCustName = Forms.frmBasic.sfrmCustomers.Form.CustName
End Sub
This didn't work. So I put "= [Forms].[frmBasic].[sfrmCustomers].Form.[CustName]" in the control source of the text box on the main form I want populated.
The name doesn't appear in the field. I get #Name? in the field.
Hello - am hoping someone can help with the below query.
I have a database with two linked tables. Both of them contain a 10-digit ID number. One table (Table 1) has this ID stored as text; the second (Table 2) as a double.
I need to find records in Table 2 that are not in Table 1. I first created a stored query that converted the Table 1 ID into a double using the function CDbl([ID]). I then created a new query left-joining the stored query on Table 2 against the ID. The query displays the stored query's converted ID.
I run the new query. In the results - if there's a match, the ID successfully appears. But where there's no match - instead of a Null or an empty string, "#Error" appears. Can anyone advise why this is, and how I can work around it?
Thanks!
I have a form - purchase, and a subform purchase detail they are linked with a field "puid"..I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.
I used the control source code:
=DMax("[forms]![purchase]![purchasedetailds]![pdID]","[forms]![purchase]![purchasedetailds]","[Forms]![purchse]![purchasedetailds]![puid]=" & [Forms]![purchase]![puid])
and the simpler:
=DMax("purchasedetailds]![pdID]","[purchasedetailds]","[purchasedetailds]![puid]=" &[puid])
I have a form (named frmAddSession) with a combo box (named Band_Name) and lets say i have it displaying the band "Band A"
I have a query where the criteria is [Forms]![frmAddSession]![Band_Name]. when i run the query the results are blank. but if i type ="Band A" it works just fine.
why won't my reference to the combo box on the form work?
In a series of deductions to try and figure this out, my form only has the one combo box, and my query is based on only one table and only has the one field.
I have an issue with a BeforeUpdate procedure that I am trying to get working. I'm using simple elements here as this is a test DB, not the real one yet.
I'm trying to get the value from a Text Box TextData into a string cString. I've used a Dim statement to declare cString - I think the problem I am having is trying to get the actual data from the form. The error I am getting is:
Run Time error '2465'
Microsoft Access cannot find the field "|1" referred to in your expression.
The code I am using is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim cString As String
cString = [Forms!FormData!TextData]
End Sub
(I've removed some code that is either comments or working since I have to retype things!)
The line cString = [Forms!FormData!TextData] is where the debugger highlights as the error.
In short, here is what I am trying to accomplish:
1. I have a two-tier, tabbed Navigation form (Menu-Main).
2. One of the second-tier tabs opens a form (VendorMain) which displays a Datasheet based on [Query.Vendor_Query]
3. VendorMain contains a Command Button (EditButton), which calls a subform (VendorForm) used for editing.
4. Upon saving the edits, VendorForm's OnClose event is supposed to Requery the datasheet on VendorMain, so the edits are reflected when the user returns.
I have tried over a dozen different permutations of the following, but nothing seems to work:
Me!Parent.Requery
Forms!VendorMain.Requery
Forms![Vendor_Query].Requery
Forms!Main-Menu!VendorMain.Requery
Etc...
VendorForm opens, edits, saves and closes perfectly. However, I consistently get the following error and the parent form is not requeried:
Runtime Error 2450 - Cannot find the referenced form VendorMain
Code available upon request.
I have a complete working timeclock and trying to add the forms within the navigation form. I know I have to go back and re-reference everything and have already done so with the queries. But I cannot get the setfocus to work.
Here is the setup.
NavigationForm (navigation form)
frmClockIn (top form)
frmClockIn_SUB (bottom form)
Open Navigation > Time Clock.
Select an EmployeeID from the dropdown
Click on clock in. (successful)
cboEmployeID goes blank and try selecting the same employeeID to clock out and I get a debug at
Me.frmClockIn_SUB.SetFocus
I think there is a problem with my cboEmployeeID_AfterUpdate() as well.(related?) which used to be
Private Sub cboEmployeeID_AfterUpdate()
me.refresh
End Sub
I have replaced "me.refresh" with "Me.frmClockIn_SUB.Form.Refresh"
(It used to break here, but not after I changed it.)
Not too sure where to set these forms to in the code
I have attached a blank copy ....
The database I have created is to produce product description sheets. From the switchboard, the user clicks a button that opens a form on which they can enter about 5 bits of information , then select the product they wish to produce the product sheet for and when they click a button, the product sheet is produced. The product sheet is a report with a subreport. The information entered on the form is included with more information from the database on the main report and the subreport is based on a union query which combines information from 2 tables to list the items included in this product. (the information the user inputs into the form is information that changes depending on the situation and is not stored in the database.
Now comes the next step in this process. Just as the information input into the form is temporary information, there is also one bit of information that needs to be input on a temporary bases for the items that are listed on the subreport. Each of the items on this subreport needs an assignment of AAA or BBB that needs to appear in the subreport on the line with that particular item. There can be one item in an product, or there can be many and the listing of AAA or BBB can be different from one item to the next or they could all be the same. The assignment needs to be made on an item basis.
My thoughts on this were to have a subform appear once the user selects the product on the form which would list all of the items and allow them to select either AAA or BBB in a column on this form. Then when the click to open the report, those assignments would carry through to the report.I have been able to create a continuous form displaying all of the items based on the union query but that's as far as I can get. Not even sure if that is the way to go.
Is there a way to reference the AAA and BBB input on the subform I described so that it displays in the report.
Hi,
I am trying to access one form from another. the second form loads correctly and filters the records that I want to see but when I want to add a new record on the second form by wanting to repeat the key value from the main form it is not showing that value. i have set the property of the key field on the subform to be equal to the key value from the main form using
[Form]![Complaints Inbound]![Access #]
what could be wrong?
can anyone help?
thanks!
mozart
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
Hi guys, I think that I am being dumb but here goes.
I need the query associated with a Combo Box row-source to filter records based on another field on the form.
I can get this to work just fine if I explicitally include the form name in the query, ie SELECT Client.Client FROM Company INNER JOIN Client ON Company.Company = Client.Company WHERE (((Company.Company)=[forms]![allcalls]![company])); My form name is AllCalls.
However, I will be having many similar forms so do not want to reference the form by name. I am sure that the form can be referenced by me! or something like that but try as I might I cannot get it working.
Am I making sense? and does anyone have a solution?
Cheers
Wayne
I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.
Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.
My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.
I have sequence field in query and want it show in form.
Please help me. Thanks
I have a Form that has a subform and in that there is a subform that runs a query. I need to take the value of in one of the fields returned in that query and transfer it to a field in my original subform.
View 4 Replies View RelatedI know this should be very simple, but I've already tried every possible entry from the site given in the FAQ section of this website (http://www.mvps.org/access/forms/frm0031.htm), but it keeps access keeps asking for a parameter value!!
i have a subform(frmSub2) on a subform(frmSub1) on frmMaster.
I have a combo box (cbo2) on frmSub2 that grabs its value from the value chosen from another combo box (cbo1). Here's what I have tried in the query builder (none work!! why?????):
-[forms]![frmMaster]![frmSub1].[form]![frmSub2].[form]![cbo1]
-[forms]![frmMaster]![frmSub1].[form]![frmSub2].[form].[cbo1]
-[forms]![frmMaster]![frmSub1].[form]![frmSub2]![cbo1]
-[forms]![frmmaster]![frmSub1].[form]![frmSub2].[cbo1]
Please help!!
I want to be able to reference a variable dependant on where I am within a loop. I have myct1, myct2 and myct3 looping then a mycount loop and I want to add a record to a table taking the relevant myct value dependanct on the current mycount value. Any ideas anyone?
Here is my code:
for myct1 = 1 to 3
for myct2 = 1 to 3
for myct3 = 1 to 3
for mycount = 1 to 3
mytable.addnew
myvalue = <if mycount = 1 then myct1 etc>
mytable.update
next mycount
next myct3
next myct2
next myct1
Any help most appreciated.
I have code on a subform that is referencing another form that is still open (not the parent form, but the form which's button openned the form that the subform is on). I'm trying to get the value from a combobox that contains the data that will filter the subform in question. See below.
==========================================
Dim strForm As String
Dim strSQL1 As String, strSQL2 As String, strWhere As String
Dim strSelect As String, strFrom As String, strJoin As String
strSelect = "SELECT tblMainData.* "
strFrom = "FROM tblMainData "
strJoin = "INNER JOIN tsubPermissionList ON tblMainData.ResponsibleParty = tsubPermissionList.FullName "
strWhere = "WHERE(((tsubPermissionList.UserID) = [Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)))"
strSQL1 = strSelect & strFrom & strJoin
strSQL2 = strSelect & strFrom & strJoin & strWhere
If IsNull([Forms]![frmMainEntry].Form.[cboSelectUser].Column(0)) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = strSQL1
Else
Me.RecordSource = strSQL2
End If
==========================================
I can't resolve the error:
==========================================
Runtime error '3085':
Undefined function
'[Forms]![frmMainEntry].Form.[cboSelectUser].Column' in expression.
==========================================
Somehow access thinks that the references to the combobox is a function. Can someone help me with this?
Thanks,
PC
When clicking on a command button on a form in order to open another form and go to a specified record, i used to use a code similar to the following:
DoCmd.OpenForm "frmName", , , "[FieldName]=" & Me.ControlName
Is it in the same format the above statement if the form to be opened has a tab control ?
Thanks