Filling textbox from table for the corresponding combox data - Reg

[manthiralaya]

Dear All,
Say for example, I have a Table named 'Table1' contains fields, ID (as primary), MDate, PersonName, PersonJob. Another table named 'Table2' contains ID (as primary), MDate, PersonName, PersonAddress.
I have a Form contains a ComboBox named 'cmbbox1' and have TextBoxes named 'txtbox1' and 'txtbox2'. I have managed to fill the 'MDate' field values from 'Table1'. I want to fill the selected 'MDate' corresponding value of PersonJob from Table1 in txtbox1 and PersonAddress from Table2 in txtbox2.
Please advice how can I modify the following code.
Thanks in advance.
Regards,
manthiralaya

Private Sub cmbbox1_AfterUpdate()
Dim DBS As Database, RST As DAO.Recordset, STRSQL As String, Table1 As TableDef
Set DBS = CurrentDb
STRSQL = "MDate=" & cmbbox1 & ""
Set RST = DBS.OpenRecordset("Table1", dbOpenDynaset)
RST.FindFirst STRSQL
txtbox1 = RST!PersonJob
Set RST = Nothing
End Sub

 

[Rabbie]

Please clarify how table1 and Table 2 are related?

 

[manthiralaya]

Thanks for the kind reply.

I didn't relate the tables. Please advice me how to relate.

The reason I made two tables is as I have around 200 data. As dont want to save 200 data in the same table, I splitted in to two tables.

Any other way to do?

Thanks in advance.

Regards,
manthiralaya