run time error 3061 too few parameters expected 2

[archy321]

Hello,

I am trying to run a query based on a start/end date on my form and have the results get pasted onto an excel template. I've done this before but without the start/end date on it, but rather a set "last month" or "current month" criteria. I THINK I have the Start/end date down as it works, but now that I want to paste the results into excel I run into a run time error 3061 too few parameters expected 2 and am absolutely stumped at this point!

This is my code

Option Compare Database
Option Explicit
 
Private Sub CustomReport_Click()
    
'Opens Date Range Query
DoCmd.OpenQuery "DateRangeTEST"
    
    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Dim MaxFields As Integer
    Dim MthQuery As String
    Set dbs = CurrentDb()
    
   
    blnEXCEL = False
    blnHeaderRow = False
    

    ' Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set xlx = CreateObject("Excel.Application")
        blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0
    xlx.Visible = True
    Set xlw = xlx.Workbooks.Open("C:\Desktop\Templates\Metrics.xlsx")
    Set xls = xlw.Worksheets("TEMPLATE")
        Set xlc = xls.Range("A41")
        Set rst = dbs.OpenRecordset("DateRangeTEST", dbOpenDynaset, dbReadOnly)
        If rst.EOF = False And rst.BOF = False Then
        rst.MoveLast
        rst.MoveFirst
        If blnHeaderRow = True Then
            For lngColumn = 0 To rst.Fields.Count - 1
                xlc.Offset(0, lngColumn) = rst.Fields(lngColumn).Name
            Next lngColumn
            Set xlc = xlc.Offset(1, 0)
        End If
        ' write data to worksheet
        MaxFields = rst.Fields.Count - 1
        Do While rst.EOF = False
            For lngColumn = 0 To MaxFields
                xlc.Offset(0, lngColumn) = rst.Fields(lngColumn)
            Next lngColumn
            rst.MoveNext
            Set xlc = xlc.Offset(1, 0)
        Loop
        End If
        rst.Close
End Sub

This portion of code is highlighted when i'm given the error

Set rst = dbs.OpenRecordset("DateRangeTEST", dbOpenDynaset, dbReadOnly)

Any help would be greatly appreciated!

V/R
Archy

 

[pbaldy]

OpenRecordset can't resolve form references in the query. The simplest solution is to wrap each form reference in the Eval() function:

Eval('Forms!FormName.TextboxName')

 

[archy321]

Would I put the eval function in the query criteria or directly into the code somewhere?

 

[pbaldy]

In the query criteria. It would look exactly like above, but with your form and control names.

 

[archy321]

Would what I put in criteria look something like this?

Between eval('Forms!Pagehome.Startdate') and eval('Forms!pagehome.Startdate')

This would all go under the field I have for the date entry?

 

[pbaldy]

Yes, did you try it? You've got the same control there, I'd expect a end date.

 

[theDBguy]

Hi,

Pardon me for jumping in but I just wanted to offer this other approach .

PS. The website is currently down for maintenance, but the link should work eventually.

 

[archy321]

Yes, did you try it? You've got the same control there, I'd expect a end date.

OMG that worked perfectly! Thank you so much!

@theDBguy

Your link doesnt work. I would really like to learn more though!

V/R
Archy

 

[pbaldy]

Happy to help!

dbGuy, link doesn't work for me either.