By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Creating A Filter Dialog Box With An Unknown Value

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command15_Click()
  2. On Error GoTo Err_Command15_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "SearchView"
  8.  
  9.     stLinkCriteria = "[LastName]=" & "'" & Me![txtLastName] & "'"
  10.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  11.  
  12. Exit_Command15_Click:
  13.     Exit Sub
  14.  
  15. Err_Command15_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_Command15_Click
  18.  
  19. End Sub
can someone help?
Mar 5 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Use an if/then/else statement to check for nulls and create the stLinkCriteria appropriately.
Mar 5 '07 #2

ADezii
Expert 5K+
P: 8,634
Use an if/then/else statement to check for nulls and create the stLinkCriteria appropriately.
This should point you in the right direction:
Expand|Select|Wrap|Line Numbers
  1. Dim stDocName As String
  2. Dim stLinkCriteria As String
  3.  
  4. stDocName = "frmEmployees2"
  5.  
  6. 'Both First & Last Name missing
  7. If Len(Me![txtLastName]) = 0 And Len(Me![txtFirstName]) = 0 Then
  8.   Exit Sub
  9. 'First Name but no Last
  10. ElseIf Len(Me![txtFirstName]) > 0 And Len(Me![txtLastName]) = 0 Then
  11.   stLinkCriteria = "[FirstName]=" & "'" & Me![txtFirstName] & "'"
  12. 'Last Name but no First
  13. ElseIf Len(Me![txtFirstName]) = 0 And Len(Me![txtLastName]) > 0 Then
  14.   stLinkCriteria = "[LastName]=" & "'" & Me![txtLastName] & "'"
  15. 'Both First and Last Names
  16. Else
  17.   stLinkCriteria = "[FirstName]=" & "'" & Me![txtFirstName] & "' AND [LastName]=" & "'" & Me![txtLastName] & "'"
  18. End If
  19.  
  20. DoCmd.OpenForm stDocName, , , stLinkCriteria
Mar 5 '07 #3

P: 3
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
Mar 5 '07 #4

Rabbit
Expert Mod 10K+
P: 12,365
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.
Mar 5 '07 #5

P: 3
that's what I thought, I'll triple check eveything. thanks again
Mar 6 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.