Lookup info in Form

[HeathRamos]

I am trying to create a data entry form.

In this form, I want to make it so when the user enters an employee number in the first field, the last name of the employee automatically generates in the second field.

I know how to do drop down boxes that contain both info but they would have to scroll thru all the employee numbers in order to choose it.

Obviously I do have a link to a table in the database that contains the emploee numbers, names and other info.

 

[ssatech]

Why don't you use DLookup to do you want you want.

"I know how to do drop down boxes that contain both info but they would have to scroll thru all the employee numbers in order to choose it."

As far as dropdown or a combobox you do not need to scroll thru. If you have a query for Rowsource for combobox, Column Count & Width correct. Then just type in "employee number" and should just come up.

Otherwise, use the afterupdate event & FindFirst method or
Private Sub employeeID_AfterUpdate()
Me.YourTableField = Me.YourFormField Column(1)

 

[HeathRamos]

DLookup could work but not sure how to use it.

I have a table called tblMain that has fields empno and lastname. This table is empty.

I have a form called frmMain that points to this blank table.

On this form I have a text box for the employee number, lastname and another field (dropdown box).

I want to be able to type in the employee number, have the last name field automatically be filled out based on the employee text box and then the user would use the dropdown box and save the record.

I have a table called employees that contains a list of employee numbers, names, etc.

I assume you change the text box in the form for the last name and use DLookup.

Something like: =DLookUp(" [SURNAME]","employees","employeeno= [empno] ")

 

[ssatech]

You are going to need another table to do the DLookup.

e.g. tblEmployee
empno....
lastname....


Then your main frmMain based on your tblMain:
On the afterupdate event for your txt field employee number,

something like:

Private Sub empno_AfterUpdate()
Dim StrLastName As String

StrLastName = Nz(DLookup("[lastname]", "EMP", "[empno] = '" & Me![empno] & "'"))
Me.LastName = StrLastName

End Sub

Play with it and see....

 

[ssatech]

Correction:

Private Sub employee_number_AfterUpdate()
Dim StrLastName As String

StrLastName = Nz(DLookup
("[lastname]", "tblEmployee", "[empno] = '" & Me![empno] & "'"))
Me.LastName = StrLastName

End Sub

 

[HeathRamos]

that worked

thanks!