how to re-run a search if nothing was found

[easyrider]

I have a button on a form that gives the user an opportunity to search for a record that contains the address that the user types into an InputBox.

If nothing is found, a MsgBox notifies the user and then closes which returns the user back to the form

What I would like to do is give the user the option to search again if nothing is found instead of them having to click the search button on the form. What would be the best way to accomplish this?



Here's the current code:

Private Sub SearchByAddress()
    Dim S, LN As String
    S = InputBox("Enter the address of the property" & vbCrLf & "(partial address search works as well)", "Find A Property by address")
    If S = "" Then Exit Sub
    LN = Nz(DLookup("PropertyAddress1", "Property_T", "[PropertyAddress1] LIKE ""*" & S & "*"""), "")
    If LN = "" Then
        MsgBox "That address was not found.", vbInformation, "Search Result"
        Exit Sub
    Else
        DoCmd.OpenForm "ViewProperty_F", , , "[PropertyAddress1] = " & " '" & LN & "'"
        Forms!ViewProperty_F.Filter = "PropertyAddress1 LIKE ""*" & S & "*"""
        Forms!ViewProperty_F.FilterOn = True
        Forms!ViewProperty_F.SearchFilter = "ON"
        Forms!ViewProperty_F.SearchFilter.BackColor = vbRed
    End If

End Sub

[davegri]

This should work. I VERY seldom use goto, but in this case it's simple and appropriate. Others might favor a Do While...Loop construct.

Private Sub SearchByAddress()
    Dim S as String, LN As String
TryAgain:
    S = InputBox("Enter the address of the property" & vbCrLf & "(partial address search works as well)", "Find A Property by address")
    If S = "" Then Exit Sub
    LN = Nz(DLookup("PropertyAddress1", "Property_T", "[PropertyAddress1] LIKE ""*" & S & "*"""), "")
    If LN = "" Then
        If MsgBox ("That address was not found." & vbCrlF _
            & "Would you like another Search?", vbQuestion+vbYesNo, "Search Result") = vbYes then
            goto TryAgain
        else    
            Exit Sub
        End If    
    Else
        DoCmd.OpenForm "ViewProperty_F", , , "[PropertyAddress1] = " & " '" & LN & "'"
        Forms!ViewProperty_F.Filter = "PropertyAddress1 LIKE ""*" & S & "*"""
        Forms!ViewProperty_F.FilterOn = True
        Forms!ViewProperty_F.SearchFilter = "ON"
        Forms!ViewProperty_F.SearchFilter.BackColor = vbRed
    End If
End Sub

[easyrider]

@davegri I thought about using a while loop for this, but I kinda like your goto solution. Is there an issue or problem with GoTo?

[davegri]

In the very early days of coding, especially in COBOL, looping structures were not available, and GOTO was used extensively. Coding was done with pencil and paper, then transferred to punched cards for input to the computer. The resulting program was printed out (with or without errors) on that 20 inch wide folding printer paper (nobody had video terminals yet). With all those GOTOs you had to constantly flip back and forth thru all those pages to try and find the target code. The target code could issue another GOTO and on and on. No code automatically returned to the line after the GOTO. This was defined as 'spaghetti code', and nobody liked it. So when better coding procedures became available, GOTO was cast into Hell, and anyone who continued to use it was deemed the Devil's disciple.

[easyrider]

I remember GoTo from the Basic programming days. Thanks for the solution (works like a charm) and the trip down memory lane!

[Welshgasman]

Yet we have to use it for any error procedures?

[Micron]

What would be the best way to accomplish this?

IMO, don't use input box. Create your own form and improve the power and flexibility of the process. Not only could a form contain as many controls as you need to assist in the process it can remain open if nothing is found. Additionally, you could use FAYT to show results related to the input as it is typed in.