Solved

Msgbox to show multiple results!

Posted on 2004-03-26
11
561 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…

705 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