Open Recordset with Parameter

  • Thread starter Thread starter Steve D
  • Start date Start date
S

Steve D

I am trying to open a recordset but I am running into an error because the
query pulls a criteria from a form. The form is open when the code runs but I
still get the error "Missing Parameter" The code is attached below. I am
looking for assistance on passing the parameter to the recordset. Any help is
appreciated

Private Sub Command14_Click()
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlWsht As Excel.Worksheet
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSheetName As String
strQueryName = "qryAssociates_Paid_Chargeback_Amt"
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)
strSheetName = Left(strQueryName, 31)
strSheetName = Trim(strSheetName)
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWbk = xlApp.Workbooks.Add


Set xlWsht = xlWbk.Sheets(1)
With xlWsht
.Cells.CopyFromRecordset objRST
.Name = strSheetName
End With


Set objRST = Nothing
Set xlWsht = Nothing
Set xlApp = Nothing
Set xlWbk = Nothing
End Sub
 
Here is sample code for evaulating parameters for stored query and then
opening that query:

Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("NameOfQueryWithParameter(s)")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
' rst object contains the query's records after applying the parameters
' code goes here to use the records' data from rst object
'
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing
 
Does your SQL for qryAssociates_Paid_Chargeback_Amt contain a Parameters
clause? If not, you should add one. If you don't specifically declare the
parameters being used, as a particular data type, Access may not be
interpreting those values correctly.

Although Ken's method works, you should not have to do that if your
parameters are actually declared in the query.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top