Auto-Populate From Table2 to Table1 1

[woodywoodpecker]

I have two tables, one being the main table and the other being a reference table. When I enter a "1" in column A in the main table, I want Access to look at the reference table and populate column B in the main table with the corresponding value. Then, I want Access to also populate Column C and D in the main table. My reference table has 3 columns. Can this be done?

 

[accessgoddess]

Hi,

Yep, it's really not hard. The easiest way is to make a combo box from the second table with the values you want to use to populate the first table. As an example let's pretend that table B is for customers and table A is orders. If you wanted to let someone choose a customer number (1) and it when to table B to find the shipping address and whatnot for the order.

So on your form for Table A make a combo box using table B as the source. Set the combo box (lets call it custpick) to query with the first column (column 0 as it's zero based) being the customer code and column 1 being the shipaddress and column 2 being the shipcity, column 3 for the shipstate and column 4 for the shipzip.

In the property box for the combo box set it to 5 columns with the bound column being 1.

When the user chooses a cust number from custpick put this code is the on update event of the combo box.

Me!shipaddress = me!custpick.column(1)
Me!shipcity = me!custpick.column(2)
Me!shipstate = me!custpick.column(3)
Me!shipzip = me!custpick.column(4)

That should do it. Me! refers to the form so all those fields would have to be on the form.

Hope that helps.

Joanne

 

[woodywoodpecker]

That's outstanding, thank you so much for your help!