Retrieve SQL Results

L

lhooker

Registered User.
Local time
Today, 05:43
Joined
Dec 30, 2005
Messages
423
How can I retrieve the results of the below SQL statement in VBA ?

Dim strSQL As String

strSQL = "SELECT [Sales Tax Table].[Category_Name] FROM [Sales Tax Table] WHERE [Company Name] = '" & Me.Name_Choose & "'"
 
Hi. You would use a recordset. For example:
Code: 
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Hope it helps...
 
theDBguy,

Did I miss something ? This did not work.


Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT [Sales Tax Table].[Category_Name] FROM [Sales Tax Table] WHERE [Company Name] = '" & Me.Name_Choose & "'"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
Hi. What does "did not work" mean? What were you expecting to get?


PS. Here's some info on what a recordset object is and what you can do with it. Perhaps if you could tell us what you're planning to do with the result of your SQL, we can recommend a more appropriate object or approach.
 
Run=time error '3061'

Too few parameters, Expected 1.
 
lhooker said:
No, [Sales Tax Table] is a table.
Click to expand...
Then, my guess is Access cannot resolve Me.Name_Choose. Either that or there's some incorrect spelling in there somewhere. Double-check the spelling for [Category_Name] and [Company Name]. Otherwise, consider posting a small copy of your db, so we can take a look.
 
the code needs to be located in a function or sub in the class module for the form where the control Me.Name_Choose exists - perhaps the click event of a button
 
I'm trying to retrieve the value in the 'Category' field of the 'Sales Tax Table' table with a VBA SQL statement. The SQL statement matches the 'Name_Choose' (textbox) with the value in the field 'Company Name' in the 'Sales Tax Table' table. I need to place this value in 'Category_Name' (textbox). Below is what I have.

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT [Category] FROM [Sales Tax Table] WHERE [Company Name] = '" & Me.Name_Choose & "'"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
lhooker said:
I'm trying to retrieve the value in the 'Category' field of the 'Sales Tax Table' table with a VBA SQL statement.
Click to expand...

You can use
Code: 
Dlookup("Category","Sales Tax Table","Company Name= '" & Me.Name_Choose & "'")
 
Debug.Print OR msgbox the content of StrSQL prior to doing the Set rs. That way you can see what you are actually passing prior to passing it.
 
Actually, Tera and lhooker, you would use:

Code: 
Dlookup("Category","[Sales Tax Table]","[Company Name]= '" & Me.Name_Choose & "'")

That is, presuming that [Company Name] is a field of [Sales Tax Table] and so is [Category]. You need the brackets for the domain and for the criterion because the field names have spaces in them. So the brackets are mandatory. Might get away with leaving off the brackets for [Category] since it shows no spaces in the name. Tera is right that you don't need SQL because a DLookup will do what you want from VBA.
 
Code: 
Dlookup("Category","Sales Tax Table","Company Name= '" & Me.Name_Choose & "'")
Do we know from the limited code posted that the sql will only return one record? DLookup won't return more than one so maybe it's not useful here.

The error can be caused by several things but the root cause is that the vba side cannot resolve a reference that points to the container side (objects such as forms). There is more than one solution for your situation; one being to assign the criteria to a variable(s) and use the variable in the sql construct. Aside from the obvious, such as correcting mis-spelled references, you could call a query with defined parameters (parameters property sheet). Or you could define the parameters in code. If those don't appeal to you and a DLookup won't work, then I'm out of ideas for now.

EDIT: OK, so maybe one or two more

- Eval function on a form control reference sometimes works.
- as a test, you could try Openquery method since it's a select query. If it runs, that indicates to me that the .Execute method doesn't pre-process the sql ( as I've read), thus the references aren't evaluated first.
 
Last edited by a moderator:
The_Doc_Man said:
Actually, Tera and lhooker, you would use:

Code: 
Dlookup("Category","[Sales Tax Table]","[Company Name]= '" & Me.Name_Choose & "'")

That is, presuming that [Company Name] is a field of [Sales Tax Table] and so is [Category]. You need the brackets for the domain and for the criterion because the field names have spaces in them. So the brackets are mandatory. Might get away with leaving off the brackets for [Category] since it shows no spaces in the name. Tera is right that you don't need SQL because a DLookup will do what you want from VBA.
Click to expand...
I never use space in my fields naming and didn’t know we have to use breakets in Dlookup in this case. I thought its just for sql statements.
Thanks for the correction and reminder.
 
Micron said:
- as a test, you could try Openquery method since it's a select query. If it runs, that indicates to me that the .Execute method doesn't pre-process the sql ( as I've read), thus the references aren't evaluated first.
Click to expand...
Whoops! I meant to say .Openrecordset might behave similar to what I've read about .Execute.
 
OK, I tried the 'Dlookup' command, but I'm getting a syntax error.

Dlookup("[Category]","[Sales Tax Table]","[Company Name]= '" & Me.Name_Choose & "'")

What's wrong ?
 
Mark,

The msgbox displays the correct SQl code, but how can I retrieve the results.

Dim strSQL As String
Dim rs As DAO.Recordset
MsgBox Me.Name_Choose & " <==> " & Me.Category_Name
strSQL = "SELECT [Category] FROM [Sales Tax Table] WHERE [Company Name] = '" & Me.Name_Choose & "'"
MsgBox strSQL
Debug.Print
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
 
You can't 'run' or execute a SELECT SQL statement in VBA.
Do one of the following.
1. Create a query def or saved query using that sql and then open that for viewing
2. Create a form using that SQL as its record source. View the form

I can't see a syntax error in your DLookup. Does it just need wrapping in the Nz function to handle nulls?
 
You must log in or register to reply here.

Similar threads

C
Get 2 values from popup
Replies
17
Views
445
Gasman
Gasman
C
Problem with 'Like' SQL filter
Replies
6
Views
242
Josef P.
J
C
Date Filter not working
Replies
3
Views
519
Gasman
Gasman
D
Solved Query showing all records instead of just the ones that meet the criteria
Replies
3
Views
286
The_Doc_Man
The_Doc_Man
D
Solved Creating a query with 1 table and 4 queries adds a new record
Replies
10
Views
426
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom