How to write a parameter query in recordset

[Junior728]

Hi,

I try to type a proper parameter query in acess query as below but either it
has extra or missing brackets? It has gave me error no, but i do not
understand.

See extract from my vba:
====>>>> SQL="SELECT* FROM 2007 Full WHERE Account = [Pls enter customer
code]"

'2007 Full is my table name
'Account is my query field that require user input

Set rst = CurrentDb.OpenRecordset(SQL) 'My QueryName

Another qns is if i want to include more thatn 1 parameter query that ask
for user input , how do i do that?

thanks.

 

[John Spencer]

First problem is that you are going to need to surround 2007 Full with
square brackets. Why? Because you have used a space in the table name.
Names of fields and tables should consist of only letters, spaces and
underscore characters (and should contain at least on letter). If you don't
follow that rule you must surround the name with square brackets.

Next problem, you need a space between "SELECT" and "*".

SQL = "SELECT * FROM [2007 Full] WHERE Account = [Please enter customer
code]"

Since I don't know how you plan to use this query string, I won't comment on
whether or not it will work. If you are building a saved query then this
will probably work, but if you are trying to use this to get a recordset
then it will probably fail.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Junior728]

Hi,

thanks for the info. I have tried to run it but still has the error no. 3061
Too few parameters. Expected 2.

I think in my query, there are 2 parameters that require user input. but i
dont know how to phrase it in vba. How can i get it to recognise the user
input and use the input to get a recordset ?

my full script as below:
=======================
Option Compare Database

Public Sub CopyRecordset2XL()
Dim objXLApp As Object 'Excel.Application
Dim objXLWb As Object 'Excel.Workbook
Dim objXLWs As Object 'Excel.Worksheet
Dim strWorkBook As String 'name of workbook
Dim strWorkSheet As String 'name of worksheet
Dim lngSheets As Long 'sheet number
Dim lngCount As Long 'counter

Dim db As DAO.Database
Dim rst1 As DAO.Recordset 'Required Field
Dim rst2 As DAO.Recordset 'Required Field
Dim strSQL As String 'data to export. Required Field
Dim qdf As QueryDef
Dim qdf2 As QueryDef

strWorkBook = "\\txfil001\MKoh$\My WorkStation\MRPbyPN.xls"

'name of the recordset to copy,QueryName
strSQL = "Year 2007 FC Query by Cust, by CPN-Done by ML"

SQL = "SELECT * FROM [2007 Full] WHERE Account = [Please enter customer
code]"

'SQL2 = "SELECT * FROM [2007 Full] WHERE Customer PN = [Pls enter Cust PN]"

Set rst1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
'Set rst2 = CurrentDb.OpenRecordset(SQL2, dbOpenSnapshot)

'Start Excel
Set objXLApp = CreateObject("Excel.Application")

'only create workbooks with 1 sheet
lngCount = objXLApp.SheetsInNewWorkbook 'save user's setting
objXLApp.SheetsInNewWorkbook = 1 'set for only 1 sheet
Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
objXLApp.SheetsInNewWorkbook = lngCount 'restore user's setting

strWorkSheet = "Sheet1"

Set objXLWs = objXLWb.Worksheets(strWorkSheet)

objXLWs.Range("A2").CopyFromRecordset rst
objXLWs.Columns.AutoFit

'Save wb
objXLWb.Save
objXLWb.Close

'Need to close off the Excel Book Object
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset object
rst.Close

End Sub

================

First problem is that you are going to need to surround 2007 Full with
square brackets. Why? Because you have used a space in the table name.
Names of fields and tables should consist of only letters, spaces and
underscore characters (and should contain at least on letter). If you don't
follow that rule you must surround the name with square brackets.

Next problem, you need a space between "SELECT" and "*".

SQL = "SELECT * FROM [2007 Full] WHERE Account = [Please enter customer
code]"

Since I don't know how you plan to use this query string, I won't comment on
whether or not it will work. If you are building a saved query then this
will probably work, but if you are trying to use this to get a recordset
then it will probably fail.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I try to type a proper parameter query in acess query as below but either
it
has extra or missing brackets? It has gave me error no, but i do not
understand.

See extract from my vba:
====>>>> SQL="SELECT* FROM 2007 Full WHERE Account = [Pls enter customer
code]"

'2007 Full is my table name
'Account is my query field that require user input

Set rst = CurrentDb.OpenRecordset(SQL) 'My QueryName

Another qns is if i want to include more thatn 1 parameter query that ask
for user input , how do i do that?

thanks.

 

[Junior728]

Hi,

I try another method by using QueryRef parameters...but i got another error
msg:
"Subscript out of range. ..."...but is there anything wrong with my
parameter collection as below or is it they cannot export the data onto excel?

Option Compare Database

Public Sub CopyRecordset2XL()
Dim objXLApp As Object
Dim objXLWb As Object
Dim objXLWs As Object
Dim strWorkBook As String
Dim strWorkSheet As String
Dim lngSheets As Long 'sheet number
Dim lngCount As Long 'counter

