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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)


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
Office Version
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.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...