To filter records by category, will need the CatID in the subform's RecordSource. Do not need the Sale table in that query. Instead of referencing query object, could put SQL statement in the RecordSource.
SELECT SALE_DETAILS.*, Item.*, [QtyOrdered]*[ItemPrice] AS Total FROM Item INNER JOIN SALE_DETAILS ON Item.ItemID = SALE_DETAILS.ItemID;
Autonumber PK usually has no meaning to users and does not need to be displayed.
Rename the combobox to cbxItem.
Then code in the button Click events like:
Code:
Private Sub CmdDrink_Click()
Me.cbxItem.RowSource = "SELECT ItemID, ItemName FROM Item WHERE CatID=2 ORDER BY ItemName;"
Me.cbxItem.Requery
Me.Filter = "CatID=2"
Me.FilterOn = True
End Sub
Private Sub CmdFood_Click()
Me.cbxItem.RowSource = "SELECT ItemID, ItemName FROM Item WHERE CatID=1 ORDER BY ItemName;"
Me.cbxItem.Requery
Me.Filter = "CatID=1"
Me.FilterOn = True
End Sub
Alternatively, could have two subforms placed on pages of a Tab control. Each subform would have its Filter and FilterOn properties set in design to filter the appropriate category. The combobox could also have static filter criteria. No VBA needed. But this would mean building two forms that are identical, just with different filter properties. This would simplify the grand totals you want to calculate on the main form. Otherwise, you will need DSum() domain aggregate functions to provide those calcs.
Heineken Beer is coded as a food item.