Changing my Search Button

[glamster7]

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

Private Sub Command53_Click()
Dim strClientIdNo As String
    Dim strSearch As String
 
    If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![Text54].SetFocus
    Exit Sub
End If
 
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Client_Id_No")
    DoCmd.FindRecord Me!Text54
 
    Client_Id_No.SetFocus
    strClientIdNo = Client_Id_No.Text
    Text54.SetFocus
    strSearch = Text54.Text
 
    If strClientIdNo = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        Client_Id_No.SetFocus
        Text54 = ""
 
         Else
            MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            Text54.SetFocus
    End If
End Sub

[Scott Price]

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

[glamster7]

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

[Scott Price]

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:

Dim MySQL As String
 
MySQL = "Select [YourTableName].Surname Where [Surname] =" & Me.[txtSurname] & ";"
 
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

[glamster7]

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:

Dim MySQL As String
 
MySQL = "Select [YourTableName].Surname Where [Surname] =" & Me.[txtSurname] & ";"
 
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

[glamster7]

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

Dim MySQL As String
 
If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![Text54].SetFocus
    Exit Sub
End If
 
MySQL = "Select [Client Details].Client_Surname Where [Client_Surname] =" & Me.[Text54] & ";"
 
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

[Scott Price]

Try

"Select [Client Details].[Client_Surname] From [Client Details] Where [Client_Surname] =" & Me.Text54 & ";"

I forgot to give you the From clause :-(

Regards,
Scott

[glamster7]

Try

"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

[glamster7]

cheers Scott will give this a go later

Private Sub Command53_Click()
'Dim strClientIdNo As String
    'Dim strSearch As String
    Dim MySQL As String
 
    If IsNull(Me![Text54]) Or (Me![Text54]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![Text54].SetFocus
    Exit Sub
End If
 
 
    MySQL = "Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"
 
    DoCmd.RunSQL MySQL
 
End Sub 

Am getting the following error
Run time error 2342
A RunSQL action requires an arguement consisting of an SQL statement

[Scott Price]

Strange...

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

Regards,
Scott

[glamster7]

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

[Scott Price]

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.

[glamster7]

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

[Scott Price]

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:

"Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"

"Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & "'" & Me.[Text54] & "'" & ";"

Regards,
Scott

[glamster7]

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:

"Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"

"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

[Scott Price]

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,