?
Solved

Msgbox to show multiple results!

Posted on 2004-03-26
11
Medium Priority
?
563 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:ExtremeD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:Bobeo
ID: 10686878
Hi Paul!

Looks like you need to do a loop through all the records in the Recordset?

Try this...


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

    do while not myRec.EOF

        MsgBox "POST CODE      " & Text1.Text & vbCrLf _
            & "Address            " & myRec!road & vbCrLf & vbCrLf & "Is within the registering area!", vbInformation, "In Practice Area!"

    myRec.MoveNext
    loop

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

Think that should do it!
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 10686891
Hi ExtremeD,

> 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

Could be changed to

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

    With myRec
        .MoveFirst
        strMessage = "POST CODE      " & Text1.Text & vbCrLf
        If .RecordCount < 1 Then
        MsgBox "Out of registering area!", vbCritical, "Not In Area!"
       
        Else
            Do
                strMessage = strMessage & "Address            " & myRec!road & vbCrLf & vbCrLf & "Is within the registering area!"
                .MoveNext
            Loop Until .Eof
            Msgbox strMessage, vbInformation, "In Practice Area!"
        End If
    End With
myRec.Close

Tim Cottee
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 

Author Comment

by:ExtremeD
ID: 10687517
Hi Guys,

I like both your suggestions thx for the quick response.

Bobeo, I might have some trouble with your code! The users would probably want everything on one screen (one msgbox).

TimCottee, your code works fine until I search for a postcode which is not in the database and I get the following error.

Run-time error '3021'
Either BOF or EOF is True, or the current records has been deleted.
Requested operation requires a current record.

Also it leaves the last line of the address blank. :(

Paul
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ExtremeD
ID: 10687533
Hi Guys,

I like both your suggestions thx for the quick response.

Bobeo, I might have some trouble with your code! The users would probably want everything on one screen (one msgbox).

TimCottee, your code works fine until I search for a postcode which is not in the database and I get the following error.

Run-time error '3021'
Either BOF or EOF is True, or the current records has been deleted.
Requested operation requires a current record.

Also it leaves the last line of the address blank. :(

Paul
0
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 100 total points
ID: 10687570
ExtremeD,

Just move the .MoveFirst line into the else clause, alternatively use a .Eof test rather than the unreliable .RecordCount property.

Using both of these changes together gives:

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

    With myRec
        strMessage = "POST CODE      " & Text1.Text & vbCrLf
        If .Eof Then
        MsgBox "Out of registering area!", vbCritical, "Not In Area!"
       
        Else
            .MoveFirst
            Do
                strMessage = strMessage & "Address            " & myRec!road & vbCrLf & vbCrLf & "Is within the registering area!"
                .MoveNext
            Loop Until .Eof
            Msgbox strMessage, vbInformation, "In Practice Area!"
        End If
    End With
myRec.Close

Tim.
0
 
LVL 2

Expert Comment

by:robctech
ID: 10688184
You may want to create a secondary form with a listbox on it.  You can then show that Form modaly and they will see all the resulsts.

Private Sub Scan_PostalCodes(CodeToSearch as string)

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

    With myRec
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .CursorType = adOpenKeySet
        .Source = "SELECT post_code, road FROM Post WHERE post_code= '" & CodeToSearch & "'"
        .Open
        if .RecordCount > 0 Then
           Do While Not .EOF
              '//PostResult Is a List Box On frmResults
              frmResults.PostResult.AddItem "[" & .Fields("Post_Code") & "] " & road
              .MoveNext
           Loop
           frmResults.Show vbModal
        Else
           MsgBox "Out of registering area!", vbCritical, "Not In Area!"
        End If
        .Close
    End With
End Sub

Just another approach you can take...hope all helps
0
 
LVL 2

Expert Comment

by:robctech
ID: 10688191
Ooops...remove

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

from what I posted
0
 
LVL 2

Assisted Solution

by:robctech
robctech earned 100 total points
ID: 10688208
Oh man hate days like this...here is the proper code

Private Sub Scan_PostalCodes(CodeToSearch as string)

    With myRec
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .CursorType = adOpenKeySet
        .ActiveConnection = myConn
        .Source = "SELECT post_code, road FROM Post WHERE post_code= '" & CodeToSearch & "'"
        .Open
        if .RecordCount > 0 Then
           Do While Not .EOF
              '//PostResult Is a List Box On frmResults
              frmResults.PostResult.AddItem "[" & .Fields("Post_Code") & "] " & road
              .MoveNext
           Loop
           frmResults.Show vbModal
        Else
           MsgBox "Out of registering area!", vbCritical, "Not In Area!"
        End If
        .Close
    End With
End Sub
0
 

Author Comment

by:ExtremeD
ID: 10688218
thx chaps, I'm going to try all your suggestions....... thx.
0
 
LVL 3

Accepted Solution

by:
Bobeo earned 300 total points
ID: 10688377
Just in response to your comment on my post...


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
    dim AddMSG as String
    do while not myRec.EOF

        AddMSG = AddMSG & "POST CODE      " & Text1.Text & vbCrLf _
            & "Address            " & myRec!road & vbCrLf & vbCrLf

    myRec.MoveNext
    loop

msgbox AddMSG &  " are within the registering area!", vbInformation, "In Practice Area!"
 End If
    End With
myRec.Close
myConn.Close
End Sub
0
 

Author Comment

by:ExtremeD
ID: 10690090
Thank you Bobeo, that's exactly how I wanted it.
and thanks to everyone one else.

Paul :0)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month12 days, 8 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question