Solved

Output result in msgbox

Posted on 2004-03-23
10
364 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
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 125 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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…

911 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

22 Experts available now in Live!

Get 1:1 Help Now