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?
