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

A

ARW17

Board Regular
Joined
Oct 31, 2016
Messages
109
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

'Schedule
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>"
rec1.MoveNext
Loop
End If


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

'Fixed
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>"
rec2.MoveNext
Loop
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."
Else
Set olmail = olapp.CreateItem(olmailitem)
With olmail
.To = "Amanda.Falvo@mcmaster.com" '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)
.Send
End With
End If


Set olapp = Nothing
Set olmail = Nothing
End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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!
 
Upvote 0
You can access the parameters collection to add parameters to a query using DAO.
Example:
Code: 
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
    
    rs.Close
    Set rs = Nothing
    Set qdf = Nothing

End Sub
 
Upvote 0
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
 
Upvote 0
I think you have it right.

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

Basically:
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:
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top