Exit Sub not working properly?

[phoeneous]

This procedure is used to search through a database of customers via first and last names. If the user leaves the txtSearchFirst or txtSearchLast fields empty, a msgbox is displayed and the search is cancelled. Why is the sub continuing to the next lines of code if it should be exiting the sub?

Private Sub cmdSearch_Click()
 
    If txtSearchFirst = "" Or txtSearchLast = "" Then
        MsgBox "Missing information for a valid search", vbCritical, "Warning!"
        Exit Sub
    End If
 
    adoSearch.Recordset.MoveFirst
        
    Do While Not adoSearch.Recordset.EOF
        If adoSearch.Recordset.Fields("First Name").Value = txtSearchFirst And _
            adoSearch.Recordset.Fields("Last Name").Value = txtSearchLast Then
            MsgBox "Match Found!", vbInformation, ""
            fraResults.Visible = True
            imgResultsGirl.Visible = True
            imgWelcomeBack.Visible = True
            lblWelcomeName.Caption = txtResultFirst & " " & txtResultLast
            lblWelcomeName.Visible = True
            txtResultFirst = adoSearch.Recordset.Fields("First Name")
            txtResultLast = adoSearch.Recordset.Fields("Last Name")
            txtResultStreet = adoSearch.Recordset.Fields("Street")
            txtResultCity = adoSearch.Recordset.Fields("City")
            txtResultState = adoSearch.Recordset.Fields("State")
            txtResultZip = adoSearch.Recordset.Fields("ZipCode")
            txtResultPhone = adoSearch.Recordset.Fields("Phone")
            txtResultFax = adoSearch.Recordset.Fields("Fax")
            txtResultEmail = adoSearch.Recordset.Fields("Email")
            adoSearch.Recordset.MoveNext
    Loop
            Else
            MsgBox "No Match Found", vbInformation, ""
            Exit Sub
        End If
            
End Sub

[Pasvorto]

If trim(txtSearchFirst.text) = "" Or trim(txtSearchLast.text) = "" Then

[kfcSmitty]

thats your code? it looks like you should be getting an error cause the loop loops through only 1 part of the IF

Put the whole if statement in the loop

[Pasvorto]

Also, your 'IF' starts inside the loop, and the 'ENDIF' is outside the loop.

Do While Not adoSearch.Recordset.EOF
        If adoSearch.Recordset.Fields("First Name").Value = txtSearchFirst And _
            adoSearch.Recordset.Fields("Last Name").Value = txtSearchLast Then
            MsgBox "Match Found!", vbInformation, ""
            fraResults.Visible = True
            imgResultsGirl.Visible = True
            imgWelcomeBack.Visible = True
            lblWelcomeName.Caption = txtResultFirst & " " & txtResultLast
            lblWelcomeName.Visible = True
            txtResultFirst = adoSearch.Recordset.Fields("First Name")
            txtResultLast = adoSearch.Recordset.Fields("Last Name")
            txtResultStreet = adoSearch.Recordset.Fields("Street")
            txtResultCity = adoSearch.Recordset.Fields("City")
            txtResultState = adoSearch.Recordset.Fields("State")
            txtResultZip = adoSearch.Recordset.Fields("ZipCode")
            txtResultPhone = adoSearch.Recordset.Fields("Phone")
            txtResultFax = adoSearch.Recordset.Fields("Fax")
            txtResultEmail = adoSearch.Recordset.Fields("Email")
            adoSearch.Recordset.MoveNext
    Loop
            Else
            MsgBox "No Match Found", vbInformation, ""
            Exit Sub
        End If

Edit: Added [vbcode][/vbcode] tags for clairty. - Hack

[phoeneous]

