Form with parameter query subform

[shiphtfour]

Hello,

I am trying to create a form that uses a combo box as the criteria value for a query that is displayed in a subform below the combo box. Because I am new to access, I can't quite figure it out.



Given the example of using the form to enter product quantities, I would like the user to be able to select the company from a combo box, then this would auto-update a query contained in a subform below the combo box to display all the products made by that company. The user would then select the product in the subform and enter the quantity in a textbox below the subform.

My purpose for the form is not the same as this, but for the sake of simplicity, this scenario is easier. Is this possible?

So far, all I have figured out is that Forms!Form1.ComboBox (with the correct names replaced of course) goes in the criteria value under the same field that the combo box contains. Any help is greatly appreciated.

[TheShabz]

You would create the query for the subform as if you wanted to get info for all the companies. In the design view, for companyID, you would enter Forms!frmName!comboName . Then you create a query that will pull company IDs and names and bind the combo box to it. Hide the column with the IDs but make sure the value held is the ID. Now go into the AfterUpdate event of the combo box and type in Me.subformName.Requery.

That should do it.

[shiphtfour]

Thanks for the help TheShabz. I still am a bit confused though, I'm fairly new to access. How do I "bind" the combo box to the second query? Would I put SELECT "[Companies Query].CompanyName FROM [Companies Query] ORDER BY [CompanyName];" in Row Source? My companies query has both CompanyID (Hidden) and CompanyName. How do I make the value held CompanyID? Also, when I add Me.subformName.Requery to AfterUpdate, I get the error message "Microsoft Access can't find the Macro 'Me.'"

[TheShabz]

As far as binding the combobox to the query, create a new combobox and the wizard should pop up allowing you to do all the things i described. you should have the query saved as a separate query. Then you just select it and the wizard should take care of all of that for you.

With the AfterUpdate, my mistake, I didn't explain it well enough. Click on the elipses (...) next to the box and choose "code builder" the VBA window should pop up with a section that looks like

Private Sub objectName_AfterUpdate ()

End Sub

make it look like:

Private Sub objectName_AfterUpdate ()
 
Me.subformName.Requery

 End Sub

[shiphtfour]

Sorry, but I still can't seem to get it to work. If I put Forms!Products!CompanyName under criteria of CompanyID on my first query, I get zero results, which makes my second query also have zero results... I think I am doing something wrong here.

[TheShabz]

What is CompanyName holding? the companyID or the name? Make sure they match up.

[shiphtfour]

Row Source = SELECT [Companies Query].CompanyName, [Companies Query].CompanyID FROM [Companies Query] ORDER BY [CompanyName];

Is this the information you are looking for? I'm not sure what you mean by "hold."

[TheShabz]

In the properties of the combobox, check the "bound column". If it's 1, make sure your Forms!.... is in the [Companies Query] criteria for CompanyName. If it's 2, make sure its under the CompanyCompanyID.

[shiphtfour]

It is 1, but if I put Forms!... in the criteria for CompanyName under Companies Query, I get no choices in the combo box (which makes sense to me because how could I be filtering the list I'm choosing from?). Is there maybe a sample form that has a similar setup somewhere that I can look at?

[TheShabz]

nonononono. the Forms!.. part goes in the query for the subform, not for the combobox.

[shiphtfour]

That's what I thought... now I seem to have done something to make access not open my form anymore...

[shiphtfour]

I was able to get it working, thanks! (At least the design, haven't figured out how to actually use the subform to input records yet.)