Combobox lookup and managing foreign keys 1

[jges]

I have a database that links my part numbers with supplier(s) and their part number(s). I have a join table set up to manage the many-to-many relationship. What I would like to do in 'frmParts' is to select a supplier (or enter a new one) in the combobox and update the foreign keys in the join table. I started out with faq702-4283, but I need more and I don't know where to start. I have been playing with the row source of the combo box but I don't think it is right yet.

Attached is a simplified database showing my problem (access 2000 format). I'm fairly new at this, so any and all good advice is welcome.

ps any book recommendations for Access would be appreciated - so far the ones at the local library have been a mixed bag - good for the basics but lacking much useful info on coding and things that seem like they would be fairly common (such combo box lookups).

 

[T111]

1. Open up "frmParts" in design view
2. Right click on supplier field in sub form and click change to combo box
3. Bring up properties of supplier combo (F4) and set the Data tab settings to
Control source = "Supplier_fk"
Row source = "SELECT tblSuppliers.Supplier_ID, tblSuppliers.Supplier_Name FROM tblSuppliers; "

Format settings
Column Widths = "0cm;2.542cm"

That should do it.

I don't use books as there are an unlimited no of websites!

 

[jges]

Excellent!

Thanks for the help.