Runtime error 3061 too few parameters

Question

I'm exporting data from a joined table in MS Access to an Excel spreadsheet. When I use a SQL query (without criteria), it works fine but when I try to put a criteria in the code i get the run time error message. The following is the SQL query code:

SELECT CompsT.CompsID, CompsT.InspectionID, CompsT.Component, CompsT.[Location Grouping], CompsT.[Component Description], CompsT.[Make/Model/Type], CompsT.COD, CompsT.[Dimension Type], CompsT.Length, CompsT.LUnit, CompsT.Wide, CompsT.WUnit, CompsT.High, CompsT.HUnit, CompsT.Depth, CompsT.DUnit, CompsT.[Quantity Type], CompsT.[Quantity Unit], CompsT.Quantity, CompsT.[Work Complete], InspectionsT.[Damage#]

FROM InspectionsT INNER JOIN CompsT ON InspectionsT.[InspectionID] = CompsT.[InspectionID]

WHERE (((InspectionsT.[Damage#])=[Enter a Damage #]));

I think the problem stems from the dialogue requesting the user to input a Damage #. I need to create either a message box with the Damage # input field or perhaps a separate form that passes the post query data to the VBA code below. I'm not having much success with that so any help would be appreciated.

The following is my VBA code:

Private Sub Command0_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim SourceFile, DestinationFile
    
    Set db = CurrentDb
    SourceFile = "C:\SITemplates\BridgeTemplate.xlsx" & _
    DestinationFile = "C:\SITemplates\Export\BridgeTemplate.xlsx"
    FileCopy "C:\SITemplates\BridgeTemplate.xlsx", "C:\SITemplates\Export\BridgeTemplate.xlsx"
    
    sSQL = "CompsTExportQ"
    
    'Troubleshooting
    'Debug.Print ssql 'Copy/Paste and Test in QBE
    
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    
    Call ExportRecordset2XLS(rs, "C:\SITemplates\Export\BridgeTemplate.xlsx", "Input", 1, 6, False, False, False)
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Answer 1

As I said earlier you will need to evaluate the querydef object's Parameters collection before establishing the recordset.  The relevant lines, in addition to the necessary object variable declarations, in the example I posted are:

    Set dbs = CurrentDb
    
    Set qdf = dbs.QueryDefs(strQuery)
    
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
    
    Set rst = qdf.OpenRecordset

Each parameter obviously must be exposed to the code, so cannot be a simple prompt like [Enter damage number:].  It should be a reference to a control in an open form, and of syntax such as Forms!NameOfFormGoesHere!NameOfControlGoesHere.

Answer 2

Ken,

I used your prm code changes which got me further down the road. But I still could not select from a field (InspectionsID) to call specific records for the export to excel.

I came up with a solution, which won't really help anyone else in this forum and is not ideal. It will require me to create multiple subforms (13) which I am willing to do, but wish there was another way. I changed my SQL query to the following:

SELECT CompsT.Component, CompsT.[Location Grouping], CompsT.[Component Description], CompsT.[Make/Model/Type], CompsT.COD, CompsT.[Dimension Type], CompsT.Length, CompsT.LUnit, CompsT.Wide, CompsT.WUnit, CompsT.High, CompsT.HUnit, CompsT.Depth, CompsT.DUnit, CompsT.[Quantity Type], CompsT.[Quantity Unit], CompsT.Quantity, CompsT.[Work Complete]

FROM CompsT

WHERE (((CompsT.InspectionID)=[Forms]![BridgesF]![CompsTsubform]![InspectionID]));

CompsTsubform (was) a shared form among 13 different types of inspections including BridgesF. Now I have to create 12 other subforms like "roads", "buildings", "bridges", etc. UNLESS there is some way to replace BridgesF with something more universal and I know of nothing that will work. [Forms]![BridgesF]![CompsTsubform]![InspectionID]

I'm willing to create said number of forms however since this method doesn't require user input but operates off the current InspectionID in the open form.

Sorry for the long reply, but at least I have a solution to what I wanted to accomplish.

Thanks again for your help!

Answer 3

To establish a recordset from a query which takes one or more parameters, you firstly need to evaluate the members of the querydef object's Parameters collection, and then call the OpenRecordset method of the querydef object:

The following is an example which does this when creating a text file for use as the data source for a Word mail merge.  The parameter will need to be a reference to a control in a currently open dialogue form:

Public Function WordMerge(strQuery As String, _
                        strDataDoc As String, _
                        strMergeFile As String, _
                        Optional blnSuppressBlankLines As Boolean = True)
                        
    ' Merges data from query into Word document
    ' Accepts:  Name of Access query providing data for merge - String.
    '           Path to Word data file created from query - String.
    '           Path to Word document to be used for merge - String.
    '           Optional setting to suppress or show blank lines
    '           if data missing ( Default = True ) - Boolean
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim wApp As Object
    Dim wDoc As Object
    Dim wActiveDoc As Word.Document
    
    Set dbs = CurrentDb
    
    Set qdf = dbs.QueryDefs(strQuery)
    
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
    
    Set rst = qdf.OpenRecordset
    
    ' exit function if recordset is empty
    If rst.EOF Then
        MsgBox "No data to merge.", vbInformation, "Mail Merge"
        GoTo Exit_Here
    End If
    
    Set wApp = GetWordApp()
    
    ' close datasource document if open in Word
    For Each wDoc In wApp.Documents
        If wDoc.Path & "\" & wDoc.Name = strDataDoc Then
            wDoc.Close wdDoNotSaveChanges
        End If
    Next wDoc
    
    ' delete current Word data file.
    ' ignore error if file doesn't exist
    On Error Resume Next
    Kill strDataDoc
    On Error GoTo 0
    
    ' create new Word data file
    ExportToText strQuery, strDataDoc, ",", True
    
    'open word merge document
    Set wDoc = wApp.Documents.Open(strMergeFile)
        
    ' execute merge
    With wDoc.MailMerge
        .OpenDataSource strDataDoc
        .SuppressBlankLines = blnSuppressBlankLines
        .Destination = wdSendToNewDocument
        .Execute
    End With
    Set wActiveDoc = wApp.ActiveDocument
    
    ' show document in maximized window
    ShowWord wApp, wDoc, wActiveDoc
    
Exit_Here:
    Set rst = Nothing
    Set dbs = Nothing
    
End Function

Answer 4

Ken, thanks for your reply. It's taken some time for me to analyze your code since I am not well versed in VBA. My code was adapted from Dan Pineault's "export to excel" code for what I am trying to accomplish. I have managed to get it to work so that it exports the recordset from the SQL query but I want to add user input for a specific damage number that selects only the records with that number (instead of all records). It is a union query. When I put a criteria that asks the user for a specific damage number, I get an error message "Too few parameters. Expected <number>. (Error 3061)". When I run the query by itself, it pulls up the correct records but when I call it with a command button through the code. That is the error message I get.