Combo box slectetion of 2 fields

[ramrod]

HI

I have access 2003 and I have problems solving something which I am
sure is easy .. however I can not find an example to show me how !!

Problem:

I have a form showing customers ... on this form I have a quick search
for customers LastName in a Combo Box.
This works fine ... how ever I have some customers with the same
surname... so I have created a ComboBox which display's Lastname and
Firstname ! Works OK except ....
when I click on a name in a list of say people, when I get to those who
are called 'Smith' ... the one that the form shows is the first in the
list and not the 3rd one down in the list called Smith which I selected
!! ie shows Smith Alan not Smith Charles which I selected !!!


Can anyone tell me how to get the form to the correct record by
clicking the name in the combo box ..... ie the combo box caluclation
must match the 2 fields and show the correct record

I have looked at VBA (limited knowledge here ...) and have looked hear
wondering if I could add the second critiera ...???

Here is the VBA routine for this

Private Sub Combo66_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName] = '" & Me![Combo66] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I thought I could add the field [FirstName] in this line above
.......rs.FindFirst "[LastName] = '" & Me![Combo66] & "'") or am I not
thinking correctly ..??

ANy help for a small problem would be great ........ Thank you.

R

 

[Douglas J. Steele]

The problem is, FindFirst is going to find the first Smith in the table, so
you really have no way of knowing which Smith that is.

Assuming you've got both the first name and last name in the combo box, you
can refer to the other column(s) in the combo box in the AfterUpdate event.
For instance, if the first name is in the 2nd column of the combo box, try:

rs.FindFirst "[LastName] = '" & Me![Combo66] & "' " & _
"AND [FirstName] = '" & Me![Combo66].Column(1) & "'"

(the Column collection starts at 0, not 1)

 

[ramrod]

Thank you very much for your time and help ...... It works a treat !!!

Thank you

R