?
Solved

Output result in msgbox

Posted on 2004-03-23
10
Medium Priority
?
375 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…
Suggested Courses

830 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