Run saved query object in access through vba and display result in subform (1 Viewer)

[madujr]

Hi all,
This is my first post. I'd appreciate all the help I can get.

I have saved query object named qrySearchBill. I wan to call this query through vba and display the result in a subform named subQrySearchBills in datasheet view. Here's how I want it to work:

When the main form loads, I want all unfiltered records to be displayed in the subform initially. The user may then decide to filter based on date range, so he enters startdate and enddate parameter values in their respective textboxes in the main form. Then click search button to run the saved query based on the date range parameter taken from the textboxes.

I have this code so far:
SQL of the saved query object:

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 to call the query and its parameter:

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

This code works fine except that when the main form loads, a prompt window appears to ask for the value of dateStart and dateEnd. I don't want it to prompt because it's suppose to get these values from the main form's textboxes (txtStartDate and txtEndDate respectively), plus it should initially display all the unfiltered records.

Let me hear from you guys. Thanks

 

[pr2-eugin]

Change your Query as,

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
GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.[Date];

Then your button code would be.

Private Sub btnSearchBill_Click()
    Dim strSQL As String
    
    strSQL = "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 " & Format(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & " And " & Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#") & _
             "GROUP BY tblInvoice.BillNo, tblCrdCustomer.CstName, tblCrdCustomer.CstAddress, tblCrdCustomer.Island, tblInvoice.[Date];"
             
    Me.subQrySearchBills.Form.Recordsource = strSQL
    Me.subQrySearchBills.Form.Requery
End Sub

 

[madujr]

You are awesome pr2-eugin! Your code works! Thank you so much!

 

[pr2-eugin]

You're welcome !