Else message box won't fire

[brad78]

Hi all,
I am having a problem with one of my forms. Basically, I need to check whether a form value exists already in my table. If the value does exist, the user continues on through the form, however, if the value doesn't exist, I am trying to have a message box alert that no match was found and direct the user to another form. Now, the if statement works if a match is found but if there is no match, nothing happens. Hopefully someone can point me in the right direction.

Private Sub searchULRbtn_Click()
On Error GoTo Err_searchULRbtn_Click
    Dim rs As DAO.Recordset
    Dim searchURL As String
    Dim strSql As String
 
    If (IsNull(Me.page_file_name_search.Value) = True) Then
        MsgBox ("Please copy and paste the URL of the page that links to the above document from Internet Explorer")
    Else
        searchURL = Me.page_file_name_search.Value
        strSql = "Select page_file_name from html_files where page_file_name='" & searchURL & "'"
        Set rs = CurrentDb.OpenRecordset(strSql)
 
        While Not rs.EOF And Not rs.BOF
        rs.Edit
            If rs!page_file_name = searchURL Then
                MsgBox ("A page that matches your search criteria has been found." & vbCrLf & "Please continue adding your documents information")
                Me.page_file_name_search.Locked = False
                Me.page_file_name_search.BackColor = 12632256
            Else
                MsgBox ("NO MATCH FOUND" & vbCrLf & "Please go back to the add html form to add the page to the CMS.")
            End If
        rs.Update
        rs.MoveNext
        Wend
 
        rs.close
        Set rs = Nothing
    End If
Exit_searchULRbtn_Click:
Exit Sub
 
Err_searchULRbtn_Click:
    MsgBox Err.description
    Resume Exit_searchULRbtn_Click
 
End Sub

Thanks
Brad

 

[namliam]

If you dont find a match, your recordsset will be EOF and BOF thus never enter the loop, thus never enter the Else.
Something like so should do the trick

        if rs.EOF then MsgBox ("NO MATCH FOUND" & vbCrLf & "Please go back to the add html form to add the page to the CMS.")

        While Not rs.EOF And Not rs.BOF
        rs.Edit
                MsgBox ("A page that matches your search criteria has been found." & vbCrLf & "Please continue adding your documents information")
                Me.page_file_name_search.Locked = False
                Me.page_file_name_search.BackColor = 12632256
        rs.Update
        rs.MoveNext
        Wend

Happy coding !

 

[brad78]

Namliam,
Thanks for the help. Greatly appreciated!
Brad

If you dont find a match, your recordsset will be EOF and BOF thus never enter the loop, thus never enter the Else.
Something like so should do the trick

        if rs.EOF then MsgBox ("NO MATCH FOUND" & vbCrLf & "Please go back to the add html form to add the page to the CMS.")
 
        While Not rs.EOF And Not rs.BOF
        rs.Edit
                MsgBox ("A page that matches your search criteria has been found." & vbCrLf & "Please continue adding your documents information")
                Me.page_file_name_search.Locked = False
                Me.page_file_name_search.BackColor = 12632256
        rs.Update
        rs.MoveNext
        Wend

Happy coding !