Lookup question

[Mike106]

I have a form with fields bound to table1. Field1 is a combo list that is populated by values from field1 in table2. Tsble2 has other fields that correspond to fields in table1. When I am adding a new record in talbe1 and select a choice in the field1 combo list that would have the same value in field1 in table2, I would like to have the corresponding values from the corresponding table2 fields to appear in the form but not necessarily accepting the values before I commit the record to the table. How do I accomplish this? I am new to VBA.

[pbaldy]

This help?

http://www.baldyweb.com/Autofill.htm

[Mike106]

This help?

http://www.baldyweb.com/Autofill.htm

No, I couldn't get it to work. What I would like to do is have VBA code that does the following:
After the selection is made from the combo box "Entry Title" the following query runs:
SELECT Commons.Checks, Commons.ACCTNUM, Commons.Amount, Categories.Description, Categories.[Income/Expense]
FROM Categories RIGHT JOIN Commons ON Categories.ID = Commons.CATEGORY
The query would have a where clause, where Common.Checks= the value in the combo box "Entry Title". The values returned from the query would be entered in the corresponding fields in the form.

I am migrating an app that I developed in Paradox years ago to Access 2010. I am using the "Personal account ledger" template. I made a few adaptions, imported all my data. It works fine, but don't know VBA to automate common tasks.

This is the Paradox code that I used in the Paradox app on the combo box Entry Title.
method depart(var eventInfo MoveEvent)
Var
tc TCursor
s String
EndVar
s = CHECKNAME.value
If isedit() Then
If AMNT = 0 Then
tc.open(":mikedb:commons.db")
If tc.locate("Checks", s)

Then
AMNT.value = tc.("Amount")
NOTES.value = tc.("ACCTNUM")
EXPCAT.value = tc.("CATEGORY")
EndIf
EndIf
EndIf
endMethod

s is the value in the combo box. A tCursor is method that points to a record in a table without running a query. If the equivalent of a tCursor doesn't exist in VBA, then the values returned from the query I described above would work.
Thanks

[pbaldy]

The linked method is the most efficient, but see if this gets you started:

  Dim strSQL        As String
  Dim rslookup      As DAO.Recordset

    strSQL = "SELECT ResNum, AuthBy, PassName " & _
             "FROM tblReservations " & _
             "WHERE Resnum = " & Me.Reservation_num

    Set rslookup = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

    If Not (rslookup.EOF) Then
      'no records found
    Else
      'record(s) found
    End If

    Set rslookup = Nothing