run time error 3061 too few parameters expected 2 (1 Viewer)

archy321

Registered User.
Local time
Yesterday, 16:20
Joined
Sep 10, 2018
Messages
11
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


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

Code:
Set rst = dbs.OpenRecordset("DateRangeTEST", dbOpenDynaset, dbReadOnly)
Any help would be greatly appreciated!

V/R
Archy
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:20
Joined
Aug 30, 2003
Messages
36,124
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

Registered User.
Local time
Yesterday, 16:20
Joined
Sep 10, 2018
Messages
11
Would I put the eval function in the query criteria or directly into the code somewhere?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:20
Joined
Aug 30, 2003
Messages
36,124
In the query criteria. It would look exactly like above, but with your form and control names.
 

archy321

Registered User.
Local time
Yesterday, 16:20
Joined
Sep 10, 2018
Messages
11
Would what I put in criteria look something like this?

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

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

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 16:20
Joined
Aug 30, 2003
Messages
36,124
Yes, did you try it? You've got the same control there, I'd expect a end date.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:20
Joined
Oct 29, 2018
Messages
21,453
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

Registered User.
Local time
Yesterday, 16:20
Joined
Sep 10, 2018
Messages
11
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

Wino Moderator
Staff member
Local time
Yesterday, 16:20
Joined
Aug 30, 2003
Messages
36,124
Happy to help!

dbGuy, link doesn't work for me either.
 

Users who are viewing this thread

Top Bottom