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

Changing my Search Button

P: 33
I have the following code which allows me to search for a client by Id Number -- however we now wish to search by client name preferably Surname & First name. I have had a go at this & can adapt the code to search on Surname but this will only bring up the first record. Is it going to be easy to change this code so that we can search on First Name & Surname (or Surname First Name) or would new code be better. And how would I do this? Any help would be great

Am using Windows XP & Access 2000



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+
15 Replies


Scott Price
Expert 100+
P: 1,384
Have a look here at this sample database: http://allenbrowne.com/AppFindAsUType.html. As well as this one: http://allenbrowne.com/ser-62.html

Adapting your code is possible, but you might find the methods used in these samples a little more intuitive... Instead of clicking a command button, the first searches based on text entered while you are typing.

Regards,
Scott
Sep 10 '07 #2

P: 33
Thanks for your help - although having had a quick look at them they are maybe a bit to complex for what we need. I did manage to change my code to search for client surname & that was ok except it only picked up the first record. If I could adapt that to pick up all of the records with that surname it would probably do the job
Sep 11 '07 #3

Scott Price
Expert 100+
P: 1,384
One of the problems adapting the code you have is that your code is looking for a unique identifier, and so will only be returning one result. Changing the unique identifier to look in the Surname field will do what you are finding: return one result.

You can run an SQL query using the DoCmd.RunSQL method in VBA. Just off the top of my head, try something like this:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. MySQL = "Select [YourTableName].Surname Where [Surname] =" & Me.[txtSurname] & ";"
  4.  
  5. DoCmd.RunSQL MySQL
Change the names in []'s to reflect the names you are using for table/form/control... Let me know how this works.

Regards,
Scott
Sep 11 '07 #4

P: 33
One of the problems adapting the code you have is that your code is looking for a unique identifier, and so will only be returning one result. Changing the unique identifier to look in the Surname field will do what you are finding: return one result.

You can run an SQL query using the DoCmd.RunSQL method in VBA. Just off the top of my head, try something like this:
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. MySQL = "Select [YourTableName].Surname Where [Surname] =" & Me.[txtSurname] & ";"
  4.  
  5. DoCmd.RunSQL MySQL
Change the names in []'s to reflect the names you are using for table/form/control... Let me know how this works.

Regards,
Scott
Cheers Scott
Will have a look at that later -- am currently looking at something but will get back to this
Sep 11 '07 #5

P: 33
Ok have just had a go at this .Have the following code

Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String
  2.  
  3. If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
  4.         MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
  5.         Me![Text54].SetFocus
  6.     Exit Sub
  7. End If
  8.  
  9. MySQL = "Select [Client Details].Client_Surname Where [Client_Surname] =" & Me.[Text54] & ";"
  10.  
  11. DoCmd.RunSQL MySQL 
I am getting an error message when I try to use this. the error message is

Runtime Error '3075'
Syntax Error (missing operator) in query expression '[Client Detals].Client_Surname Where [Client_Surname] =biggs'

Used the name biggs as I know this is in the clients table with several entries
Sep 11 '07 #6

Scott Price
Expert 100+
P: 1,384
Try
Expand|Select|Wrap|Line Numbers
  1. "Select [Client Details].[Client_Surname] From [Client Details] Where [Client_Surname] =" & Me.Text54 & ";"
I forgot to give you the From clause :-(

Regards,
Scott
Sep 11 '07 #7

P: 33
Try
Expand|Select|Wrap|Line Numbers
  1. "Select [Client Details].[Client_Surname] From [Client Details] Where [Client_Surname] =" & Me.Text54 & ";"
I forgot to give you the From clause :-(

Regards,
Scott

cheers Scott will give this a go later
Sep 12 '07 #8

P: 33
cheers Scott will give this a go later
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command53_Click()
  2. 'Dim strClientIdNo As String
  3.     'Dim strSearch As String
  4.     Dim MySQL As String
  5.  
  6.     If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
  7.         MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
  8.         Me![Text54].SetFocus
  9.     Exit Sub
  10. End If
  11.  
  12.  
  13.     MySQL = "Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"
  14.  
  15.     DoCmd.RunSQL MySQL
  16.  
  17. End Sub 
Am getting the following error
Run time error 2342
A RunSQL action requires an arguement consisting of an SQL statement
Sep 12 '07 #9

Scott Price
Expert 100+
P: 1,384
Strange...

Do away with the call to the MySQL string, and instead put the Select statement directly after the DoCmd.RunSQL command.

Regards,
Scott
Sep 12 '07 #10

P: 33
Strange...

Do away with the call to the MySQL string, and instead put the Select statement directly after the DoCmd.RunSQL command.

Regards,
Scott

WHAT ABOUT THE SYNTAX OF THE SELECT STATEMENT SCOTT - ANY THING NEED CHANGING IN THAT
Sep 12 '07 #11

Scott Price
Expert 100+
P: 1,384
If you look at the error you got from post #6 you will notice that the query expression had something wrong with it. That was corrected by adding the FROM clause. Now you are getting a different error which you should not be getting!

Why don't you try doing a debug.print of MySQL, copying the resulting sql expression into a new query in design view. Run that query and see if there are any results.

Kind regards,
Scott

P.S. Posting in all caps isn't necessary, please refrain from doing it in the future. I understand you are frustrated, programming is a frustrating job! However, getting angry or being rude doesn't help solve the problem.
Sep 12 '07 #12

P: 33
If you look at the error you got from post #6 you will notice that the query expression had something wrong with it. That was corrected by adding the FROM clause. Now you are getting a different error which you should not be getting!

Why don't you try doing a debug.print of MySQL, copying the resulting sql expression into a new query in design view. Run that query and see if there are any results.

Kind regards,
Scott

P.S. Posting in all caps isn't necessary, please refrain from doing it in the future. I understand you are frustrated, programming is a frustrating job! However, getting angry or being rude doesn't help solve the problem.
Ok Scott - forgot the caps lock was so my apologies there - had it on for some data entry on something. Am looking at another job at the moment but will let ya know how i go
Sep 12 '07 #13

Scott Price
Expert 100+
P: 1,384
No problems!

The only thing I can see right now that might need changing (the puzzling thing is that it shouldn't be giving you the error you are reporting) is to put your Me.Text54 in single quotes...i.e. instead of:
Expand|Select|Wrap|Line Numbers
  1. "Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"
Expand|Select|Wrap|Line Numbers
  1. "Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & "'" & Me.[Text54] & "'" & ";"
Regards,
Scott
Sep 12 '07 #14

P: 33
No problems!

The only thing I can see right now that might need changing (the puzzling thing is that it shouldn't be giving you the error you are reporting) is to put your Me.Text54 in single quotes...i.e. instead of:
Expand|Select|Wrap|Line Numbers
  1. "Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"
Expand|Select|Wrap|Line Numbers
  1. "Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & "'" & Me.[Text54] & "'" & ";"
Regards,
Scott
Ok Scott thought I'd best let you know what we've done. after talking to my supervisor & manager we are going down the route of using the link you gave me to Allene Brownes Findasutype procedure -they like the functionality of that.

The reason behind that is knowing the department thats using this database it covers all options of search criteria & filters they may decide they need in the future. Have it in & its working well & looks good

Many thanks for replying to my post even through we never got to a final solution. I may look at solving this as a matter of personal curiosity
Sep 14 '07 #15

Scott Price
Expert 100+
P: 1,384
Thanks for posting back! If you find out what was happening with the other, I'd be interested in the solution also!

Yes, Allen's FindAsUType is pretty slick. I'm using it currently also.

Regards,
Sep 14 '07 #16

Post your reply

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