Parameter queries & dialogue boxes

[John De Beer]

[This followup was posted to microsoft.public.access.queries and a copy was sent to the cited author.]

Good Evening from San Diego;

I am trying to use parameter queries in VBA code to build a query and
then use it to transfer the infformation into an Excel spreadsheet.

The query works and the TransferSpreadsheet works but I cannot get the
VBA code to stop putting up a dialogue box to ask for a parameter, and
then using that parameter to develop the spreadsheet.

Attached is the code and below the code are the Debug.Print results.

I had posted this same type of question in the ADO section but gave up
on ADO and am still having problems with DAO.

Any help gratefully received.

John



=================================
Sub ParameterExample()

Dim db As DAO.database
Dim rst As DAO.Recordset
Dim qdfQuery As DAO.QueryDef
Dim prmSinger As DAO.Parameter
Dim strSinger As String

Dim strSQL As String

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete "qryTestOne"
On Error GoTo 0

Set qdfQuery = db.CreateQueryDef("QryTestOne")

strSQL = "Parameters Singer Text;" & _
"Select * From AllCdList Where Artist=Singer;"

qdfQuery.SQL = strSQL
strSinger = "Shakira"

qdfQuery.Parameters(0).Value = strSinger

Debug.Print strSQL
Debug.Print qdfQuery.Parameters.Count
Debug.Print qdfQuery.Parameters(0).Name
Debug.Print qdfQuery.Parameters(0).Type
Debug.Print qdfQuery.Parameters(0).Value

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QryTestOne", "c:\testmeOut4.xls"

End Sub
======================================
Debug.Print Results

Parameters Singer Text;Select * From AllCdList Where Artist=Singer;
1
Singer
10
Shakira

 

[John Vinson]

strSQL = "Parameters Singer Text;" & _
"Select * From AllCdList Where Artist=Singer;"

Try putting square brackets around the parameter: [Singer] instead of
just Singer.

 

[Van T. Dinh]

You resolved the Parameter for the QueryDef Object but the
TransferSpreadsheet used the saved Query whose Parameter hasn't been
resolved.

It is easier to construct the SQL String for the saved Query in which the
Parameter has been resolved. Something like:

strSQL = "Select * From AllCdList Where Artist= ""Shakira"";"
qdfQuery.SQL = strSQL
qdfQuery.Close
DoEvents

DoCmd.TransferSpreadsheet ...

 

[John De Beer]

[This followup was posted to microsoft.public.access.queries and a copy
was sent to the cited author.]

strSQL = "Parameters Singer Text;" & _
"Select * From AllCdList Where Artist=Singer;"

Try putting square brackets around the parameter: [Singer] instead of
just Singer.

Tried, but failed. Thanks.

 

[John de Beer]

You resolved the Parameter for the QueryDef Object but the
TransferSpreadsheet used the saved Query whose Parameter hasn't been
resolved.

It is easier to construct the SQL String for the saved Query in which the
Parameter has been resolved. Something like:

strSQL = "Select * From AllCdList Where Artist= ""Shakira"";"
qdfQuery.SQL = strSQL
qdfQuery.Close
DoEvents

DoCmd.TransferSpreadsheet ...

Your suggestion surely works and thanks but does not solve my problem.

I need to pass a variable (variables) into a query.

I tried the following but it does not work either. Dialogue box still
shows up.

------------
Set qdfQuery = db.CreateQueryDef("QryTestOne")

strSinger = "Shakira"
strSQL = "Select * From AllCdList Where Artist=" & strSinger

qdfQuery.SQL = strSQL
qdfQuery.Close
DoEvents
DoCmd.TransferSpreadsheet

 

[Van T. Dinh]

You didn't notice the multitude of double-quotes I used.

Try:

strSQL = "Select * From AllCdList Where Artist=""" & strSinger & """;"

--
HTH
Van T. Dinh
MVP (Access)



Your suggestion surely works and thanks but does not solve my problem.

 

[John de Beer]

Multitude is right.

This works like a champion. This helps me very much.

Ask the right question in the right discussion group and the answers
comes.

Thanks again.

John