Dim MyDB As Database
Dim RecordMRP As Recordset
Dim QueryMRP As QueryDef
Dim Account As String
Dim Customer_PN As String

strWorkBook = "\\txfil001\MKoh$\My WorkStation\MRPbyPN.xls"

Set QueryMRP = CurrentDb.QueryDefs("Year 2007 FC Query by Cust, by
CPN-Done by ML")

With QueryMRP
.Parameters("[Pls enter Cust Code]") = Account
.Parameters("[Pls enter Cust PN]") = Customer_PN
End With

Set RecordMRP = QueryMRP.OpenRecordset()


Set objXLApp = CreateObject("Excel.Application")

'name and full path to use to save the xls file
'only create workbooks with 1 sheet
lngCount = objXLApp.SheetsInNewWorkbook 'save user's setting
objXLApp.SheetsInNewWorkbook = 1 'set for only 1 sheet
Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
objXLApp.SheetsInNewWorkbook = lngCount 'restore user's setting

strWorkSheet = "Sheet1"

Set objXLWs = objXLWb.Worksheets(strWorkSheet) -----> Error stuck here.
objXLWs.Range("A2").CopyFromRecordset RecordMRP 'I want to copy start
from Header!
objXLWs.Columns.AutoFit

'Save wb
objXLWb.Save
objXLWb.Close

'Need to close off the Excel Book Object
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset object
RecordMRP.Close

End Sub

thanks.

Hi,

thanks for the info. I have tried to run it but still has the error no. 3061
Too few parameters. Expected 2.

I think in my query, there are 2 parameters that require user input. but i
dont know how to phrase it in vba. How can i get it to recognise the user
input and use the input to get a recordset ?

my full script as below:
=======================
Option Compare Database

Public Sub CopyRecordset2XL()
Dim objXLApp As Object 'Excel.Application
Dim objXLWb As Object 'Excel.Workbook
Dim objXLWs As Object 'Excel.Worksheet
Dim strWorkBook As String 'name of workbook
Dim strWorkSheet As String 'name of worksheet
Dim lngSheets As Long 'sheet number
Dim lngCount As Long 'counter

Dim db As DAO.Database
Dim rst1 As DAO.Recordset 'Required Field
Dim rst2 As DAO.Recordset 'Required Field
Dim strSQL As String 'data to export. Required Field
Dim qdf As QueryDef
Dim qdf2 As QueryDef

strWorkBook = "\\txfil001\MKoh$\My WorkStation\MRPbyPN.xls"

'name of the recordset to copy,QueryName
strSQL = "Year 2007 FC Query by Cust, by CPN-Done by ML"

SQL = "SELECT * FROM [2007 Full] WHERE Account = [Please enter customer
code]"

'SQL2 = "SELECT * FROM [2007 Full] WHERE Customer PN = [Pls enter Cust PN]"

Set rst1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
'Set rst2 = CurrentDb.OpenRecordset(SQL2, dbOpenSnapshot)

'Start Excel
Set objXLApp = CreateObject("Excel.Application")

'only create workbooks with 1 sheet
lngCount = objXLApp.SheetsInNewWorkbook 'save user's setting
objXLApp.SheetsInNewWorkbook = 1 'set for only 1 sheet
Set objXLWb = objXLApp.Workbooks.Open(strWorkBook)
objXLApp.SheetsInNewWorkbook = lngCount 'restore user's setting

strWorkSheet = "Sheet1"

Set objXLWs = objXLWb.Worksheets(strWorkSheet)

objXLWs.Range("A2").CopyFromRecordset rst
objXLWs.Columns.AutoFit

'Save wb
objXLWb.Save
objXLWb.Close

'Need to close off the Excel Book Object
Set objXLWs = Nothing
Set objXLApp = Nothing
' Destroy the recordset object
rst.Close

End Sub

================

First problem is that you are going to need to surround 2007 Full with
square brackets. Why? Because you have used a space in the table name.
Names of fields and tables should consist of only letters, spaces and
underscore characters (and should contain at least on letter). If you don't
follow that rule you must surround the name with square brackets.

Next problem, you need a space between "SELECT" and "*".

SQL = "SELECT * FROM [2007 Full] WHERE Account = [Please enter customer
code]"

Since I don't know how you plan to use this query string, I won't comment on
whether or not it will work. If you are building a saved query then this
will probably work, but if you are trying to use this to get a recordset
then it will probably fail.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,

I try to type a proper parameter query in acess query as below but either
it
has extra or missing brackets? It has gave me error no, but i do not
understand.

See extract from my vba:
====>>>> SQL="SELECT* FROM 2007 Full WHERE Account = [Pls enter customer
code]"

'2007 Full is my table name
'Account is my query field that require user input

Set rst = CurrentDb.OpenRecordset(SQL) 'My QueryName

Another qns is if i want to include more thatn 1 parameter query that ask
for user input , how do i do that?

thanks.