Changing the Datasheet recordsource of a split form

[jerepois]

I have a unbound combobox that uses a select case to change the recordsource of the form.



here is the code that is powering the AfterUpdate() event.

Private Sub Combo2_AfterUpdate()    Select Case Me.Combo2.Column(1)
        Case "Scope Category"
            Me.RecordSource = "SELECT tblScopeCategory.[ScopeCategoryName] AS NAME, Count(tblWorkPackage.[WorkPackageID]) AS TOTAL" _
            & " FROM (tblCraft INNER JOIN (((tblWorkList INNER JOIN tblInScope ON tblWorkList.InScopeID = tblInScope.InScopeID) INNER JOIN tblWorkPackage ON tblWorkList.WorkPackageID = tblWorkPackage.WorkPackageID) INNER JOIN tblPlanner ON tblWorkPackage.PlannerID = tblPlanner.PlannerID) ON tblCraft.CraftID = tblWorkList.LeadCraftID) INNER JOIN tblScopeCategory ON tblWorkList.ScopeCategoryID = tblScopeCategory.ScopeCategoryID" _
            & " WHERE (((tblWorkList.AREA) Like " * ") AND ((tblInScope.InScopeName) Like " * ") AND ((tblPlanner.PlannerName) Like " * "))" _
            & " GROUP BY tblScopeCategory.[ScopeCategoryName];"
        Case "Equipment Category"
            Me.RecordSource = "SELECT tblWorkList.[EQUIPMENT_CATEGORY] AS NAME, Count(tblWorkPackage.[WorkPackageID]) AS TOTAL" _
            & " FROM (tblCraft INNER JOIN (((tblWorkList INNER JOIN tblInScope ON tblWorkList.InScopeID = tblInScope.InScopeID) INNER JOIN tblWorkPackage ON tblWorkList.WorkPackageID = tblWorkPackage.WorkPackageID) INNER JOIN tblPlanner ON tblWorkPackage.PlannerID = tblPlanner.PlannerID) ON tblCraft.CraftID = tblWorkList.LeadCraftID) INNER JOIN tblScopeCategory ON tblWorkList.ScopeCategoryID = tblScopeCategory.ScopeCategoryID" _
            & " WHERE (((tblWorkList.AREA) Like " * ") AND ((tblInScope.InScopeName) Like " * ") AND ((tblPlanner.PlannerName) Like " * "))" _
            & " GROUP BY tblWorkList.[EQUIPMENT_CATEGORY];"
        Case "Craft"
            Me.RecordSource = "SELECT tblCraft.[CraftName] AS NAME, Count(tblWorkPackage.[WorkPackageID]) AS TOTAL" _
            & " FROM (tblCraft INNER JOIN (((tblWorkList INNER JOIN tblInScope ON tblWorkList.InScopeID = tblInScope.InScopeID) INNER JOIN tblWorkPackage ON tblWorkList.WorkPackageID = tblWorkPackage.WorkPackageID) INNER JOIN tblPlanner ON tblWorkPackage.PlannerID = tblPlanner.PlannerID) ON tblCraft.CraftID = tblWorkList.LeadCraftID) INNER JOIN tblScopeCategory ON tblWorkList.ScopeCategoryID = tblScopeCategory.ScopeCategoryID" _
            & " WHERE (((tblWorkList.AREA) Like " * ") AND ((tblInScope.InScopeName) Like " * ") AND ((tblPlanner.PlannerName) Like " * "))" _
            & " GROUP BY tblCraft.[CraftName];"
    End Select
End Sub

I currently have NAME and TOTAL displaying in the Datasheet. Also the combo box is displaying in the datasheet view.

Right now my issue is that when I make a selection from the combo box. The only change that happens in the datasheet is that the combo boxes field changes. I have attached two pictures to show what I mean.

What I would like to do is completely reload the datasheet with the new recordsource. This is for display purposes only and not for information to be changed.

Ive also tried the Me.Form.RecordSource still didn't update the datasheet.

[June7]

I just tested that and it worked for me. But did not use aggregate query and no table joins and no filter, just simple select.

Probably the quotes around the * wildcard are an issue. Double them or use apostrophe and no spaces around the * wildcard:

WHERE (((tblWorkList.AREA) Like '*') AND ((tblInScope.InScopeName) Like '*') AND ((tblPlanner.PlannerName) Like '*'))"

But if you want all records, why bother with the WHERE clause?

Suggest you start with simpler SQL statements.

[jerepois]

Thanks June,

Turns out it was just the quotes that was causing my issue. Now to clarify about the WHERE clause, I was adding in combo box selections for the where clause but I first needed to make sure the it worked and pulled everything first.

all is working now. thank you.