Change code to search subform recordsource query instead of main query?

[Garindan]

Hi all, I have a form/subform with search. I would like to ask if anybody could help me change the code to search the record source query for the subform instead of the 'main' query, because I have grouped on fields in the record source query to alter the results.....

The subform is based on qselProductSalesCalculator:-

qselProductSalesCalculator
Salesperson
Product
ProductWeight
Quantity
DeliveryDate
DeliveryTime

In the query the results display without any grouping and there are 14703 records.

In the subform record source I have changed the query to group by saleperson and product, and sum on quantity. I only want to show each product once per salesperson with a total quantity. I have unticked show 'DeliveryDate' and 'DeliveryTime' but I would like to be able to search by them.

Here's the search code I have at the moment, but it brings back results from qselProductSalesCalculator so its not giving me the results I want.

I've been stuck on this for ages and would be really really thankful for any help!!

Option Compare Database
Option Explicit

Private Sub btnClear_Click()

    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtStartDate = ""
    Me.txtEndDate = ""
    Me.cmbSalesperson = ""
    Me.cmbDeliveryTime = ""
    
    DoEvents
    Me.fsubProductSalesCalculatorDetails.Form.RecordSource = "SELECT * FROM qselProductSalesCalculator WHERE 1=0;"
    
End Sub

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.fsubProductSalesCalculatorDetails.Form.RecordSource = "SELECT * FROM qselProductSalesCalculator " & BuildFilter
    
    ' Requery the subform
    Me.fsubProductSalesCalculatorDetails.Requery
    
End Sub


Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim intIndex As Integer
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    varWhere = Null  ' Main filter
    
    ' Check for Salesperson
    If Me.cmbSalesperson > "" Then
        varWhere = varWhere & "[Salesperson] LIKE """ & Me.cmbSalesperson & "*"" AND "
    End If
    
    ' Check for Start Date
    If Me.txtStartDate > "" Then
        varWhere = varWhere & "([DeliveryDate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
    
    ' Check for End Date
    If Me.txtEndDate > "" Then
        varWhere = varWhere & "([DeliveryDate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If
    
    ' Check for Delivery Time
    If Me.cmbDeliveryTime > "" Then
        varWhere = varWhere & "[DeliveryTime] LIKE """ & Me.cmbSalesperson & "*"" AND "
    End If
    
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

The subform recordsource is like this at the mo:-

SELECT qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, Sum(qselProductSalesCalculator.Quantity) AS SumOfQuantity, qselProductSalesCalculator.ProductWeight, Sum([Quantity]*[ProductWeight]) AS QuantityxWeight
FROM qselProductSalesCalculator
GROUP BY qselProductSalesCalculator.Salesperson, qselProductSalesCalculator.Product, qselProductSalesCalculator.ProductWeight;