Private Sub cmdSearch_Click()
 
    If Trim(txtSearchFirst.Text) = "" Or Trim(txtSearchLast.Text) = "" Then
        MsgBox "Missing information for a valid search", vbCritical, "Warning!"
        Exit Sub
    End If
 
    adoSearch.Recordset.MoveFirst
        
    Do While Not adoSearch.Recordset.EOF
        If adoSearch.Recordset.Fields("First Name").Value = txtSearchFirst And _
            adoSearch.Recordset.Fields("Last Name").Value = txtSearchLast Then
            MsgBox "Match Found!", vbInformation, ""
            fraResults.Visible = True
            imgResultsGirl.Visible = True
            imgWelcomeBack.Visible = True
            lblWelcomeName.Caption = txtResultFirst & " " & txtResultLast
            lblWelcomeName.Visible = True
            txtResultFirst = adoSearch.Recordset.Fields("First Name")
            txtResultLast = adoSearch.Recordset.Fields("Last Name")
            txtResultStreet = adoSearch.Recordset.Fields("Street")
            txtResultCity = adoSearch.Recordset.Fields("City")
            txtResultState = adoSearch.Recordset.Fields("State")
            txtResultZip = adoSearch.Recordset.Fields("ZipCode")
            txtResultPhone = adoSearch.Recordset.Fields("Phone")
            txtResultFax = adoSearch.Recordset.Fields("Fax")
            txtResultEmail = adoSearch.Recordset.Fields("Email")
            adoSearch.Recordset.MoveNext
            Else
            MsgBox "No Match Found", vbInformation, ""
            Exit Sub
        End If
Loop
   
End Sub

I think my "No Match Found" MsgBox is in the wrong place because it shold be finding the info that Im typing in. I verified by looking at the database.

[kfcSmitty]

you would have had that error long before the Trim was added

Switch ur above code to this

Do While Not adoSearch.Recordset.EOF
        If adoSearch.Recordset.Fields("First Name").Value = txtSearchFirst And _
            adoSearch.Recordset.Fields("Last Name").Value = txtSearchLast Then
            MsgBox "Match Found!", vbInformation, ""
            fraResults.Visible = True
            imgResultsGirl.Visible = True
            imgWelcomeBack.Visible = True
            lblWelcomeName.Caption = txtResultFirst & " " & txtResultLast
            lblWelcomeName.Visible = True
            txtResultFirst = adoSearch.Recordset.Fields("First Name")
            txtResultLast = adoSearch.Recordset.Fields("Last Name")
            txtResultStreet = adoSearch.Recordset.Fields("Street")
            txtResultCity = adoSearch.Recordset.Fields("City")
            txtResultState = adoSearch.Recordset.Fields("State")
            txtResultZip = adoSearch.Recordset.Fields("ZipCode")
            txtResultPhone = adoSearch.Recordset.Fields("Phone")
            txtResultFax = adoSearch.Recordset.Fields("Fax")
            txtResultEmail = adoSearch.Recordset.Fields("Email")
            adoSearch.Recordset.MoveNext
            Else
            MsgBox "No Match Found", vbInformation, ""
            Exit Sub
        End If
Loop

[phoeneous]

Yeah, I moved the loop to the outside but I think my "No Match Found" msgbox is in the wrong place because it isnt finding the info that Im typing in. I verified by looking at the records in the dbase and searched for that same exact name yet I still get "No Match Found".

[kfcSmitty]

try

Do While Not adoSearch.Recordset.EOF
        If Trim(adoSearch.Recordset.Fields("First Name")) = txtSearchFirst.Text And _
            Trim(adoSearch.Recordset.Fields("Last Name")) = txtSearchLast.Text Then
            MsgBox "Match Found!", vbInformation, ""
            fraResults.Visible = True
            imgResultsGirl.Visible = True
            imgWelcomeBack.Visible = True
            lblWelcomeName.Caption = txtResultFirst & " " & txtResultLast
            lblWelcomeName.Visible = True
            txtResultFirst = adoSearch.Recordset.Fields("First Name")
            txtResultLast = adoSearch.Recordset.Fields("Last Name")
            txtResultStreet = adoSearch.Recordset.Fields("Street")
            txtResultCity = adoSearch.Recordset.Fields("City")
            txtResultState = adoSearch.Recordset.Fields("State")
            txtResultZip = adoSearch.Recordset.Fields("ZipCode")
            txtResultPhone = adoSearch.Recordset.Fields("Phone")
            txtResultFax = adoSearch.Recordset.Fields("Fax")
            txtResultEmail = adoSearch.Recordset.Fields("Email")
            adoSearch.Recordset.MoveNext
            Else
            MsgBox "No Match Found", vbInformation, ""
            Exit Sub
        End If
