Solved

Output result in msgbox

Posted on 2004-03-23
10
363 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 53

Expert Comment

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

Author Comment

by:CptPicard
Comment Utility
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 125 total points
Comment Utility
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
 

Expert Comment

by:Mahesh-M
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 53

Expert Comment

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

Expert Comment

by:Dhaest
Comment Utility
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
Comment Utility
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
Comment Utility
yeh, sry that was my problem, good job you noticed that :)

thank you for your help.

Paul
0
 
LVL 5

Expert Comment

by:Pi7
Comment Utility
sorry I did not read your question very well
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

15 Experts available now in Live!

Get 1:1 Help Now