Surname Search problem

[Griffin]

I have a button that when clicked is used to switch to a form displaying a record details based on a search by surname using a combo box. The record chosen by using the combo box is also displayed lower down on the initial form.
My problem is with multiple surname entries, it keeps switching to the first surname record; even when another surname record is selected.

Can I combine the use of surname and first name in my criteria? or Is there a better way to switch to the correct record once it has been selected. I am a bit of a novice with coding. My code is below:

Private Sub Open_Form_Click()
On Error GoTo Err_Open_Form_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Patient Details/Visit History"

stLinkCriteria = "[Surname]=" & "'" & Me![Surname] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_Form_Click:
Exit Sub

Err_Open_Form_Click:
MsgBox Err.Description
Resume Exit_Open_Form_Click
End Sub

 

[Wolfryder88]

You can combine firstname and surname in your combo box. I'm assuming your current combo box is pointing the [surname] field in a table?

Instead, create a query; add the firstname and surname fields (and your primary key field); next, create a new field: Fullname: [surname] & ", " & [firstname]. Be sure to sort your query by the surname field

Point your combo box to the Fullname field in your new query, and you'll get a list of records with the lastname, firstname displayed.

 

[Griffin]

Thanks for all your suggestions Kevin...much appreciated and it worked!