Changing my Search Button

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • glamster7
    New Member
    • May 2007
    • 33

    Changing my Search Button

    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



    Code:
    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
    Recognized Expert Top Contributor
    • Jul 2007
    • 1384

    #2
    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

    Comment

    • glamster7
      New Member
      • May 2007
      • 33

      #3
      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

      Comment

      • Scott Price
        Recognized Expert Top Contributor
        • Jul 2007
        • 1384

        #4
        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:
        [CODE=vb]
        Dim MySQL As String

        MySQL = "Select [YourTableName].Surname Where [Surname] =" & Me.[txtSurname] & ";"

        DoCmd.RunSQL MySQL[/CODE]

        Change the names in []'s to reflect the names you are using for table/form/control... Let me know how this works.

        Regards,
        Scott

        Comment

        • glamster7
          New Member
          • May 2007
          • 33

          #5
          Originally posted by 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:
          [CODE=vb]
          Dim MySQL As String

          MySQL = "Select [YourTableName].Surname Where [Surname] =" & Me.[txtSurname] & ";"

          DoCmd.RunSQL MySQL[/CODE]

          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

          Comment

          • glamster7
            New Member
            • May 2007
            • 33

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

            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

            Comment

            • Scott Price
              Recognized Expert Top Contributor
              • Jul 2007
              • 1384

              #7
              Try
              Code:
              "Select [Client Details].[Client_Surname] From [Client Details] Where [Client_Surname] =" & Me.Text54 & ";"
              I forgot to give you the From clause :-(

              Regards,
              Scott

              Comment

              • glamster7
                New Member
                • May 2007
                • 33

                #8
                Originally posted by Scott Price
                Try
                Code:
                "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

                Comment

                • glamster7
                  New Member
                  • May 2007
                  • 33

                  #9
                  Originally posted by glamster7
                  cheers Scott will give this a go later
                  Code:
                  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

                  Comment

                  • Scott Price
                    Recognized Expert Top Contributor
                    • Jul 2007
                    • 1384

                    #10
                    Strange...

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

                    Regards,
                    Scott

                    Comment

                    • glamster7
                      New Member
                      • May 2007
                      • 33

                      #11
                      Originally posted by 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

                      WHAT ABOUT THE SYNTAX OF THE SELECT STATEMENT SCOTT - ANY THING NEED CHANGING IN THAT

                      Comment

                      • Scott Price
                        Recognized Expert Top Contributor
                        • Jul 2007
                        • 1384

                        #12
                        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.

                        Comment

                        • glamster7
                          New Member
                          • May 2007
                          • 33

                          #13
                          Originally posted by 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.
                          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

                          Comment

                          • Scott Price
                            Recognized Expert Top Contributor
                            • Jul 2007
                            • 1384

                            #14
                            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:
                            [CODE=sql]"Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"[/CODE]

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

                            Regards,
                            Scott

                            Comment

                            • glamster7
                              New Member
                              • May 2007
                              • 33

                              #15
                              Originally posted by 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:
                              [CODE=sql]"Select [Client Details].Client_Surname From [Client Details] Where [Client_Surname] =" & Me.[Text54] & ";"[/CODE]

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

                              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

                              Comment

                              Working...