Jun 2 2009, 08:49 AM
Im trying to call a form [Form2](which is based on a query) from a different form[Form1] using a combo box to select a value and a button to action the opening of the second form.
The trouble Im having is where and how to use the info from the combo box in the openform command on Form1, to ensure that I open Form2 using the reference that I picked in the combo box.
Hope that makes sense :-)
Help! Its driving me mad I know Im almost there, but somethings not quite right.....
Jun 2 2009, 09:07 AM
One way, change the query Form2 is based on so that it uses the value in the combo box on Form1 as its criteria value. This is probably the easiest way. All you need to do is open the query and type into the relevant Criteria cell the reference the combo box on Form1, like this:
Jun 2 2009, 09:14 AM
I already have what you suggested in the query [forms]![opening form]![CARNo], I also call form2 from Form1 with DoCmd.OpenForm Form-EditAction, , , CARNo
CARNo being the combo box
But it still comes up with the following message
Syntax error (missing operator) in query expression '04-EG-J-01-02'
04-EG-J-01-02 being the value from the combo box CARNo on Form1.
Am I confusing two different types of call maybe? or something like that?
Hope you can help
Jun 2 2009, 09:15 AM
fkegley has one answer, which I would modify by saying that if you want the form to show all records if there is no selection made in the combo, you can use the following syntax in the query:
[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null
(thanks to Dev Ashish at the Access Web).
If however you use the query underlying the second form for other purposes, and don't want to include criteria in it, you can use the Where option (Note: NOT the "Filter" option) of the docmd.openform method in the code opening the form. Your code would look something like this:
dim strWhere as string
strWhere="[YourFieldName]=" & me.combo1 'where YourFieldName is the name of the field you're filtering, and Combo1 is the name of the combo you're using
Note--check the number of commas--it may only be two...but the screen tip will tell you if you're on the where option or the Filter option.
Note, if your combo returns a text value, you have to delimit it, so the line would look like this:
strWhere="[YourFieldName]='" & me.combo1 & "'"
Jun 2 2009, 09:54 AM
Is the reference to the form control in the Criteria cell of the field that corresponds to the one that is the BOUND column of the combo box.
For instance, typically there is an ID field and a descriptive field. The ID field is hidden as it is meaningless to the user. All he sees is the descriptive field. However, the combo box is actually storing the hidden field. Therefore, the reference to the combo box needs to be in the table field that corresponds to the ID field, not the descriptive field.
Jun 2 2009, 09:56 AM
If you are going to do this via the OpenForm command, then you need something more like this:
DoCmd.OpenForm "Form-EditAction",,, "[FieldName] = " & Me.NameOfComboBox
Jun 2 2009, 10:09 AM
OK got it to work thanks :-) It was the query the form was based on that was the problem. That and I was trying to use filterAND a parameter query together, silly me!
However I have another question if you can still help, if I want to create a new record using this same method, eg if they can put the ref in the combo box and click the same button, what would I change in the code? or am I being too complicated now?
Thanks for all your help both of you :-)
Jun 2 2009, 10:56 AM
You're welcome. Both Ben and I are glad we could help.
If they put something in the combo box that cannot be found in the underlying table, then I believe Form2 would just open to a new record. I am not sure if the recordset is updatable, however.
Jun 2 2009, 10:59 AM
could I still pass information to the new record? For example I would like to make it a particular "Type" (not type of record "Type" is a field in the table) and use the information they input into the combo as the reference. Is this possible or way too complicated?
Jun 2 2009, 12:08 PM
Yes, you can do that. For instance, a bit of code in the Load Event of Form2, assuming Form1 is still open, can do that:
Me.NameOfControl = [Forms]![Form1]![NameOfControl]
Or if Form1 is going to be closed, you can use the OpenArgs argument of the OpenForm command:
Then in Form2, you need to move the value from OpenArgs to the control, again in the Load event:
Me.NameOfControl = Me.OpenArgs
Jun 4 2009, 01:26 PM
This sort of works, but I keep getting a "duplicate" error, even though the reference doesnt already exist.
The form I am using to update uses a query as the source for the data, would that matter?
The query links 2 tables.
Thanks for any help you can give me :-)
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here