Is it true that you can’t open a recordset if the query is based on a control on an open form?


Board Regular
Oct 31, 2016
For example, I’m trying to write:

Dim rec1 As Recordset
Set rec1 = CurrentDb.OpenRecordset("MySchedulewXdept1") ‘This query (MySchedulewXdept1) is filtered based on the date that I have on another open form.

I get a Run-time error '3061': Too few parameters. Expected 1.

My main objective is to create a chart that I can email to users with their schedule. Since they can make changes to their schedule anytime from now until 3 weeks from now, I need to know the date that they've selected from the form to know which schedule to create and send.

Here's the rest of my code if that helps. I'm using late binding because I was having issues with users with 2007 and 2010 runtime versions.

Public Sub SendMailSSSch()

Dim olapp As Object
Dim olmail As Object
Const olmailitem As Long = 0

Dim rec1 As Recordset
Dim aHead(1 To 3) As String
Dim aRow(1 To 3) As String
Dim aBody() As String
Dim lCnt As Long

'Create the header row - Schedule
aHead(1) = "Home?"
aHead(2) = "Interval Start"
aHead(3) = "My Schedule"

lCnt = 1
ReDim aBody(1 To lCnt)
aBody(lCnt) = "<HTML>******><table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"

'Create each body row - Schedule
Set rec1 = CurrentDb.OpenRecordset("MySchedulewXdept1")

If Not (rec1.BOF And rec1.EOF) Then
Do While Not rec1.EOF
lCnt = lCnt + 1
ReDim Preserve aBody(1 To lCnt)
aRow(1) = rec1("FHome")
aRow(2) = rec1("IntervalStart")
aRow(3) = rec1("FinWhere")
aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
End If

aBody(lCnt) = aBody(lCnt) & "</table></body></html>"

Dim rec2 As Recordset
Dim bHead(1 To 2) As String
Dim bRow(1 To 2) As String
Dim bBody() As String
Dim lCnts As Long

'Create the header row - Fixed
bHead(1) = "Time"
bHead(2) = "Department"

lCnts = 1
ReDim bBody(1 To lCnts)
bBody(lCnts) = "<HTML>******><table border='2'><tr><th>" & Join(bHead, "</th><th>") & "</th></tr>"

'Create each body row - Fixed
Set rec2 = CurrentDb.OpenRecordset("MyDailyRestrict")

If Not (rec2.BOF And rec2.EOF) Then
Do While Not rec2.EOF
lCnts = lCnts + 1
ReDim Preserve bBody(1 To lCnts)
bRow(1) = rec2("IntervalStart")
bRow(2) = rec2("FinWhere")
bBody(lCnts) = "<tr><td>" & Join(bRow, "</td><td>") & "</td></tr>"
End If

bBody(lCnts) = bBody(lCnts) & "</table></body></html>"

Set olapp = CreateObject("outlook.application")
If olapp Is Nothing Then
MsgBox "Outlook is not open. Please open Outlook and try again."
Set olmail = olapp.CreateItem(olmailitem)
With olmail
.To = "" 'Forms!ssvacdayfrm!txtEmail
.Subject = "My Schedule for " & Forms![Self-Service]!TxtTab
.htmlBody = "Fixed Intervals:" & "<br>" & "Periods when you aren't able to change your schedule." & "<br>" & Join(bBody, vbNewLine) & "<br>" & "<br>" & Join(aBody, vbNewLine)
End With
End If

Set olapp = Nothing
Set olmail = Nothing
End Sub

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.


Board Regular
Oct 31, 2016
I might be getting closer - I need to define parameters in my query? The ones I want to use to create the chart and/or the one that the filtering is based on? I think this is a step in the right direction, but I'm not all the way to the solution...

Any additional insight is appreciated!


MrExcel MVP, Moderator
Mar 2, 2007
Office Version
You can access the parameters collection to add parameters to a query using DAO.
Sub foo()

Dim qdf As QueryDef
Dim rs As DAO.Recordset

    Set qdf = CurrentDb.QueryDefs("Query6")
    qdf.Parameters("[Forms]![Form1]![Text1]") = Forms!Form1!Text1
    Set rs = qdf.OpenRecordset()
    Debug.Print rs.Fields(1).Value
    Set rs = Nothing
    Set qdf = Nothing

End Sub


Board Regular
Oct 31, 2016
Xenou, thanks for the reply, but I'm not sure I'm understanding.

I need to define the parameter that the filtering is based on, right?

I went into my 4 subqueries and defined the parameter used to filter by date. The subqueries look at the date on my schedule form to pull that day’s schedule: Forms!Self-Service!txtTab is the date on the schedule form used in the subqueries “MyScheduleTyp&Holiday1”, “MyScheduleAttEvents2”,”MyScheduleExceptions3”, and “MyScheduleSubExceptions3”.

Am I understanding that I need to incorporate something like this for each subquery to make this work?:
dim rst_1 as recordset
dim qdf_1 as querydef
set qdf_1 = currentdb.querydefs("MyScheduleTyp&Holiday1")
qdf_1.parameters(“Forecast Date”)= Forms!Self-Service!txtTab
set rst_1=qdf_1.openrecordset


MrExcel MVP, Moderator
Mar 2, 2007
Office Version
I think you have it right.

Personally I would bind the date to a database table field, and join the queries on that table.

Create a table XYZ
Create a field called ID, make it a long data type, make it a primary key, and in the validation property enter the value 1, which means the only valid entry is the number 1
Create other fields you want in it (such as "MyDate")

Open the table, add a new record, put 1 in the ID field (so you have the one and only possible record). That's the point of the validation - to prevent a horrific error of multiple rows in this table by mistake, which would wreak havoc on the reports with duplicate rows becoming possible where they aren't expected.

Now you can bind the MyDate field to your form, or if you prefer use the afterupdate event on the form control to update the record.

and now your queries can use that field:
select * from SomeTable inner join XYZ on SomeTable.Field = XYZ.MyDate

That's one solution.

The other might be to rewrite you query so you don't have to use the same date parameter four times.
Last edited:


Well-known Member
Jun 3, 2015
I'm wondering if one function could accept the parameter and the query name as inputs and either build and process the recordset, or create and return the recordset to the calling procedure. I'm fairly sure either is possible, and it would be slightly simpler if the parameter was defined in the query. It would also help if each parameter was the same data type.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...