OpenRecordset parameter [Forms]... reference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I get a "Too few parameters. Expected: 1" on the 2nd line below:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyQueryName")

I have isolated the problem to a a parameter in the query that references a
control on the form from whence I am attempting to run the code above. The
parameter looks like this, and works fine when the query is run alone:

[Forms]![myForm]![controlName]

When I hard-code the data contained in the control controlName into the
query, I can open the recordset with no problem. Any ideas on a better method
of passing the parameter to the query that will work in the recordset?
 
Hi, Brian.
I get a "Too few parameters. Expected: 1" on the 2nd line below:
Click to expand...

First, ensure that the query name and field names in the query are all
spelled correctly. (Copy/paste the names if you need to.)

Next, ensure that all the field names listed in the query exist in all
tables used in the query.

Also ensure that this isn't a crosstab query using any parameters derived
from an open form as criteria in the WHERE clause.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Brian said:
I get a "Too few parameters. Expected: 1" on the 2nd line below:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyQueryName")

I have isolated the problem to a a parameter in the query that references a
control on the form from whence I am attempting to run the code above. The
parameter looks like this, and works fine when the query is run alone:

[Forms]![myForm]![controlName]

When I hard-code the data contained in the control controlName into the
query, I can open the recordset with no problem. Any ideas on a better method
of passing the parameter to the query that will work in the recordset?
Click to expand...
 
Access.Application.[Forms] references only work in
queries run by Access, which translates the Application
references into values before passing the query to
DAO/Jet.

So you can use a query like that attached to a form or
listbox, or with Access.Application.Docmd.RunSQL
or .OpenQuery, but not with DAO/Jet methods like
db.Execute or db.OpenRecordset

Using DAO objects, you have to evaluate the Application
references first, for example like this:

dim qdf as dao.querydef
dim db as dao.database

set db = currentdb
set qdf = db.querydefs("MyQueryName")
qdf.parameters("[Forms]![myForm]![controlName]") = me.controlName

set rs = qdf.OpenRecordset

Because Access sometimes has problems with untyped parameters,
you may wish to formally declare a parameter in that query. If you
are only using DAO to open the query, you may wish to change the
parameter name to something shorter.

(david)
 
Thank you, David. I knew it had something to do with the context in which the
Forms collection could be referenced.

david@epsomdotcomdotau said:
Access.Application.[Forms] references only work in
queries run by Access, which translates the Application
references into values before passing the query to
DAO/Jet.

So you can use a query like that attached to a form or
listbox, or with Access.Application.Docmd.RunSQL
or .OpenQuery, but not with DAO/Jet methods like
db.Execute or db.OpenRecordset

Using DAO objects, you have to evaluate the Application
references first, for example like this:

dim qdf as dao.querydef
dim db as dao.database

set db = currentdb
set qdf = db.querydefs("MyQueryName")
qdf.parameters("[Forms]![myForm]![controlName]") = me.controlName

set rs = qdf.OpenRecordset

Because Access sometimes has problems with untyped parameters,
you may wish to formally declare a parameter in that query. If you
are only using DAO to open the query, you may wish to change the
parameter name to something shorter.

(david)


Brian said:
I get a "Too few parameters. Expected: 1" on the 2nd line below:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyQueryName")

I have isolated the problem to a a parameter in the query that references a
control on the form from whence I am attempting to run the code above. The
parameter looks like this, and works fine when the query is run alone:

[Forms]![myForm]![controlName]

When I hard-code the data contained in the control controlName into the
query, I can open the recordset with no problem. Any ideas on a better method
of passing the parameter to the query that will work in the recordset?
Click to expand...
Click to expand...
 
Hi All,

I think i have problems passing a parameter from a form control to the
criteria for the table query? After trying the below, i still find
difficulty, especially in the WHERE statement and also the form control
reference(strCrit)...can someone advise me how to solve the compile error?

Option Compare Database

Public Function 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 DAO.Database
Set MyDB = CurrentDb
Dim RecordMRP As DAO.Recordset

Dim strCrit As String
Dim strSQL As String

strWorkBook = "\\txfil001\MKoh$\My WorkStation\MRPbyPN.xls"
strCrit = [Forms]![QuerybyYear]![CustPN]

strSQL = "SELECT [2007 Full].Account,[2007 Full].[Customer PN], [2007
Full].[Mfg PN], "
strSQL = strSQL + "[2007 Full].[FC Load Date],[2007 Full].[LT Qty],
[2007 Full].[Cust OH],"
strSQL = strSQL + "[2007 Full].[Req Resv], [2007 Full].[MRP Resv], [2007
Full].[MRP BO], "
strSQL = strSQL + "[2007 Full].ATS, [2007 Full].[YTD Sales], [2007
Full].[Whse ATS], [2007 Full].[Avg Cost]"
strSQL = strSQL + "FROM [2007 Full] "
strSQL = strSQL + "WHERE ((([2007 Full].[Customer PN]=))" & strCrit


