Combo box - Selecting a record using two fields.

[chantil]

Apologies if this has been asked somewhere before, I checked the FAQ / search and couldn't find an answer but I admit that I am not the most tecnically minded person and a solution may have gojne completely over my head...

I wanted to add a search function to a form on my database which allows me to search by surname.

I finally settled on a combo box with three columns: Surname, Firstname and Date of Birth. I used the combo box wizard to do this (selecting option 3 to search the form)and then arranged the fields in alphabetical order.

The row source coding I have is this:
SELECT [1 - Meningo data 1999-present].surname, [1 - Meningo data 1999-present].firstname, [1 - Meningo data 1999-present].dob FROM [1 - Meningo data 1999-present] ORDER BY [1 - Meningo data 1999-present].surname, [1 - Meningo data 1999-present].firstname;

The problem I have is this...
If I enter a surname say for example 'Smith' and then use the combo box to select the record I want, for example 'John Smith', it does not necessarily open that record.

The record that is opening appears to be the first record (lowest ID number) on the database that has the surname 'Smith' irrespective of First name.

I am still trying to learn the basics of Access and so am at a loss of how to resolve this. Any help would be much appreciated.

 

[PWise]

try this row source

SELECT a.surname & ", " &  a.firstname & "-" & a.dob 
FROM [1 - Meningo data 1999-present] as a 
ORDER BY a.surname, a.firstname;

 

[chantil]

Thanks, I've tried the code that you suggested but now it's not bringing up any record details, it just keeps displaying the first record.

 

[PWise]

What is the code that you have in the after update of the combo box

 

[chantil]

I have...

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

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

 

[PHV]

Use this RowSource:
SELECT surname, firstname, dob FROM [1 - Meningo data 1999-present] ORDER BY surname, firstname;

And in the Combo96's AfterUpdate event procedure:

...
rs.FindFirst "surname='" & Me!Combo96.Column(0) & "' AND firstname='" & Me!Combo96.Column(1) & "'"
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chantil]

That's causing the same problem and now not changing record at all, just staying on the first one.

 

[PWise]

The record that is opening appears to be the first record (lowest ID number) on the database that has the surname 'Smith' irrespective of First name.

I just noticed that you have an ID number for each record.
Then try this

1)change your rowsource to

SELECT id,a.surname & ", " &  a.firstname & "-" & a.dob 
FROM [1 - Meningo data 1999-present] as a 
ORDER BY a.surname, a.firstname;

2)Column Count = 2
3)Column Widths =0";3"

4)

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

    Set rs = Me.Recordset.Clone
    [COLOR=green]'rs.FindFirst "[surname] = '" & Me![Combo96] & "'"[/color]
     [b]'rs.FindFirst "[id] = '" & Me![Combo96] & "'"[/b]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[chantil]

Thanks, I've given it a go.
Unfrotunately it's still staying put on that first record.

 

[PWise]

sorry should be

 ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    'rs.FindFirst "[surname] = '" & Me![Combo96] & "'"
    rs.FindFirst "[id] = '" & Me![Combo96] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[chantil]

It's now bringing up:
Run-time Error '3463':
Data type mismatch in criteria expression.

And the line that's highlighted when you go to debug is:
rs.FindFirst "[id] = '" & Me![Combo96] & "'"

Thanks for your continuing help on this, it's much appreciated!

 

[PWise]

try

rs.FindFirst "[id] = " & Cstr(Me![Combo96] )

 

[chantil]

That is absolutely perfect!
Thank you very much!