Solved

Msgbox to show multiple results!

Posted on 2004-03-26
11
550 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ExtremeD
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Expert Comment

by:robctech
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thx chaps, I'm going to try all your suggestions....... thx.
0
 
LVL 3

Accepted Solution

by:
Bobeo earned 75 total points
Comment Utility
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
Comment Utility
Thank you Bobeo, that's exactly how I wanted it.
and thanks to everyone one else.

Paul :0)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now