Microsoft Access 2007 Form Button HELP

Hi

It seems you used some sort of criteria in the query

Click to expand...

Yes that is correct.
1. Create a new query and select the fields you want to display including the ID field.
Then you save the query.
2. Create a blank form and add a combo box that looks up the ID field in your table (you said you knew how to do this)
3. Then create a button on the form that opens the query. In the Button wizard you can choose "Miscellaneous" -> "Run Query". Select the query you created in (1) above.
4. Save the Form but take note of the name of the form and the name of the combo box (Note not the name of the label for the combo box but the name of the actual combo box).
5. Edit (Design view) the query you created in (1)
6. Under the ID field column in the Criteria row put
where frmUser = the name of the form created in (2) - (4) and
Combo5 = the name of the Combo Box created in (2)
Keep the same format as above just replace the name of the form and combo box.

Edit: Ahh it took me longer to type this than for you to figure it out :grin:

Hi

In Dragoen's example:
1. Edit Form "Vendor Details"
2. Go to Property Sheet of the form -> Data tab
3. In Record Source put in:
instead of
SELECT Vendors.[Vendor Name], Vendors.Address, Vendors.CSZ, Vendors.Phone FROM Vendors WHERE Vendors.VendorID=me.cboVendor;

Or see example attached

Hi jimmerr,
Here is a "little" tutorial on this common usage of a combobox and subform:

Start with the subform which is a control you drag down from the Design ribbon/Controls tab. Click on the upper left square on the subform frame and make sure the Properties window is open. Now select the Data or All tab in the Properties window and look at the Record Source property and click the ellipsis on the right side.

This opens the query designer where you can build a query for selecting what you want to show in the subform. You can right click the upper region and select Show Table... to bring up a list of tables and queries you can use as data sources for this query. Select the fields you want, and check (checkbox) whether you want them to display or if they are just used to help select or sort the results.

Next is where I screwed up earlier. On the Criteria line for VendorID, I referenced the ID value of the Combobox selection as Me.cboVendor. Me is an Access shortcut for referencing the current object (the subform in this case) but I meant it to mean the main form, so thats why it didn't work.

The "proper" way is to right click the criteria field and select Build... This utility wizard helps you build a properly referenced criteria object. In our example you would drill down thru Forms, Loaded Forms to Form1(the main form that contains the combobox). Then in the second column select the combobox - cboVendor, and in the third column doublclick <value> and hit OK.

This is where a lot of people get tripped up. The 'value' of that combobox is not usually what is displayed. The displayed characters are the "text" property further down the list in the third column. The value of the combobox is the "bound' column of the combobox, usually an ID value for the record. If you look at the properties for the combobox you will see Bound column, Column Count and Column Widths. These properties allow you to have an ID attached to a Text value in the row source for the Combobox, but if you set the column width to 0" for column 1, that ID value will not display, but we can refer to what the user selected by its hidden ID value.

Now back to the SubForm data source query... you should see the properly formated MicroSoft Access reference to the combobox in the criteria field...Forms![Form1]![cboVendor] . Note the use of bangs (!) instead of dots (.) to delimit the reference objects. I just let the build wizard do the syntax formatting for that stuff.

There you have it, that hooks up the plumbing between the combobox and the subform. Sorry this is a long read but I hope it answers questions for people.

enjoy,