Combo boxes filling in fields automatically

[sharks13]

Hey all,

I have a table in my database (called SupplierCage) that has 2 headings - Supplier name and Supplier Cage Code. For each supplier name there is 1 unique cage code. I have a form (the source of which is not the SupplierCage table) with 2 fields (among many others) called Supplier and Cage Code. The Supplier field is a combobox and the combobox points back to the SupplierCage for the list of names available.

Because cage code is not readily accessible to my users, I'd like to set it up so that whenever someone entered in a Supplier Name, the correct cage would automatically fill in, in the correct field. I know how to set up code to do each Supplier and Cage individually, however that would take me forever as there are over 3000 suppliers.

Is there any way to call the entire table to do what I described?

 

[LxQ]

Point the Cage Code field to a query (based on SupplierCage) that picks up the right code based on the name selected in the Supplier combo box. You'd have to set the AfterUpdate of the Supplier combo box to an event that requeries the form so when a name is selected, a form is refreshed and the Cage Code is picking up the value from the query.

 

[sharks13]

Thanks for the help, but I'm not really following you (still learning). I made a query based off the SupplierCage table that has 2 columns - SupplierName and Cage. Each Name has one cage code.

How would I set the AfterUpdate of the combo box to pull the correct cage code from a query? The refresh coding is easy enough.

 

[LxQ]

well, the AfterUpdate event on Supplier (SupplierCombo) would just be to refresh Cage Code (Cage_Code) by calling a requery command:

Private Sub SupplierCombo_AfterUpdate()
Me.Cage_Code.Requery
End Sub

If the Cage Code is pointing to a query you saved, then in the query make sure you have the criteria on the Supplier column to equal the Supplier combo box.
If the query is just in the row source of the Cage Code, then it should look the same and the row source would look something like this:

SELECT SupplierCage.Supplier, SupplierCage.Cage_Code FROM SupplierCage WHERE (((SupplierCage.Supplier)=[SupplierCombo]));

 

[sharks13]

Ah OK I got it now. Thanks a lot for the help.