Solved

Msgbox to show multiple results!

Posted on 2004-03-26
11
556 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
  • 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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 

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 25 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 25 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 75 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

810 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