Loop

all i can really think of

[phoeneous]

I dont think its even reading from the dbase... I tried the following procedure and my msgbox came up blank!

Private Sub Command1_Click()
 
    adoSearch.Recordset.MoveFirst
    MsgBox (adoSearch.Recordset.Fields("First Name"))
    
End Sub

Ggggrrrrr.....

[kfcSmitty]

you know what? if you dont find a record you make it move on

try putting "adoSearch.Recordset.MoveNext" outside the loop


can you post all your code? the connection string and everything please?

[phoeneous]

The problem is that the dbase is overwriting the first record when I click on save. I need to modify some of my procedures, be back later.

[phoeneous]

Well, I still cant figure it out. I thought the dbase was overwriting the first record but it doesnt.

Is my syntax correct for comparing the txtsearchfirst and txtsearchlast fields to the fileds of the dbase?

Do these two statements do the same things?

If Trim(adoSearch.Recordset.Fields("First Name")) = txtSearchFirst.Text And _
            Trim(adoSearch.Recordset.Fields("Last Name")) = txtSearchLast.Text Then

If Trim(adoSearch.Recordset.Fields(0)) = txtSearchFirst.Text And _
            Trim(adoSearch.Recordset.Fields(1)) = txtSearchLast.Text Then

[dglienna]

Those statements are the same. Try it like this. If it doesn't find a match, it will loop until the end and print an error message. If it does find a match, it will exit the sub without reading any more records.

Private Sub cmdSearch_Click()
 
    If Trim(txtSearchFirst.Text) = "" Or Trim(txtSearchLast.Text) = "" Then
        MsgBox "Missing information for a valid search", vbCritical, "Warning!"
        Exit Sub
    End If
 
    adoSearch.Recordset.MoveFirst
        
    Do While Not adoSearch.Recordset.EOF
        If adoSearch.Recordset.Fields("First Name").Value = txtSearchFirst And _
            adoSearch.Recordset.Fields("Last Name").Value = txtSearchLast Then
            MsgBox "Match Found!", vbInformation, ""
            fraResults.Visible = True
            imgResultsGirl.Visible = True
            imgWelcomeBack.Visible = True
            lblWelcomeName.Caption = txtResultFirst & " " & txtResultLast
            lblWelcomeName.Visible = True
            txtResultFirst = adoSearch.Recordset.Fields("First Name")
            txtResultLast = adoSearch.Recordset.Fields("Last Name")
            txtResultStreet = adoSearch.Recordset.Fields("Street")
            txtResultCity = adoSearch.Recordset.Fields("City")
            txtResultState = adoSearch.Recordset.Fields("State")
            txtResultZip = adoSearch.Recordset.Fields("ZipCode")
            txtResultPhone = adoSearch.Recordset.Fields("Phone")
            txtResultFax = adoSearch.Recordset.Fields("Fax")
            txtResultEmail = adoSearch.Recordset.Fields("Email")
            exit sub
        End If
        adoSearch.Recordset.MoveNext
Loop
        MsgBox "No Match Found", vbInformation, ""
   End Sub

[Bruce Fox]

This is going to sound stupid... but. Why don't you use a WHERE clause (FirstName & LastName), returning ONLY the matched Record?

[phoeneous]

Okay, that finds the match but something keeps overwriting the first record in the database and it only does it when I run the search.

It also "no match found" for every record if it doesnt match the search. So, out of 12 records, 11 dont match I will get a msgbox 11 times saying "no match found".

[dglienna]

1) The msgbox is outside of the loop, so it gets called once, every time you call the search routine. 2) Nothing in the sub does anything TO the recordset.
You must have something else looping thru the records, and changing them.
Not that it would matter, unless you are displaying or saving them somewhere else.

You should place a breakpoint in your code, and step (F8) thru it to see what it's doing.

[phoeneous]

I found it, but after 5 hours of coding and debugging yesterday I forgot what it was