Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    Unanswered: Access: Set parameter of existing query object in vba and display results in subform

    Hi all,
    I have an existing query object and I'd like to do the following with it in vba:
    Get its date start and date end parameters from respective textboxes in the main form, then run it through button click and display the results in the subform of the mainform. The expected result of the query are those records that fall within the range of date start and date end parameters.

    The subform is bound to the said query with the name qrySearchBills. The textboxes of which date parameters shall be taken from are txtStartDate and txtEndDate respectively.

    Can you guys shade some light on me? This is my first post in this forum and I'd really appreciate your help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. The query must be built with the syntax:
    Code:
    PARAMETERS [StartDate] DateTime,
    [EndDate] DateTime;
    SELECT ...
    FROM ...
    WHERE [Start Date] = [StartDate] ...
    2. To pass the parameters values in VBA, use:
    Code:
    Private Sub Command_LoadData_Click()
    
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        
        Set qdf = CurrentDb.QueryDefs("qry_QueryName")
        With qdf
            .Parameters("StartDate").Value = Format(Me.Text_StartDate.Value, "yyyy-mm-dd")
            .Parameters("EndDate").Value = Format(Me.Text_EndDate.Value, "yyyy-mm-dd")
        End With
        Set rst = qdf.OpenRecordset
        Set Me.SubFormControlName.Form.Recordset = rst
        Set qdf = Nothing
        
    End Sub
    Note: The subform must be bound, otherwise you cannot assign a value to its Recordset object.
    Have a nice day!

  3. #3
    Join Date
    Jun 2014
    Posts
    3
    Thank you for responding Sinndho . Your code has gotten me real close to what I want to achieve. There's a little issue however that I want to address.

    When the main form named frmSearchBill loads, it prompts me to enter the value of [StartDate] and [EndDate] parameters. What I'm trying to achieve is that when the main form loads, all the unfiltered records are initially displayed in the subform without the parameter prompts. When the user wants to filter the records based on date range, He must enter the [StartDate] value in txtStartDate and [EndDate] value in txtEndDate of the main form, then click the search button to execute the query.

    The vba code works perfectly fine, so I'm looking right now at the sql statement in the saved query object. Here's how I used you code:

    SQL in the query object:
    Code:
    PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
    SELECT tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date, Sum(tblInvoice.[TotalPrice]) AS Amount
    FROM tblCrdCustomer INNER JOIN tblInvoice ON tblCrdCustomer.IDNo = tblInvoice.NameID
    WHERE tblInvoice.Date Between [StartDate] And [EndDate]
    GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date;
    VBA Code: (Works fine, However when the parameter prompts are ignored or canceled, it generates Run-time error saying "expression you entered refers to an object that is closed or doesn't exist". But we don't want the prompts in the first place.)
    Code:
    Private Sub btnSearchBill_Click()
        Dim qdf As DAO.QueryDef
        Dim rst As DAO.Recordset
        
        Set qdf = CurrentDb.QueryDefs("qrySearchBills")
        With qdf
            .Parameters("StartDate").Value = Format(Me.txtStartDate.Value, "yyyy-mm-dd")
            .Parameters("EndDate").Value = Format(Me.txtEndDate.Value, "yyyy-mm-dd")
        End With
        Set rst = qdf.OpenRecordset
        Set Me.subQrySearchBills.Form.Recordset = rst
        Set qdf = Nothing
    End Sub
    Thanks again for paying attention to this.
    Last edited by madujr; 06-25-14 at 23:46.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The trick consists in using a query without the parameters as the original RecordSource of the subform, possibly with an "impossible" WHERE condition if you want that the subforms appears empty before assigning the parameters to the actual query.

    e.g. (from your example), in design view, assign this to the RecordSource property of the subform:
    Code:
    SELECT tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date, Sum(tblInvoice.[TotalPrice]) AS Amount
    FROM tblCrdCustomer INNER JOIN tblInvoice ON tblCrdCustomer.IDNo = tblInvoice.NameID
    WHERE 0 = 1
    GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.Date;
    Have a nice day!

  5. #5
    Join Date
    Jun 2014
    Posts
    3
    Your trick works Sinndho! Thanks a lot.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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