combo box question

[Lieu]

Hello there,

I have table1, table2, a combo box. table1 has field1, table2 has field1 and field2. When the form load, the combo box is bounded to field1 of table1.

Question: Although the combo box is bounded to field1 of table1, is it possible to have the combo box populate information from field1 of table2. And then when comes to save the record, I would like it to save the information from field2 of table2 to field1 of table1.

I am using access 97 for table1 and table2.

Thank you in advance.

Hai Lieu

[Gaffer]

Hi Hai,

You may need to clarify what you need to do:

If you just want to change the data source of the combo box from table1 to table2, then change the Row Source property of the combo box from
SELECT DISTINCTROW [table1].[field1] FROM [table1];
to
SELECT DISTINCTROW [table2].[field1] FROM [table2];

or click the Build button next to property to invoke the QBE grid.

The second issue requires you to put the following code in your Save record button (called cmdUpdateTable1 here) Click() Event:

Private Sub cmdUpdateTable1_Click()

Dim db As Database

Set db = CurrentDb

db.Execute "insert into table1(field1) " & _
"select field2 from table2 where field1='" & Me.Combo1 & "'"

End Sub

I hope this answers your question,

Gaffer