I am using VBA to set the RecordSource of a SubForm. I'm doing so with the code below. The [Fields] MODULE, MANUFACTURER AND MODEL_NUMBER may exist in the table multiple times each with a varying QUANTITY. What i'm trying to do is group them up and sum the QUANTITY field to condense the subform table into a more useful collection of data. The code works just fine in a Query and provides the desired output. When I pass this code as the recordsource to the subform however, it just spits out a #Name? in the QUANTITY field and doesnt add a field for TOTAL. All of the other fields are output as I would expect.
Do SubForms not allow for one to use the SUM function in SQL for a RecordSource? I need this type of functionality over a Query as I want this to be a user Form to select different MODULES and spit out the data. It obviously doesnt like the ALIAS (TOTAL) as it doesnt even show up in the Subform.
Code:
Private Sub Generate_BOM_Click()
Dim strSQL As String
strSQL = "SELECT tblInstruments.MODULE, tblInstruments.MANUFACTURER, tblInstruments.MODEL_NUMBER, tblInstruments.DEVICE_TYPE, SUM(tblInstruments.QUANTITY) As TOTAL " _
& "FROM tblInstruments GROUP BY tblInstruments.MODULE, tblInstruments.MANUFACTURER, tblInstruments.MODEL_NUMBER, tblInstruments.DEVICE_TYPE"
Me.cntrlsubfrmInstruments.Form.RecordSource = strSQL
Me.cntrlsubfrmInstruments.Form.Requery
End Sub