Retrieve SQL Results (1 Viewer)

lhooker

Registered User.
Local time
Today, 14:32
Joined
Dec 30, 2005
Messages
399
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 & "'"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:32
Joined
Oct 29, 2018
Messages
21,358
Hi. You would use a recordset. For example:
Code:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Hope it helps...
 

lhooker

Registered User.
Local time
Today, 14:32
Joined
Dec 30, 2005
Messages
399
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)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:32
Joined
Oct 29, 2018
Messages
21,358
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.
 

lhooker

Registered User.
Local time
Today, 14:32
Joined
Dec 30, 2005
Messages
399
Run=time error '3061'

Too few parameters, Expected 1.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:32
Joined
Oct 29, 2018
Messages
21,358
No, [Sales Tax Table] is a table.
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:32
Joined
Feb 19, 2013
Messages
16,553
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
 

deletedT

Guest
Local time
Today, 18:32
Joined
Feb 2, 2019
Messages
1,218
Run=time error '3061'

Too few parameters, Expected 1.

This error mostly happens when one of your field’s name is not correct.
Check for miss type in [Category_Name] Or [Company Name]
 

lhooker

Registered User.
Local time
Today, 14:32
Joined
Dec 30, 2005
Messages
399
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)
 

deletedT

Guest
Local time
Today, 18:32
Joined
Feb 2, 2019
Messages
1,218
I'm trying to retrieve the value in the 'Category' field of the 'Sales Tax Table' table with a VBA SQL statement.

You can use
Code:
Dlookup("Category","Sales Tax Table","Company Name= '" & Me.Name_Choose & "'")
 

Mark_

Longboard on the internet
Local time
Today, 11:32
Joined
Sep 12, 2017
Messages
2,111
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:32
Joined
Feb 28, 2001
Messages
26,999
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.
 

Micron

AWF VIP
Local time
Today, 14:32
Joined
Oct 20, 2018
Messages
3,476
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:

deletedT

Guest
Local time
Today, 18:32
Joined
Feb 2, 2019
Messages
1,218
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.
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

AWF VIP
Local time
Today, 14:32
Joined
Oct 20, 2018
Messages
3,476
- 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.
Whoops! I meant to say .Openrecordset might behave similar to what I've read about .Execute.
 

lhooker

Registered User.
Local time
Today, 14:32
Joined
Dec 30, 2005
Messages
399
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 ?
 

lhooker

Registered User.
Local time
Today, 14:32
Joined
Dec 30, 2005
Messages
399
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)
 

isladogs

MVP / VIP
Local time
Today, 18:32
Joined
Jan 14, 2017
Messages
18,186
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?
 

Users who are viewing this thread

Top Bottom