[jimmerr]

Hello,

I am trying to create a form in Access that does the following.

The form has a drop down list of ID numbers (linked/pulled from a table)
The form has a button that will go to the specific ID number that is chosen on the drop down menu when clicked.
This will then display the TABLE showing 3 specific fields (including the linked ID number)

Please help if you can. Thank you.

 

[AlbertMC2]

Hi

Should the table just show the specific entity (ID with 3 attributes)?

 

[jimmerr]

Access 2007

 

[jimmerr]

This description might help more.

I have a table with multiple fields, but I want to create a form that pulls 4 of these fields using a drop down list and a button command. I assume a form is the best option.

I have created the drop down list that allows you to chose an ID(key) relating to the table. However, when I click the button I am only able to open the table and it does not go to the specific record.

I wish to choose the ID from the drop down, and have the button direct me to that specific record. If possible, I would also like to customize what fields show up during this search (as noted above). Please help. Thanks.

 

[AlbertMC2]

So you want a table to open that shows all the records but the cursor must be at that specific ID?
Or is is ok if the table opens and only that record with the specified fields display?

See the example and say if you want it differently.

 

[jimmerr]

I want to chose the Id in the form (from the drop down) and have it open up the table showing a select number of fields (not all the fields that are in the original table). Looking at your example that seems exactly how I want it. It seems you used some sort of criteria in the query, could you help me with that?! Thank you for everything.

 

[jimmerr]

I think I figured it out! I compared your inputs to mine and got it! The code you put as criteria in the query design, what is that called?

 

[AlbertMC2]

Hi

It seems you used some sort of criteria in the query

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

[Forms]![frmUser]![Combo5]

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:

 

[jimmerr]

I truly appreciate your help. Thank you VERY much!

 

[Dragoen]

Jimmerr,

I see Albert got you a solution but since I threw this together I thought I'd post it too. It has just one form with a selection field and then a subform under it that shows the record for editing.

enjoy,

 

[jimmerr]

Thank you for the example I appreciate it. So I go into Form1 and when I change it, is it suppose to populate vendor details? It doesn't seem to be doing anythign when I switch between the vendor names...

 

[Dragoen]

Yea, it's supposed to bring up their details when you select them. If you can check the bound column property on the combobox - make sure it is set to 1 and not 0. I don't have Access 2007 at home so I can't check it.

 

[AlbertMC2]

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:

SELECT Vendors.[Vendor Name], Vendors.Address, Vendors.CSZ, Vendors.Phone FROM Vendors WHERE (((Vendors.VendorID)=[Forms]![Form1]![cboVendor]));

instead of
SELECT Vendors.[Vendor Name], Vendors.Address, Vendors.CSZ, Vendors.Phone FROM Vendors WHERE Vendors.VendorID=me.cboVendor;

Or see example attached

 

[Dragoen]

Thanks for fixing that Albert, I don't know why it worked on my pc when I tested it.

 

[jimmerr]

Albert,

When I open your file I am getting an "Enter Parameter" box that says "me.cboVendor" when I attempt to open the form. When I go into design view I also cannot get the drop down to generate the data below. Is it now working for you?

 

[Dragoen]

Jimmerr,

Here is the corrected version with the fully qualified reference to the combobox in the query. You should be able to dbl click on Form1 to get the example to work. Sorry for the confusion.

 

[jimmerr]

Dragoen,

It works now thank you. I see that you used a sub form within Form1, but could you go into a little detail on how you actually created the code to pull in the data using the drop down? I am just trying to figure out how I could create such a thing in the future. Thanks for all your help.

 

[Dragoen]

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,

 

[jimmerr]

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


Where would I find these? I'm looking the the property sheet for the sub form and do not seem to see these items there...

 

[Dragoen]

Those are properties on the combobox, not the subform. Sorry for the confusion. The Build wizard exposes the properties of the control you are referencing even though you are working on a query for the subform. Those ones you highlighted are all associated with the combobox.

hth