populate combo box by...?

[DeanRowe]

Hi,

I am very sorry, I’m sure this is a very basic issue but I’ve had a complete brain freeze and I can’t solve this problem. If anyone could help I would be very grateful.

I have one chainsaw that I can buy from a maximum of 3 different suppliers.

I have 4 tables in all – “OurTable”, “SupplierA”, “SupplierB” and “SupplierC”.

Each product in “OurTable” has a “Unique Product ID”, for example “Chainsaw1”.

Each supplier also has a “Unique Product ID” field. In the relationship screen I have linked “OurTable” to each supplier table using the “Unique Product ID” field. So if Supplier A sells “Chainsaw1” it uses the same “Unique Product ID”.

I have a form that is linked directly to “OurTable”. When I bring up the Details for “Chainsaw1” I want to have a dropdown/combo box that shows which of the 3 suppliers sells “Chainsaw1”.

So if Suppliers A & C sell “Chainsaw1” the combo box gives these two options – I then intend to use this combo box to populate other fields in my form with the data contained in the selected supplier’s table.

For example, if Supplier C is selected then Supplier C’s price, delivery cost and delivery time is shown on the blank fields on the form.

I’ve had a complete blackout and I don’t know whether I need to perform a query with an if statement of if I need to tinker with the properties of the “buying from” (this is the field name of the combo box) field in the actual “OurTable” table.

Any help or guidance would be welcomed. Thank you very much for your time.

 

[statsman]

With the form in design view, right click the combo/list box and select
Build Event, then Code Builder.

Select BeforeUpdate from the top right corner.

Enter the following code between the 2 existing lines.

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub

The [fieldname] is the name of the field you want to copy the data to.
Combo55 is the number of the combo box you're using.
The # refers to the column in the combo box. The columns start with number 0 (zero) in the left most column and increases by 1 for each column you move to the right. The third column from the left would therefore be column 2.

Each column you wish to copy when you select a record in the combo box needs its own line of code.

When you created the combo box using the wizard, you had the option of saving one of the columns into a field at that time. If you did this, you don't have to use the code to save that column, it’s already done.