Results 1 to 6 of 6

Thread: Is it true that you canít open a recordset if the query is based on a control on an open form?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2016
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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) = "******>"


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


    aBody(lCnt) = aBody(lCnt) & "
    " & Join(aHead, "") & "
    " & Join(aRow, "") & "
    "

    '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) = "******>"


    '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) = ""
    rec2.MoveNext
    Loop
    End If


    bBody(lCnts) = bBody(lCnts) & "
    " & Join(bHead, "") & "
    " & Join(bRow, "") & "
    "

    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:" & "
    " & "Periods when you aren't able to change your schedule." & "
    " & Join(bBody, vbNewLine) & "
    " & "
    " & Join(aBody, vbNewLine)
    .Send
    End With
    End If


    Set olapp = Nothing
    Set olmail = Nothing
    End Sub

  2. #2
    Board Regular
    Join Date
    Oct 2016
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,471
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

    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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular
    Join Date
    Oct 2016
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,471
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

    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 by xenou; May 12th, 2017 at 11:22 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,698
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •