Exit Sub not working properly?
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?
VB Code:
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
Re: Exit Sub not working properly?
If trim(txtSearchFirst.text) = "" Or trim(txtSearchLast.text) = "" Then
Re: Exit Sub not working properly?
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
Re: Exit Sub not working properly?
Also, your 'IF' starts inside the loop, and the 'ENDIF' is outside the loop.
VB Code:
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
Re: Exit Sub not working properly?
VB Code:
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.
Re: Exit Sub not working properly?
you would have had that error long before the Trim was added ;)
Switch ur above code to this
VB Code:
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
Re: Exit Sub not working properly?
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".
Re: Exit Sub not working properly?
try
VB Code:
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
Re: Exit Sub not working properly?
I dont think its even reading from the dbase... I tried the following procedure and my msgbox came up blank!
VB Code:
Private Sub Command1_Click()
adoSearch.Recordset.MoveFirst
MsgBox (adoSearch.Recordset.Fields("First Name"))
End Sub
Ggggrrrrr.....
Re: Exit Sub not working properly?
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?
Re: Exit Sub not working properly?
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.
Re: Exit Sub not working properly?
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?
VB Code:
If Trim(adoSearch.Recordset.Fields("First Name")) = txtSearchFirst.Text And _
Trim(adoSearch.Recordset.Fields("Last Name")) = txtSearchLast.Text Then
VB Code:
If Trim(adoSearch.Recordset.Fields(0)) = txtSearchFirst.Text And _
Trim(adoSearch.Recordset.Fields(1)) = txtSearchLast.Text Then
Re: Exit Sub not working properly?
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.
VB Code:
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
Re: Exit Sub not working properly?
This is going to sound stupid... but. Why don't you use a WHERE clause (FirstName & LastName), returning ONLY the matched Record?
Re: Exit Sub not working properly?
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".
Re: Exit Sub not working properly?
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.
Re: Exit Sub not working properly?
I found it, but after 5 hours of coding and debugging yesterday I forgot what it was :)