Applying a combo box in forms using a related look-up table?

[agent46]

Greetings

I have two tables.

1) tblProductInfo
2) tblManufacturerList - a look-up table with about 200 manufacturers

The ManufacturerList table is related in a one-to-many relationship to the ProductInfo table using the ManufacturerID field in the ManufacturerList table.

I have created a data input form for manually inputting product information. Am having problems setting up the combo box to select the manufacturer for a product. No list appears and the ManufacturerID number does not populate the Manufacturer field in the ProductInfo table.

Would some good person help explain the best practices method to set up the combo box in my input form so that the manufacturer look-up list appears and a manufacturer can be selected. How can I get the correct ManufacturerID number into the manufacturer data field in the ProductInfo table? I have tried all kinds of different approaches and continue to get bad or no results.

As a side note, I have created a continuous form that shows all the product info records. I need to display the correct Manufacturer as a text field with no combo box in this form. I seem to have done this correctly but it may be one of the contributing factors to my problem.

If anyone is interested in helping me get this worked out, I would really appreciate it. If any of this is unclear, please let me know.

Many thanks in Advance
~46

[RuralGuy]

Have you used the ComboBox wizard to set up the cbo?

[agent46]

Yes. Originally, I used the combo box wizard to assign tblManufacturer as the content of the list. I did this when I created the tblProductInfo table.

However, this didn't seem to work as expected in the data input form immediately after I set the manufacturer field to display as a text field in the separate continuous display form.

My questions about this are:

1) Did I introduced some kind of error when creating the continuous record display form assigning the field as a text field?

2) Is it better practice to create the combo box in the data input form rather then in the table itself.

3) When I create the data input form, which table field should I assign as the Control Source to input the manufacturer? I'm assuming if I want to use a combo box, it should be the Manufacturer field in tbleManufactutrer list table. I'm trusting that the correct related ManufacturerID will then be automatically placed in the Manufacturer field in the tblProductInfo table - but it doesn't appear to work this way.

Thanks for Your Patience,
~46

[RuralGuy]

Here's something to read: http://access.mvps.org/access/lookupfields.htm

[agent46]

Is this article suggesting I not use a combo box with a look up table at all because it introduces indexing errors? I don't see a suggestion for an alternative strategy. It's important company names have the correct spelling if we hope to do accurate sorts and other queries. This is why a combo box is useful here.

Is the solution here - don't use combo boxes?

Thank you
~46

[RuralGuy]

ComboBox lookup's on a form are just fine. Lookup Fields in tables are the issue.

[agent46]

Thank You.

In that case. Could you clarify this for me? Steps for creating a simple combo box in a form.

1) Which manufacturer field should I place in the form? Is it the Manufacturer field from tbleProductInfo (which accepts the foreign key from tblManufacturer) or the Manufacturer field from tblManufacturer (which is the look-up list itself)?

2) How do I assign a combo box to that field in the input form? I can't seem to find simple instructions for this.

Thanks
~46

[RuralGuy]

Sorry ~46, I had to go to a meeting.
You're going to want to bind the ComboBox to the Manufacturer field of the tblProductInfo table. Then use the cbo wizard to help you put then Manufacturers PK into the tblProductInfo table.