Set RecordMRP = MyDB.OpenRecordset(strSQL)

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)
objXLWs.Range("A2").CopyFromRecordset RecordMRP 'I want to copy start from
Header!
objXLWs.Columns.AutoFit

objXLWb.Save
objXLWb.Close

Set objXLWs = Nothing
Set objXLApp = Nothing

RecordMRP.Close
Set RecordMRP = Nothing
Set MyDB = Nothing

End Function



Brian said:
Thank you, David. I knew it had something to do with the context in which the
Forms collection could be referenced.

david@epsomdotcomdotau said:
Access.Application.[Forms] references only work in
queries run by Access, which translates the Application
references into values before passing the query to
DAO/Jet.

So you can use a query like that attached to a form or
listbox, or with Access.Application.Docmd.RunSQL
or .OpenQuery, but not with DAO/Jet methods like
db.Execute or db.OpenRecordset

Using DAO objects, you have to evaluate the Application
references first, for example like this:

dim qdf as dao.querydef
dim db as dao.database

set db = currentdb
set qdf = db.querydefs("MyQueryName")
qdf.parameters("[Forms]![myForm]![controlName]") = me.controlName

set rs = qdf.OpenRecordset

Because Access sometimes has problems with untyped parameters,
you may wish to formally declare a parameter in that query. If you
are only using DAO to open the query, you may wish to change the
parameter name to something shorter.

(david)


Brian said:
I get a "Too few parameters. Expected: 1" on the 2nd line below:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyQueryName")

I have isolated the problem to a a parameter in the query that references a
control on the form from whence I am attempting to run the code above. The
parameter looks like this, and works fine when the query is run alone:

[Forms]![myForm]![controlName]

When I hard-code the data contained in the control controlName into the
query, I can open the recordset with no problem. Any ideas on a better method
of passing the parameter to the query that will work in the recordset?
Click to expand...
Click to expand...
Click to expand...
 
Hi,
try to replace this line:
strSQL = strSQL + "WHERE ((([2007 Full].[Customer PN]=))" & strCrit
with:
strSQL = strSQL + "WHERE [2007 Full].[Customer PN]='" & strCrit & "'"

you have extra left bracket plus string parameter should be in quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Junior728 said:
Hi All,

I think i have problems passing a parameter from a form control to the
criteria for the table query? After trying the below, i still find
difficulty, especially in the WHERE statement and also the form control
reference(strCrit)...can someone advise me how to solve the compile error?

Option Compare Database

Public Function 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 DAO.Database
Set MyDB = CurrentDb
Dim RecordMRP As DAO.Recordset

Dim strCrit As String
Dim strSQL As String

strWorkBook = "\\txfil001\MKoh$\My WorkStation\MRPbyPN.xls"
strCrit = [Forms]![QuerybyYear]![CustPN]

strSQL = "SELECT [2007 Full].Account,[2007 Full].[Customer PN], [2007
Full].[Mfg PN], "
strSQL = strSQL + "[2007 Full].[FC Load Date],[2007 Full].[LT Qty],
[2007 Full].[Cust OH],"
strSQL = strSQL + "[2007 Full].[Req Resv], [2007 Full].[MRP Resv],
[2007
Full].[MRP BO], "
strSQL = strSQL + "[2007 Full].ATS, [2007 Full].[YTD Sales], [2007
Full].[Whse ATS], [2007 Full].[Avg Cost]"
strSQL = strSQL + "FROM [2007 Full] "
strSQL = strSQL + "WHERE ((([2007 Full].[Customer PN]=))" & strCrit


Set RecordMRP = MyDB.OpenRecordset(strSQL)

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)
objXLWs.Range("A2").CopyFromRecordset RecordMRP 'I want to copy start from
Header!
objXLWs.Columns.AutoFit

objXLWb.Save
objXLWb.Close

Set objXLWs = Nothing
Set objXLApp = Nothing

RecordMRP.Close
Set RecordMRP = Nothing
Set MyDB = Nothing

End Function



Brian said:
Thank you, David. I knew it had something to do with the context in which
the
Forms collection could be referenced.

david@epsomdotcomdotau said:
Access.Application.[Forms] references only work in
queries run by Access, which translates the Application
references into values before passing the query to
DAO/Jet.

So you can use a query like that attached to a form or
listbox, or with Access.Application.Docmd.RunSQL
or .OpenQuery, but not with DAO/Jet methods like
db.Execute or db.OpenRecordset

