Results 1 to 5 of 5
  1. #1
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88

    Search box not working right

    search is not working right...
    1. if no text then highlight yellow and msgbox (works)
    2. if text found then highlight green and display (doesn't work)
    3. if text is not found then highlight red and msgbox (works)

    tried else and elseif...not sure what im doing wrong. thnx.

    -----------------------------------------------------------------------------
    Private Sub cmdSearch_Click()

    Dim lRecCount As Long 'holds # of found
    Dim strSearch As String 'this looks up the text written for search
    Dim strFind As String 'this looks up in SQL for text searched

    'check if a keyword has been entered or not then vbYellow textbox if not + msgbox
    If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then


    MsgBox "Please type in your search keyword.", vbInformation, "Keyword Needed!"
    Me.txtSearch.BackColor = vbYellow
    Me.txtSearch.SetFocus

    'if record found then highlight RecFound box + filters
    ElseIf strSearch = Me.txtSearch.Value = True Then
    strFind = "SELECT * FROM tblQuestion WHERE ((Question Like ""*" & strSearch & "*"") OR (Possible1 Like ""*" & strSearch & "*"") OR (Possible2 Like ""*" & strSearch & "*"") OR (Possible3 Like ""*" & strSearch & "*"") OR (Possible4 Like ""*" & strSearch & "*""))"
    Me.RecordSource = strFind
    Me.txtSearch.SetFocus
    Me.RecID.BackColor = vbGreen
    Me.RecID.ForeColor = vbBlack

    'if record not found then vbRed textbox + msgbox
    ElseIf strFind = Me.txtSearch.Value = False Then
    Me.txtSearch.BackColor = vbRed
    DoCmd.CancelEvent
    Me.txtSearch.SetFocus
    MsgBox "Record not found!", vbExclamation, "Oh Nooooo!"
    Else
    DoCmd.CancelEvent
    End If
    End Sub

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?error message or number??
    Your Possible1, Possible2, Possible3... suggests an un-Normalized structure.
    Some context about your application, your form, your tables.. would be helpful.
    Good luck.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This will simplify the if-then-else
    Also, See the red. strSearch has never been given a value.
    Code:
    Private Sub cmdSearch_Click()
     Dim lRecCount As Long 'holds # of found
     Dim strSearch As String 'this looks up the text written for search
     Dim strFind As String 'this looks up in SQL for text searched
     'check if a keyword has been entered or not then vbYellow textbox if not + msgbox
     If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
      MsgBox "Please type in your search keyword.", vbInformation, "Keyword Needed!"
      Me.txtSearch.BackColor = vbYellow
      Me.txtSearch.SetFocus
      exit sub
     End if  
     'if record found then highlight RecFound box + filters
     If strSearch = Me.txtSearch.Value Then
      strFind = "SELECT * FROM tblQuestion WHERE ((Question Like ""*" & strSearch & "*"") OR (Possible1 Like ""*" & strSearch & "*"") OR (Possible2 Like ""*" & strSearch & "*"") OR (Possible3 Like ""*"  & strSearch & "*"") OR (Possible4 Like ""*" & strSearch & "*""))"
      Me.RecordSource = strFind
      Me.txtSearch.SetFocus
      Me.RecID.BackColor = vbGreen
      Me.RecID.ForeColor = vbBlack
      exit sub
     End if 
     'if record not found then vbRed textbox + msgbox
     If strFind = Me.txtSearch.Value = False Then
      Me.txtSearch.BackColor = vbRed
      DoCmd.CancelEvent
      Me.txtSearch.SetFocus
      MsgBox "Record not found!", vbExclamation, "Oh Nooooo!"
     End If
    End Sub

  4. #4
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88
    Quote Originally Posted by davegri View Post
    This will simplify the if-then-else
    Also, See the red. strSearch has never been given a value.
    Code:
    Private Sub cmdSearch_Click()
     Dim lRecCount As Long 'holds # of found
     Dim strSearch As String 'this looks up the text written for search
     Dim strFind As String 'this looks up in SQL for text searched
     'check if a keyword has been entered or not then vbYellow textbox if not + msgbox
     If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
      MsgBox "Please type in your search keyword.", vbInformation, "Keyword Needed!"
      Me.txtSearch.BackColor = vbYellow
      Me.txtSearch.SetFocus
      exit sub
     End if  
     'if record found then highlight RecFound box + filters
     If strSearch = Me.txtSearch.Value Then
      strFind = "SELECT * FROM tblQuestion WHERE ((Question Like ""*" & strSearch & "*"") OR (Possible1 Like ""*" & strSearch & "*"") OR (Possible2 Like ""*" & strSearch & "*"") OR (Possible3 Like ""*"  & strSearch & "*"") OR (Possible4 Like ""*" & strSearch & "*""))"
      Me.RecordSource = strFind
      Me.txtSearch.SetFocus
      Me.RecID.BackColor = vbGreen
      Me.RecID.ForeColor = vbBlack
      exit sub
     End if 
     'if record not found then vbRed textbox + msgbox
     If strFind = Me.txtSearch.Value = False Then
      Me.txtSearch.BackColor = vbRed
      DoCmd.CancelEvent
      Me.txtSearch.SetFocus
      MsgBox "Record not found!", vbExclamation, "Oh Nooooo!"
     End If
    End Sub
    Thanks. I tried that and also added the True value to the red line you mentioned. It continues to do the same. When searching for valid words it states not records found and highlights in RED. Its like it skips the code for searching and validating the correct finding. I may need to just live without the RED portion and leave it as Yellow for reminding the user and Green when found.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I think you missed my point. strSearch will always be NULL unless you explicitly give it a value. Since your code doesn't, it is NULL and the red line compare fails.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Search box not working
    By trident in forum Forms
    Replies: 8
    Last Post: 12-21-2017, 12:02 PM
  2. VBA Search code not working
    By rmrha21 in forum Programming
    Replies: 3
    Last Post: 12-02-2014, 10:59 AM
  3. Search not working in my form
    By scorpion99 in forum Forms
    Replies: 3
    Last Post: 11-06-2014, 01:37 PM
  4. Search box not working
    By banker in forum Forms
    Replies: 2
    Last Post: 08-16-2012, 07:59 PM
  5. Combo Box search not working
    By Lowell in forum Forms
    Replies: 1
    Last Post: 06-02-2012, 12:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums