Subform SQL Recordsource with Sum

[mpkimler]

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.

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

[ranman256]

you don't need to write code for this. assign a query to the subform. it should get the sql correct w/o typos.
no need to do more work.

the footer of the subform can show the subform total via text box: =Sum(Qty)

[June7]

Why do you need to programmatically set RecordSource?

I found I had to save the form with the SQL statement in RecordSource property.

[mpkimler]

I apologize. I had removed the WHERE Clause in an attempt to troubleshoot my code. Updated code below with the WHERE clause. My form is setup with a txtBox that allows the user to input what Module they want to filter by. My main Form has other things that the User can select and there is a text box "Me.txtModule" that the user input a module into which is then used in the code below so that they can filter down and see a certain MODULE. The intent of this Form is for it to be used by "Non Access DB Gurus" as a tool for work. This is very simple code for now but will further be expanded on with more WHERE clauses. However, I cant programtically change the RecordSource with VBA yet so i'm hoping someone can tell me why this works fine in a Query but not in a SubForm.

Private Sub Command48_Click()Dim strSQL As String
strSQL = "SELECT tblInstruments.MODULE, tblInstruments.MANUFACTURER, tblInstruments.MODEL_NUMBER, tblInstruments.DEVICE_TYPE, SUM(tblInstruments.QUANTITY) As TOTAL " _
& "FROM tblInstruments WHERE tblInstruments.MODULE = '" & Me.txtModule & "' GROUP BY tblInstruments.MODULE, tblInstruments.MANUFACTURER, tblInstruments.MODEL_NUMBER, tblInstruments.DEVICE_TYPE"
Me.cntrlsubfrmInstruments.Form.RecordSource = strSQL
Me.cntrlsubfrmInstruments.Form.Requery
End Sub

[June7]

Don't think I've ever seen this arrangement.

Assuming textbox and button are on main form.

I was able to get working by setting RecordSource as dynamic parameterized SQL statement in form design.

Then code: Me.cntrlsubfrmInstruments.Requery - .Form was not needed.

I also tested setting RecordSource with SQL statement without WHERE clause in design then code:
Me.cntrlsubfrmInstruments.Form.Filter = "Module='" & Me.txtModule & "'"
Me.cntrlsubfrmInstruments.Form.FilterOn = True

Why not use a combobox to select module?

As ranman256 suggested, don't use aggregate query, just bind to table and use textbox to display Sum() in form footer.

[mpkimler]

I agree. It will be a Listbox eventually, i'm just trying a proof of concept and doing a bad job of explaining it. I didnt want to go too far with the form design if the concept was flawed and the textbox was just an easy way to test out the user input and in hopes of making you understand that what I'm trying to do isn't just a one time structured statement to generate a queried output using the Query builder. This is part of an existing standalone Access database application that several engineers use. There is a main form that for now just has a command button and a textbox to simulate the ability to pass a variable parameter and include it in the SQL statement. I then want to use that SQL string to make data appear on the screen through use of a subform. However, the SUM function is kicking my tail.

I guess i'm not familiar with a dynamic parameterized SQL stagement in form design. I have always just created dynamic SQL through user forms by concatenating the string statements and then passing it as the recordsource to a subform to present data in different ways. Often using nested combo boxes or something.

The form footer Sum() isnt want i'm trying to do. I'm trying to basically do what a Pivot Table does in excel.

Here is a quick table example:

MODEL_NUMBER         QUANTITY
A                          2
A                          3
A                          5
B                          1
B                          2
B                          3

After SQL statement is ran:

MODEL_NUMBER           QUANTITY
A                         11
B                          6

There are thousands of parts and a few dozen modules so i cant use a footer type sum box. I'm trying to generate a datasheet view of the data where the same Model_Number is condensed (Group By) into a single row and the total count is summed up for each part. In the end, I want to have each part only listed once but with the total count.

I may perhaps be in over my head here. I am able to get this to do exactly what I want in a Query, but I don't know how to pass variables to a Query from a form. I just hard code the variable in the Query SQL but that doesn't help with the other engineers on my team who cant spell SQL haha. I appreciate the help, I hope I haven't made this way more complicated than it already was.

[June7]

Suggest you read up on dynamic parameterized query - basic Access functionality. The parameter can be a popup input prompt or reference control on form, examples:

SELECT * FROM table WHERE AccountNum=[Enter account number];

SELECT * FROM table WHERE AccountID = Forms!Main.cbxAcct

I don't use dynamic parameterized queries. I prefer method of VBA setting form or report Filter property.

A report is really the best vehicle for presenting this output.

[mpkimler]

Thanks for the feedback, I will look into it!

For anyone else who might ever have this need, I did figure out what the problem was. The Subform Control (located on the main form) I'm using to display the data was using another Form as the object source. The Form being used as the object source didnt like a SQL string as its Record Source if it contained the Sum or Count functions. It would never show the Alias field for the Sum or Count functions, only the fields added directly to the Form. so I changed the object source property of the Subform control to my Query which was working correctly. This then allowed me to pass a SQL string as the record source using the SUM, COUNT SQL functions and do other neat math tricks. You can even change the Object Source using VBA on the fly if you wanted to change to a different Query. Pretty cool....