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

Changing Search Criteria

P: 33
Ok I have the following code which allows the users to search by Client Id No -- but they would prefer to search by Client_First_Name & Client_Surname (which are two fields in tblClient Details). I have been having a go at this & can change the search to search on Client_Surname but this only shows the first record with that surname. What I would like is for the user to enter First Name Surname in the text box(es) & click the search button & pull back all the clients with that name. Is it possible to convert what I have code wise to do this??




Expand|Select|Wrap|Line Numbers
  1. Private Sub Command53_Click()
  2. Dim strClientIdNo As String
  3.     Dim strSearch As String
  4.  
  5.     If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
  6.         MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
  7.         Me![Text54].SetFocus
  8.     Exit Sub
  9. End If
  10.  
  11.     DoCmd.ShowAllRecords
  12.     DoCmd.GoToControl ("Client_Id_No")
  13.     DoCmd.FindRecord Me!Text54
  14.  
  15.     Client_Id_No.SetFocus
  16.     strClientIdNo = Client_Id_No.Text
  17.     Text54.SetFocus
  18.     strSearch = Text54.Text
  19.  
  20.     If strClientIdNo = strSearch Then
  21.         MsgBox "Match Found For: " & strSearch, , "Congratulations!"
  22.         Client_Id_No.SetFocus
  23.         Text54 = ""
  24.  
  25.          Else
  26.             MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
  27.             , "Invalid Search Criterion!"
  28.             Text54.SetFocus
  29.     End If
  30. End Sub
Sep 10 '07 #1
Share this Question
Share on Google+
2 Replies


P: 33
Forgot to mention I posted this a little while a go with a slightly different title but I seem to have posted it under Access Articles --- could one of the mods please delete that one for me
Sep 10 '07 #2

Jim Doherty
Expert 100+
P: 897
Ok I have the following code which allows the users to search by Client Id No -- but they would prefer to search by Client_First_Name & Client_Surname (which are two fields in tblClient Details). I have been having a go at this & can change the search to search on Client_Surname but this only shows the first record with that surname. What I would like is for the user to enter First Name Surname in the text box(es) & click the search button & pull back all the clients with that name. Is it possible to convert what I have code wise to do this??




Expand|Select|Wrap|Line Numbers
  1. Private Sub Command53_Click()
  2. Dim strClientIdNo As String
  3.     Dim strSearch As String
  4.  
  5.     If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
  6.         MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
  7.         Me![Text54].SetFocus
  8.     Exit Sub
  9. End If
  10.  
  11.     DoCmd.ShowAllRecords
  12.     DoCmd.GoToControl ("Client_Id_No")
  13.     DoCmd.FindRecord Me!Text54
  14.  
  15.     Client_Id_No.SetFocus
  16.     strClientIdNo = Client_Id_No.Text
  17.     Text54.SetFocus
  18.     strSearch = Text54.Text
  19.  
  20.     If strClientIdNo = strSearch Then
  21.         MsgBox "Match Found For: " & strSearch, , "Congratulations!"
  22.         Client_Id_No.SetFocus
  23.         Text54 = ""
  24.  
  25.          Else
  26.             MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
  27.             , "Invalid Search Criterion!"
  28.             Text54.SetFocus
  29.     End If
  30. End Sub
If you send me your email by PM I will mail you a fully functional demo of how you can search fields in combination with each other using wildcards also with the ability to output the returned dataset to excel if you wish and also add to the same table of people all from the same form. It has a number of techniques you might find useful in your learning process

Regards

Jim
Sep 10 '07 #3

Post your reply

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