Search and auto fill

[Guitarcam87]

What I have:

2 tables

tblMaterialNo with fields ID, MaterialNo, MatDes
tblColorData with fields ID, MaterialNo, ColorNo, ColorDesc

WHAT I NEED:

I need to create a form that with allow the user to select the material number from a combo box I created using the table as the source, and have that automatically populate a text box with the ColorNo associated with that MaterialNo. The data returns will not be save in a database this is just to fill the form and print.

This has to be easier than I think but I have been using dlookup functions for hours to no avail. Please guide me in the right direction!!! Thank you in advance!

Cam

 

[pbaldy]

How about:

http://www.baldyweb.com/Autofill.htm

 

[Guitarcam87]

I actually thought about the the only trouble is this is only one portion. There are actually several tables with related info that I need to pull separately. For instance there is also a tblHandleData with the materials that have a handle. I will need a box to populate that info based on the material number selected in the combo. I should have mentioned that in the first post. Basically I have several pieces of info from diff tables that I need to all activate based on the selection in the combo.

 

[pbaldy]

Can all the data be included in the combo's row source, via joins?

 

[Guitarcam87]

I'm not sure how you would go about that trying that. Please do tell???

 

[pbaldy]

Hard to be specific without seeing the tables. Basically, include the tables in a query with joins on the appropriate field.

 

[Guitarcam87]

I actually tried that. The trick is that not all the material numbers have all the components from the other tables. When I add all the other fields to the query it only returns the material number that have all the components. I'll upload the db in a bit.

 

[pbaldy]

You have to edit the join to force it to return all material number regardless of whether they have a record in the other table(s).

 

[Guitarcam87]

How would I go about that?

 

[pbaldy]

In design view, double click on the join line. In SQL view, change INNER to LEFT or RIGHT as appropriate.

 

[Guitarcam87]

That worked perfectly thank you!

 

[pbaldy]

Happy to help!