Solved

Msgbox to show multiple results!

Posted on 2004-03-26
11
558 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

790 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