Lookup Queries

[Joyce]

I am trying, unsuccessfully, to create and Auto Lookup
query. I want to use employee ID's (the customer) to
populate their information in an order form. I've put the
joined field, EmployeeID which is the primary key, from
the many side of the query for the lookup but it is not
working. The fields I'm trying to auto populate are
FirstName, LastName, Location, PhoneNo, Extension & FAXNo.

I have too many Employee ID's to use a drop down box, so I
would just like the employee to key their unique ID and
have the info prefill. I've look in the Northwinds
Database and I do see this code.

Please help, I certainly appreciate you, thank you.

 

[tina]

-----Original Message-----
I am trying, unsuccessfully, to create and Auto Lookup
query. I want to use employee ID's (the customer) to
populate their information in an order form. I've put the
joined field, EmployeeID which is the primary key, from
the many side of the query for the lookup but it is not
working. The fields I'm trying to auto populate are
FirstName, LastName, Location, PhoneNo, Extension & FAXNo.

I have too many Employee ID's to use a drop down box, so I
would just like the employee to key their unique ID and
have the info prefill. I've look in the Northwinds
Database and I do see this code.

Please help, I certainly appreciate you, thank you.

.
if you're not going to use a combo box, i'm not sure how

you could use a query to get the data into your form
controls. but below is a procedure you can call from the
BeforeUpdate or AfterUpdate event of the control where the
employee emters his/her ID. if you'd like to use this, but
are not familiar with VBA, you can email me and i'll help
you out.

Private Sub GetEmployeeInfo()

Dim Rst As DAO.Recordset, strSQL As String

strSQL = "SELECT EmployeeID, FirstName, LastName,
Location, PhoneNo, Extension, " _
& "FaxNo FROM TableName WHERE EmployeeID=" & Me!
ControlName

Set Rst = CurrentDb.OpenRecordset(strSQL,
dbOpenDynaset)

If Rst.BOF And Rst.EOF Then
Exit Sub
Else
Me!FirstName = Rst("FirstName")
Me!LastName = Rst("LastName")
Me!Location = Rst("Location")
Me!PhoneNo = Rst("PhoneNo")
Me!Extension = Rst("Extension")
Me!FaxNo = Rst("FaxNo")
End If

Rst.Close
Set Rst = Nothing

End Sub