I'd do it the following way.
Let's say we got a table MEMBERS with the fields ID, M_NAME, M_LASTNAME.
Now we've got the form with the following objects (as you listed above):
name1 - text field for the name
ok - the button
name2 - combo box for the last name
m_id - a text field for the id
All you have to do now is to add a procedure to the properties of ok onclick) and one to name2 (onchange) [not sure if you understand what I mean, but I'm working with a German version of Access and have no idea how exactly that stuff is called in your english Access] and add the following code to the form:
Option Compare Database
Private Sub Form_Load()
name1.Value = ""
name2.Value = ""
m_id.Value = ""
name2.Enabled = False
End Sub
Private Sub name2_Change()
Dim db As DAO.database, rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TOP 1 ID FROM MEMBERS WHERE M_LASTNAME='" & name2.Value & "'")
If Not rs.EOF And Not rs.BOF Then m_id.Value = rs![id]
rs.close
End Sub
Private Sub ok_Click()
name2.Enabled = True
name2.RowSource = "SELECT [M_LASTNAME] FROM [MEMBERS] WHERE M_NAME='" & name1.Value & "'"
End Sub
If you're not sure what I mean, feel free to ask. I can also provide the sample database I used for this if you would like. Hope that helps. 
Topic Locked
Posted - 04 June 2004 : 16:54:41
