?
Solved

Output result in msgbox

Posted on 2004-03-23
10
Medium Priority
?
370 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
Industry Leaders: 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

764 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