Using DAO objects, you have to evaluate the Application
references first, for example like this:

dim qdf as dao.querydef
dim db as dao.database

set db = currentdb
set qdf = db.querydefs("MyQueryName")
qdf.parameters("[Forms]![myForm]![controlName]") = me.controlName

set rs = qdf.OpenRecordset

Because Access sometimes has problems with untyped parameters,
you may wish to formally declare a parameter in that query. If you
are only using DAO to open the query, you may wish to change the
parameter name to something shorter.

(david)


I get a "Too few parameters. Expected: 1" on the 2nd line below:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyQueryName")

I have isolated the problem to a a parameter in the query that
references
a
control on the form from whence I am attempting to run the code
above. The
parameter looks like this, and works fine when the query is run
alone:

[Forms]![myForm]![controlName]

When I hard-code the data contained in the control controlName into
the
query, I can open the recordset with no problem. Any ideas on a
better
method
of passing the parameter to the query that will work in the
recordset?
Click to expand...
Click to expand...
Click to expand...
 
Hi Alex,

Thanks! it works!=)

Alex Dybenko said:
Hi,
try to replace this line:
strSQL = strSQL + "WHERE ((([2007 Full].[Customer PN]=))" & strCrit
with:
strSQL = strSQL + "WHERE [2007 Full].[Customer PN]='" & strCrit & "'"

you have extra left bracket plus string parameter should be in quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Junior728 said:
Hi All,

I think i have problems passing a parameter from a form control to the
criteria for the table query? After trying the below, i still find
difficulty, especially in the WHERE statement and also the form control
reference(strCrit)...can someone advise me how to solve the compile error?

Option Compare Database

Public Function 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 DAO.Database
Set MyDB = CurrentDb
Dim RecordMRP As DAO.Recordset

Dim strCrit As String
Dim strSQL As String

strWorkBook = "\\txfil001\MKoh$\My WorkStation\MRPbyPN.xls"
strCrit = [Forms]![QuerybyYear]![CustPN]

strSQL = "SELECT [2007 Full].Account,[2007 Full].[Customer PN], [2007
Full].[Mfg PN], "
strSQL = strSQL + "[2007 Full].[FC Load Date],[2007 Full].[LT Qty],
[2007 Full].[Cust OH],"
strSQL = strSQL + "[2007 Full].[Req Resv], [2007 Full].[MRP Resv],
[2007
Full].[MRP BO], "
strSQL = strSQL + "[2007 Full].ATS, [2007 Full].[YTD Sales], [2007
Full].[Whse ATS], [2007 Full].[Avg Cost]"
strSQL = strSQL + "FROM [2007 Full] "
strSQL = strSQL + "WHERE ((([2007 Full].[Customer PN]=))" & strCrit


Set RecordMRP = MyDB.OpenRecordset(strSQL)

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)
objXLWs.Range("A2").CopyFromRecordset RecordMRP 'I want to copy start from
Header!
objXLWs.Columns.AutoFit

objXLWb.Save
objXLWb.Close

Set objXLWs = Nothing
Set objXLApp = Nothing

RecordMRP.Close
Set RecordMRP = Nothing
Set MyDB = Nothing

End Function



Brian said:
Thank you, David. I knew it had something to do with the context in which
the
Forms collection could be referenced.

:

Access.Application.[Forms] references only work in
queries run by Access, which translates the Application
references into values before passing the query to
DAO/Jet.

So you can use a query like that attached to a form or
listbox, or with Access.Application.Docmd.RunSQL
or .OpenQuery, but not with DAO/Jet methods like
db.Execute or db.OpenRecordset

Using DAO objects, you have to evaluate the Application
references first, for example like this:

dim qdf as dao.querydef
dim db as dao.database

set db = currentdb
set qdf = db.querydefs("MyQueryName")
qdf.parameters("[Forms]![myForm]![controlName]") = me.controlName

set rs = qdf.OpenRecordset

Because Access sometimes has problems with untyped parameters,
you may wish to formally declare a parameter in that query. If you
are only using DAO to open the query, you may wish to change the
parameter name to something shorter.

(david)


I get a "Too few parameters. Expected: 1" on the 2nd line below:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MyQueryName")

I have isolated the problem to a a parameter in the query that
references
a
control on the form from whence I am attempting to run the code
above. The
parameter looks like this, and works fine when the query is run
alone:

[Forms]![myForm]![controlName]

When I hard-code the data contained in the control controlName into
the
query, I can open the recordset with no problem. Any ideas on a
better
method
of passing the parameter to the query that will work in the
recordset?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top