Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Output result in msgbox

Posted on 2004-03-23
10
Medium Priority
?
373 Views
Last Modified: 2010-05-01
Hi guys,

I have this little bit of code which checks whether a person is within the registering area by validating there post code.

however in the validated message box, I'd like it to display the post code and the address associated with it.

I have an access database called "post-codes" and a table called "post" and within that table I have 2 fields "address" and "post";
the address field obviously displays the address associated with that post code, and the post well that displays the post code :)

So once the post code has been entered, if it's in the area, a message box displays saying something like the (post code "" NewLine Address "" New line) is within the registering area!
so it checks that information from access and displays it in the msgbox!

This is my code I'm using.

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 FROM Post WHERE post_code='" & Text1.Text & "';", myConn, adOpenKeyset, adLockReadOnly
    With myRec
        If .EOF Then
            MsgBox "Out of registering area!"
        Else
            MsgBox "In Registering Area!"
        End If
    End With
myRec.Close
myConn.Close
End Sub

many thanks
Paul
0
Comment
Question by:CptPicard
[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
10 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 10656209
MsgBox "post code " & Text1.Text  & vbcrlf & " is within the registering area!"
0
 

Author Comment

by:CptPicard
ID: 10656217
Dhaest,

thankyou but I already know how to do that... it's displaying the address associated with that post code from the access database im having trouble with!
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 10656227
Then you have to add it to your sql (fe address).

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

MsgBox "post code " & Text1.Text  & vbcrlf _
            & "address " & myrec.fields!adress & vbcrlf
             & " is within the registering area!"
0
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!

 

Expert Comment

by:Mahesh-M
ID: 10656242
Just to add what Dhaest has said. If you want to display address as well then you have to get address in the select query that you are using.
0
 

Author Comment

by:CptPicard
ID: 10656247
I get this error.

run-time error '3265':

Item cannot  be found in the collection corresponding to the requested name or ordinal.

I only changed this part (myrec.fields!road) so that it matches my field in the table.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 10656256
Try myrec.fields("road") or myrec!road
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 10656262
Make sure you added road to your sql or change it to * in your sql
myRec.Open "SELECT post_code, road FROM Post WHERE post_code='" & Text1.Text & "';", myConn, adOpenKeyset, adLockReadOnly
or
myRec.Open "SELECT * FROM Post WHERE post_code='" & Text1.Text & "';", myConn, adOpenKeyset, adLockReadOnly
0
 
LVL 5

Expert Comment

by:Pi7
ID: 10656264
From your code I guess the program will search the database for the text in Textbox1 and report the appropriate message.If that is the case then modify  the last bit of your code to look like this:

   With myRec
        If .RecordCount < 1 Then
                   MsgBox "Out of registering area!"
        Else
            MsgBox "In Registering Area!"
        End If
    End With
myRec.Close
myConn.Close
End Sub
0
 

Author Comment

by:CptPicard
ID: 10656270
yeh, sry that was my problem, good job you noticed that :)

thank you for your help.

Paul
0
 
LVL 5

Expert Comment

by:Pi7
ID: 10656287
sorry I did not read your question very well
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…
Suggested Courses

650 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