sql parameter query

[Guest]

Query below prompts for variables, then executes properly. The variables are already defined, and I need the Query t
execute those values. When the query works properly, I'll be passing the parameters to the procedure, but that's a different issue

What am I missing

Thanks
Mik

Public Sub CStores(

Dim c, n, sq As Strin

c = "KINGS
n = "3

sq = "PARAMETERS [c] STRING, [n] STRING;
sq = sq & "INSERT INTO STUDY
sq = sq & "SELECT TOP " & n & " STORE,
sq = sq & "0 AS PANEL FROM DATA
sq = sq & "WHERE COUNTY = '" & c & "';

DoCmd.RunSQL (sq

End Su

 

[Douglas J. Steele]

Remove the line

sq = "PARAMETERS [c] STRING, [n] STRING; "

from your code for now.

In fact, if you're always going to be running your query from code, it's not
really necessary to declare them as parameters so that the query prompts:
you can always prompt for the values yourself from VBA code, using InputBox
(or something more sophisticated if you prefer).

Another comment. You might find that using the Execute method of the
Database object is preferable to using DoCmd.RunSQL for a couple of reasons.

The first is that you won't get the "Access is about to insert n rows"
alert, and the second is that you can specify an optional second parameter
that will raise an error that you can trap for those times when the query
doesn't run properly.

Try something like:

Public Sub CStores()

On Error GoTo Err_CStores

Dim c As String, n As String, sq As String

c = "KINGS"
n = "3"

sq = "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

CurrentDb.Execute sq, dbFailOnError

End_CStores:
Exit Sub

Err_CStores:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume End_CStores

End Sub

If you don't have a reference set to DAO, use 128 instead of dbFailOnError

By the way, note the change I made to the declaration of variables in your
code. It's necessary to declare the variable type for each variable. What
you had was actually declaring c and n to be variants.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Query below prompts for variables, then executes properly. The variables

are already defined, and I need the Query to

execute those values. When the query works properly, I'll be passing the

parameters to the procedure, but that's a different issue.

What am I missing ?

Thanks,
Mike


Public Sub CStores()

Dim c, n, sq As String

c = "KINGS"
n = "3"

sq = "PARAMETERS [c] STRING, [n] STRING; "
sq = sq & "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

DoCmd.RunSQL (sq)

End Sub

 

[Douglas J. Steele]

I should probably mention that you can't specify n as a parameter in SELECT
TOP " & n & " STORE anyhow, which is another reason not to bother using
parameters!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Remove the line

sq = "PARAMETERS [c] STRING, [n] STRING; "

from your code for now.

In fact, if you're always going to be running your query from code, it's not
really necessary to declare them as parameters so that the query prompts:
you can always prompt for the values yourself from VBA code, using InputBox
(or something more sophisticated if you prefer).

Another comment. You might find that using the Execute method of the
Database object is preferable to using DoCmd.RunSQL for a couple of reasons.

The first is that you won't get the "Access is about to insert n rows"
alert, and the second is that you can specify an optional second parameter
that will raise an error that you can trap for those times when the query
doesn't run properly.

Try something like:

Public Sub CStores()

On Error GoTo Err_CStores

Dim c As String, n As String, sq As String

c = "KINGS"
n = "3"

sq = "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

CurrentDb.Execute sq, dbFailOnError

End_CStores:
Exit Sub

Err_CStores:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume End_CStores

End Sub

If you don't have a reference set to DAO, use 128 instead of dbFailOnError

By the way, note the change I made to the declaration of variables in your
code. It's necessary to declare the variable type for each variable. What
you had was actually declaring c and n to be variants.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Query below prompts for variables, then executes properly. The

variables
are already defined, and I need the Query to

execute those values. When the query works properly, I'll be passing

the
parameters to the procedure, but that's a different issue.

What am I missing ?

Thanks,
Mike


Public Sub CStores()

Dim c, n, sq As String

c = "KINGS"
n = "3"

sq = "PARAMETERS [c] STRING, [n] STRING; "
sq = sq & "INSERT INTO STUDY "
sq = sq & "SELECT TOP " & n & " STORE, "
sq = sq & "0 AS PANEL FROM DATA "
sq = sq & "WHERE COUNTY = '" & c & "';"

DoCmd.RunSQL (sq)

End Sub