CODEWITHSUNDEEP
ms-accessvbams-access-2010
joeumlo
joeumlo

Reputation: 51

access form on click open new form with that value with multiple fields

So I have a continuous access form with last name and first name in two separate fields. When the user clicks on either the last name OR the first name, I want VBA to open a new form based on that persons first + last name. I have got VBA working for a single field as follows: 

private sub namelist_Click()
DoCmd.OpenForm "newform", , , "last_name = '" & Me.LastName & "'"
End Sub

So this will open my new form and will show all the records where the last name is the last name the user clicked on. But the problem is some people share a last name (like Smith). In SQL I would just right where last_name = lastname and first_name = firstname, does anyone know how to do that here?

Upvotes: 1

Views: 2157

Answers (1)

Mark C.
Mark C.

Reputation: 6450

Here's multiple conditions:

"last_name = '" & Me.LastName & "' AND " & _
"first_name = '" & Me.FirstName & "'"

However, I would assume you would want to explicitly know which person they are searching for. First name and last name are simply not accurate enough to define a single person. e.g: There can be 2+ Mary Johnsons, Ken Smith, etc.

I would recommend using an ID or another field that uniquely identifies each person, assuming one exists.

Upvotes: 0

Related Questions