Help with Dropdown lookup populating other fields.

[tim1234]

I have a database that keeps track of inspection records. What I want to be able to do is open the form Inspection, and select a part # in a dropdown. When I do that I want it to fill in all of the critical item/dimension boxes on that same form for that part number that I selected above. I tried to figure it out from the sample database example on here, but I just cannot figure it out. Any help is greatly appreciated. A copie of my database is attached.

 

[trucktime]

Box

In the After Update Event of the Combo Box you need to put some code like this:

Me.txtfield1 = Me.Comboboxname.Column(0)
Me.txtfield2 = Me.Comboboxname.Column(1)
etc.

Replace 'txtfield1' with the actual name of the text box you need to populate
Replace 'Comboboxname' with the actual name of the combo box

The Column count starts at 0.
So you have to match up the txtfields with the right columns.

 

[tim1234]

Wher do I put it?

Do I use the code builder or use an expression? Also, how do I know what the columns # is? Is the column number something in the text fields properties in the form? Thanks for your help!

 

[trucktime]

Box

Open the form in Design View.
Select the Combo Box and go to Properties.
On the Data Tab go to Row Source.
Click on the button with the 3 little dots.
Now you will see the Query.
The far left column is Column(0), in this case Supplier is Column(2).


As for the After Update Event.
In Properties go to the Event Tab and select After Update.
Again click on the button with the 3 little dots, it will open the Code Window.
First and Last line are already there, put your code between them.

I put one line there for you.
When you select an entry in the Combo Box the Supplier field will be
populated.
Based on this example you will able to figure out how to do the rest.


Good Luck.

 

[tim1234]

OK, I understand that part.

I now understand what you have done to populate the other fields. My problem now is that I want to have the part number be selected on the form using a dropdown (as it does in example) and populate the other text boxes (as it does in your example). The problem is that when entering a new record it then adds is to the dropdown for part # since it is in the same query.

If I want to be able to select the PART# and it populates the CRITERIA fields, but does not add the record to the PART # source. I'm not sure what I need to set it up like. I have tried two tables (one with part # and criteria) joined to another table (receipt) using part # as primary key on part # table and primary key part#/datereceived on the receipt table. Created a form and still couldn't get it to work right. I also tried creating a query that uses both tables and then created a form based on that query but everything adds to the list that I don't want to update, (the source list). I just want it to add the information to the receipt table to show the history of inspections on the parts. I think my problem is in how I as setting up my tables or queries, but I just can't figure it out. I also need it to allow for the critical item field to be blank, since not all parts inspected will have 8 critical items. Thanks again for all your help so far.