none
Search Bar String Filler RRS feed

  • Question

  • Hello,

    Can someone please help me with why this code might not be working.

    *******************************************CODE******************************************************

    Private Sub Join_Search_Click()
    Dim strCriteria, task As String

        Me.Refresh
        If IsNull(Me.SASP_Advisor_Search) Then
            MsgBox "Please enter the Advisor's Name", vbInformation, "Full Name Required"
            Me.SASP_Advisor_Search.SetFocus
        Else
            If (StrComp(Me.SASP_Advisor_Search, [SASP Primary Advisor Name], vbTextCompare) = 0) Then
                MsgBox "Good Entry", vbInformation, "Good Job"
                strCriteria = "([SASP Primary Advisor Name] = '& Me.SASP_Advisor_Search &' )"
                DoCmd.ApplyFilter , strCriteria
            Else
                'MsgBox "Please enter the Advisor's Full Name", vbInformation, "Full Name Required"
            End If

        End If
    End Sub

                
    Friday, December 15, 2017 1:47 PM

Answers

  • The Me.SASP_Advisor_Search must be outside of the string:

    strCriteria = "([SASP Primary Advisor Name] = '" & Me.SASP_Advisor_Search & "')"

    Matthias Kläy, Kläy Computing AG

    • Marked as answer by SpeedKing1000 Friday, December 15, 2017 2:38 PM
    • Unmarked as answer by SpeedKing1000 Friday, December 15, 2017 2:40 PM
    • Marked as answer by SpeedKing1000 Monday, December 18, 2017 2:42 PM
    Friday, December 15, 2017 2:01 PM
  • What currently happens?

    What's the Me.Refresh for?

     

    You have a synthax error in your line

    strCriteria = "([SASP Primary Advisor Name] = '& Me.SASP_Advisor_Search &' )"

    Try

    strCriteria = "([SASP Primary Advisor Name] = '" & Me.SASP_Advisor_Search & "')"


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Friday, December 15, 2017 2:01 PM

All replies

  • The Me.SASP_Advisor_Search must be outside of the string:

    strCriteria = "([SASP Primary Advisor Name] = '" & Me.SASP_Advisor_Search & "')"

    Matthias Kläy, Kläy Computing AG

    • Marked as answer by SpeedKing1000 Friday, December 15, 2017 2:38 PM
    • Unmarked as answer by SpeedKing1000 Friday, December 15, 2017 2:40 PM
    • Marked as answer by SpeedKing1000 Monday, December 18, 2017 2:42 PM
    Friday, December 15, 2017 2:01 PM
  • What currently happens?

    What's the Me.Refresh for?

     

    You have a synthax error in your line

    strCriteria = "([SASP Primary Advisor Name] = '& Me.SASP_Advisor_Search &' )"

    Try

    strCriteria = "([SASP Primary Advisor Name] = '" & Me.SASP_Advisor_Search & "')"


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Friday, December 15, 2017 2:01 PM
  • It is not filtering the name of who ever I type into the search bar
    Friday, December 15, 2017 2:29 PM
  • Thank you very much it works now.
    Friday, December 15, 2017 2:37 PM
  • But it is only working for one person. If I enter the other person name it doesn't work. What might be wrong?

    Friday, December 15, 2017 2:41 PM
  • What are the name's involved out of curiosity?

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, December 15, 2017 2:51 PM
  • The name involves advisors to students. If I type in the name it will say it can't find but if I copy the name from the existing field it will say it find it. I am thinking that this might be this line of

    StrComp(Me.SASP_Advisor_Search, [SASP Primary Advisor Name], vbTextCompare) = 0

     
    Friday, December 15, 2017 4:17 PM
  • Why not use a combo box or list box to select a name or, if you use a multi-select list box, multiple names.  You'll find examples in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to 'go to a record' illustrates how to navigate to a single record using a combo box, the option to 'filter by multiple values' illustrates how to filter a form on one or more values selected in a multi-select list box.  In this, after selecting one or more names in the list box, pressing the 'Confirm Search' button filters the form to the selected record by means of code in the button's Click event procedure.

    Searching solely on names is not entirely reliable, however, as personal names can legitimately be duplicated.  The NotInList demo in my same OneDrive folder illustrates how two or more people of the same name can be differentiated by other data in the list.  In the demo's opening form address data is shown for each person, concatenating the values from multiple columns into a single string expression by means of a ConcatValues function in the basConcat module in the database.

    Ken Sheridan, Stafford, England

    Saturday, December 16, 2017 1:38 PM
  • Okay, Thank you I will try it.
    Monday, December 18, 2017 2:42 PM