Creating a Lookup Field in a Form

R

RYANneedsHELP

Im am working in a form...
I need to creat a lookup field for lastname but after the name is found I
want the record for that person to automatically show???

I created the list box looking up lastname via table but cant get it to
bring up the record corresponding to that person.

Thanks.
 
B

Beetle

If you are simply trying to search for matching records based on an
employees Last name, you might be better off using a combo box. The
advantage would be that the users could start typing in the combo box
and it would move to vakues that match what they are typing.

The combo box woul need to be *unbound* (no Control Source) and
would have properties like;

Row Source = Select EmployeeID, LastName From tblEmployees
Order By tblEmployees.LastName
Bound Column = 1
Column Count = 2
Column Widths = 0", 2" (or whatever width works best for the second column)

Then, in the After Update event of the combo box you would have code
like the following;

Private Sub YourCombo_AfterUpdate()

With Me.RecordsetClone
.FindFirst "EmployeeID=" & Me!YourCombo
If .NoMatch Then
MsgBox "No match was found"
Else
Me.BookMark = .BookMark
End If
End With

End Sub

The above assumes that the PK of your Employees table (EmployeeID or
whatever it is called) is an integer data type.
 
R

RYANneedsHELP

The MemberID is an Autonumber.

When I try to run the after update it states, YourCombo cannot be found?

below is what I have for code:

Private Sub Search_Last_Name_AfterUpdate()

With Me.RecordsetClone
.FindFirst "MemberID=" & Me!YourCombo
If .NoMatch Then
MsgBox "No match was found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub
 
D

Douglas J. Steele

Since he didn't know the name of your actual combo box, Beetle had to use
something so he chose YourCombo as the name.

Based on the name of the event procedure, you presumably need

Private Sub Search_Last_Name_AfterUpdate()

With Me.RecordsetClone
.FindFirst "MemberID=" & Me!Search_Last_Name
If .NoMatch Then
MsgBox "No match was found"
Else
Me.Bookmark = .Bookmark
End If
End With

End Sub
 
R

RYANneedsHELP

I may have realized part of the problem...

the data in that tables (tblpersonnel) is not being shown on my form, when I
click the default next record button it doesnt cycle through my data?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top