Problem With VBA Code - Help

[Guest]

I need help. I cannot identify the problem. I am trying to assign a query
output to certain variables. I am getting a 3078 error and it looks like it
is happening on line 12. They query is working fine when I run it manually.
Please see the code below.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim m1 As String

strSQL = "SELECT TOP 1 captionReference.metric1"
strSQL = strSQL & " FROM captionReference"
strSQL = strSQL & " WHERE
(((captionReference.id)=[Forms]![3-1_modAdVariables]![clientId]));"

Set db = CurrentDb
Set rst = db.OpenRecordset("strSQL")

m1 = rst.Fields("[metric1]")

 

[Guest]

Remove the "'s from Line 12 -

Set rst = db.OpenRecordset(strSQL)

Incidentally, its generally easier and clearer if you use the continuation
character when building a SQL string:

strSQL = "SELECT TOP 1 captionReference.metric1" & _
" FROM captionReference" & _
" WHERE (((captionReference.id)=[Forms]![3-1_modAdVariables]!
[clientId]));"

BW

 

[Douglas J. Steele]

Put your reference to the form field outside of the SQL:

strSQL = "SELECT TOP 1 captionReference.metric1" & _
" FROM captionReference" & _
" WHERE (((captionReference.id)=" & _
[Forms]![3-1_modAdVariables]![clientId]

That assume Id is numeric: if it's text, you'll want

strSQL = "SELECT TOP 1 captionReference.metric1" & _
" FROM captionReference" & _
" WHERE (((captionReference.id)='" & _
[Forms]![3-1_modAdVariables]![clientId] & "'"

Exagerated for clarity, that's

strSQL = "SELECT TOP 1 captionReference.metric1" & _
" FROM captionReference" & _
" WHERE (((captionReference.id)= ' " & _
[Forms]![3-1_modAdVariables]![clientId] & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Remove the "'s from Line 12 -

Set rst = db.OpenRecordset(strSQL)

Incidentally, its generally easier and clearer if you use the
continuation
character when building a SQL string:

strSQL = "SELECT TOP 1 captionReference.metric1" & _
" FROM captionReference" & _
" WHERE
(((captionReference.id)=[Forms]![3-1_modAdVariables]!
[clientId]));"

BW

Thank you for taking a look at my code. I made the changes you suggested
and am still seeing errors. I get a 3075 error on line 12. Here is the
updated code. Any ideas?

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim m1 As String

strSQL = "SELECT TOP 1 captionReference.metric1" & _
" FROM captionReference" & _
" WHERE (((captionReference.id)=[Forms]![3-1_modAdVariables]!"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

m1 = rst.Fields("[metric1]")

 

[Douglas J. Steele]

Make certain you haven't mistyped anything in that SQL string.

You might check the actual error message: it should give you a bit more
detail (the generic message is "|1 in query expression '|2'.", where |1 and
|2 will be replaced by values.)

 

[Tim Ferguson]

strSQL = "SELECT TOP 1 captionReference.metric1"
strSQL = strSQL & " FROM captionReference"
strSQL = strSQL & " WHERE
(((captionReference.id)=[Forms]![3-1_modAdVariables]![clientId]));"

What is the point of a TOP clause if there is no ORDER BY..?


Tim F

 

[Guest]

One problem is that you have '(((' at the beginning of the WHERE clause and
you never close all the parentheses. There's no need for any parentheses, so
I'd just remove those. It looks like you copied the SQL from the SQL of a
stored query and then pasted that into VBA . Access always puts those (((
there but you don't need them. The other possible problem is that the value
stored in

[Forms]![3-1_modAdVariables]![clientId]

might not have a valid numeric value. SO you need to make sure you have a
valid value in that field. Either put a breakpoint at the 'strSQL=' statement
to check it or else put a msgbox statement right before that:

msgbox [Forms]![3-1_modAdVariables]![clientId]

Assuming you have a valid value in the form field, this should work:

strSQL = "SELECT TOP 1 captionReference.metric1" & _
" FROM captionReference" & _
" WHERE captionReference.id=" & _
[Forms]![3-1_modAdVariables]![clientId]