Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Example SQL Select Statement VBA Code

Posted on 2008-11-06
19
Medium Priority
?
8,433 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:mortensencs
  • 8
  • 6
  • 4
  • +1
19 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 300 total points
ID: 22897648
Try this:

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = " & Chr(34) &  Michelle & Chr(34)
0
 
LVL 75
ID: 22897653
OOPS ... typo:

strSQL = "SELECT * FROM tblContactInfo WHERE tblContactInfo.FirstName = " & Chr(34) &  "Michelle" & Chr(34)
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1400 total points
ID: 22897728

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:mortensencs
ID: 22897987
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
 

Author Comment

by:mortensencs
ID: 22898001
DatabaseMX:

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

Tx you
0
 
LVL 75
ID: 22898022
did you try this one:  http:#a22897653  ?

mx
0
 

Author Comment

by:mortensencs
ID: 22898028
DatabaseMX

Error is: Run-time erro '91':

Object variable or With block variable not set
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22898041
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
 
LVL 7

Assisted Solution

by:rheitzman
rheitzman earned 300 total points
ID: 22898042
You haven't initialized the db variable...

   set db = CurrentDB

Or use:

   Set rs = CurrentDB.OpenRecordset(strSQL, dbOpenSnapshot)

0
 

Author Comment

by:mortensencs
ID: 22898048

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

Yes, this SQL Statement worked, it just gave me the above error:
0
 
LVL 75
ID: 22898062
Yes ... as Cap points out ... you left out some code!

mx
0
 

Author Comment

by:mortensencs
ID: 22898592
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22898634
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
 

Author Comment

by:mortensencs
ID: 22898663
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22898743
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
 

Author Comment

by:mortensencs
ID: 22898830
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22898867
open table tblContactInfo in design view, see if you have a field named  LastName
0
 

Accepted Solution

by:
mortensencs earned 0 total points
ID: 22899046
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1400 total points
ID: 22899075
if field names are separated by a space you have
to enclosed it with square brackets [ ]

strLastName = rs![Last Name]
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

581 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