Example SQL Select Statement VBA Code

I've been trying to compose a VBA Select Statement in Code in Access 2007 without Success. I have been trying very simple queries and from all of online examples, It's always either complaining of a variable I have set incorrectly or something I do not have decalred.  Can someone post an example of a way to write a VBA select Statement to return data from a table and display it in message box?

Example Concept:
Dim vFirstName as String
Select tblName.FirstName from tblContactNames WHERE vFirstName
MsgBox ("First name is " + vFirstName)

I"m new to VBA and Access and an old school C++ programmer, so I'm starting out.

Your help is appreciated!!
I've tried this code:
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
 
strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = Michelle"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
'intResult = rs("RecordCount")
rs.Close
db.Close

Open in new window

mortensencsCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this:

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = " & Chr(34) &  Michelle & Chr(34)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OOPS ... typo:

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = " & Chr(34) &  "Michelle" & Chr(34)
0
Rey Obrero (Capricorn1)Commented:

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = 'Michelle'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

if you will be using variables

Dim vFirstName as String

vFirstName="Michelle"

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = '" & vFirstName &"'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)


or if there are special characters like apostrophe in the name like L'Gran

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = " & chr(34) & vFirstName & chr(34) &""
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)




0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mortensencsCEOAuthor Commented:
I tried the Capricorn1 Suggestion and :

It give me an Error of:  
Compile error:
Syntax Error

Private Sub Command140_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
Dim vFirstName As String

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = '" &
vFirstName &"'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.Close
db.Close
End Sub
0
mortensencsCEOAuthor Commented:
DatabaseMX:

It did liek your SQL Statement, it just didn'tlike my
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot
statement

Tx you
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
did you try this one:  http:#a22897653  ?

mx
0
mortensencsCEOAuthor Commented:
DatabaseMX

Error is: Run-time erro '91':

Object variable or With block variable not set
0
Rey Obrero (Capricorn1)Commented:
Private Sub Command140_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
Dim vFirstName As String

'1. you did not set db as the currentdb

set db=currentdb

'2 . you did not assign a value for vFirstName

vFirstName="Michelle"

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = '" &
vFirstName &"'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.Close
db.Close
End Sub
0
rheitzmanCommented:
You haven't initialized the db variable...

   set db = CurrentDB

Or use:

   Set rs = CurrentDB.OpenRecordset(strSQL, dbOpenSnapshot)

0
mortensencsCEOAuthor Commented:

did you try this one:  http:#a22897653  ?
mx

Yes, this SQL Statement worked, it just gave me the above error:
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Yes ... as Cap points out ... you left out some code!

mx
0
mortensencsCEOAuthor Commented:
rheitzman...that fixed the problem, I didn't initialize the DB variable.  One more question if I can ask and I'll assign points :)  Thank you all for your help!!

I want to return the data from SQL query and assign it to variables, can you guide me to that?

Example:
strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = '" &
vFirstName &"'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Dim strLastName As String
strLastName = rs.tblContactInfo.LastName

Am I warm..  I am going to finish the SQL statement and what I am going to do is populate my form based on the Query results

Thanks in advance

0
Rey Obrero (Capricorn1)Commented:
mortensencs,

hope you have seen my post at

http:#a22898041



strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = '" &
vFirstName &"'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Dim strLastName As String
strLastName = rs!LastName
0
mortensencsCEOAuthor Commented:
We are very warm capricorn.  
It gives me:
Run-time error '3265':
Item not found in this collection

Something else I'm missing?  TY
0
Rey Obrero (Capricorn1)Commented:
mortensencs,
when you respond especially mentioning errors, post the codes that you are using that raised the error.. We will never know what you are missing if you will not show us what you got...
0
mortensencsCEOAuthor Commented:
No problem

strLastName = rs!LastName <Item not found in this collection>
Its erroring on the above line.

When I debug after I get this error message, it shows that the rs!LastName is not found in this collection.

does this help?
0
Rey Obrero (Capricorn1)Commented:
open table tblContactInfo in design view, see if you have a field named  LastName
0
mortensencsCEOAuthor Commented:
For some odd reason it's named Last Name so when I change the code to show:
strLastName = rs!Last Name

it's throwing a Synax Error (doesn't like the space) is there a way to declare the filed with the space in the name?
Thank you Capricorn1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
if field names are separated by a space you have
to enclosed it with square brackets [ ]

strLastName = rs![Last Name]
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.