troubleshooting Question

Msgbox to show multiple results!

Avatar of ExtremeD
ExtremeD asked on
Visual Basic Classic
11 Comments1 Solution606 ViewsLast Modified:
Hello experts,

I have this small bit of code which checks a database in the c:\ drive "postcode.mdb" to see whether the postcode is in the database.

however, there can be more than one of the same postcode!
What it currently does is it just displays the first match it can find if the postcode is in the database it displays a message box showing the postcode they entered and the address associated with that postcode.

I'm not sure of the best way to do this.... but I need it to display all the addresses in the database associated with that postcode...

this is my code:
.......................................................................
Private Sub Command1_Click()

Dim myConn As ADODB.Connection
Set myConn = New ADODB.Connection
myConn.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=c:\postcode.mdb;"
Dim myRec As ADODB.Recordset
Set myRec = New ADODB.Recordset

myRec.Open "SELECT post_code, road FROM Post WHERE post_code='" & Text1.Text & "';", myConn, adOpenKeyset, adLockReadOnly

    With myRec
        If .RecordCount < 1 Then
        MsgBox "Out of registering area!", vbCritical, "Not In Area!"
       
        Else
         
        MsgBox "POST CODE      " & Text1.Text & vbCrLf _
            & "Address            " & myRec!road & vbCrLf & vbCrLf & "Is within the registering area!", vbInformation, "In Practice Area!"

 End If
    End With
myRec.Close
myConn.Close
End Sub
................................................

thank you.
Paul
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros