Creating A Filter Dialog Box With An Unknown Value

[focushq]

Hello,

I am creating a dialog box that filters data on another form. Currently, i've added 2 text boxes (txtFirstName & txtLastName) & a command button that will open my form from the text inputed. This works well if you type "John" for the first name and "smith" for the last name. I want to be able to leave the first name blank (txtFirstName) and input a last name in txtLastName and be able show all of the people of the same last name on the form i open. In this example everyone with the same last name of "smith". I also want to be able to leave the last name empty and enter a first name.

Here is the event for the command button:

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "SearchView"
 
    stLinkCriteria = "[LastName]=" & "'" & Me![txtLastName] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command15_Click:
    Exit Sub
 
Err_Command15_Click:
    MsgBox Err.Description
    Resume Exit_Command15_Click
 
End Sub

can someone help?

[Rabbit]

Use an if/then/else statement to check for nulls and create the stLinkCriteria appropriately.

[ADezii]

Use an if/then/else statement to check for nulls and create the stLinkCriteria appropriately.

This should point you in the right direction:

Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "frmEmployees2"
 
'Both First & Last Name missing
If Len(Me![txtLastName]) = 0 And Len(Me![txtFirstName]) = 0 Then
  Exit Sub
'First Name but no Last
ElseIf Len(Me![txtFirstName]) > 0 And Len(Me![txtLastName]) = 0 Then
  stLinkCriteria = "[FirstName]=" & "'" & Me![txtFirstName] & "'"
'Last Name but no First
ElseIf Len(Me![txtFirstName]) = 0 And Len(Me![txtLastName]) > 0 Then
  stLinkCriteria = "[LastName]=" & "'" & Me![txtLastName] & "'"
'Both First and Last Names
Else
  stLinkCriteria = "[FirstName]=" & "'" & Me![txtFirstName] & "' AND [LastName]=" & "'" & Me![txtLastName] & "'"
End If
 
DoCmd.OpenForm stDocName, , , stLinkCriteria

[focushq]

I can see how the logic is put together, thanks.

but for sum reason it is running as before.

i'm supposed to put the whole if statement in the command button correct? i don't need to modify any other part of my data base?

thanks

[Rabbit]

That would be correct. Did you make sure everything was named correctly? Since we don't know exactly how everything is named in your tables and forms, we can only guess at the ones we don't know.

[focushq]

that's what I thought, I'll triple check eveything. thanks again