Summing Continuous Form and Multiple Forms

[Weekleyba]

Error. I'll change the 'UrgencyID' in the AdditiveRanges_T to 'AdditiveID'.

 

[MajP]

I was just demonstrating how you make a scoring range table. I assume in real life you have an

Additive_T
AdditiveID
..... other additive fields

Then a project additives table
Projects_Additives_T
ProjectID_FK
AdditiveID_FK
AdditiveVal

Then you would get your scores by joining the project_Additives to the Additive_Ranges like shown. No need for all of those iif

 

[Weekleyba]

I'm so confused... I thought I could figure it out, but I'm afraid I need another assist.

So I need the subform, Additive_F, to populate when a new record is created in the main form, ProjectScoreSheet_F.
I'm trying to do that with this code. The first part works for the Category_F, the other subform.

I created the three tables AdditiveRanges_T, Additives_T, & Project_Additives_T.
I then created the query, Query1. All shown below.

But how do this all work together to populate the Additives_F?
I've attached the database to help solve this because I'm quite sure I'm not giving enough info above.
THANKS for all you help! I feel I'm getting close but I don't understand how this all works together.

 

[MajP]

 

[Weekleyba]

Thanks MajP! I'll chew on this for awhile tomorrow. I am stretching my limits....... but I sure want to learn!

 

[MajP]

The biggest issue was that you forgot to include the ProjectID_FK in your query. Therefore you could not link the main form project id to the subform ProjectID_FK. I simply added that field to the query. Linked the Main to the sub. I included the additive table in the query too so you could show the additive name.

Once I added the foreign key I could do an aggregate on this query on this query to get the sum of additive score by project ID.
As I said yesterday then I can make one more query joining the sum of the categories by project with the sum of the additives by project to get the grand total per project. I felt bad you called my query from yesterday "Junk" and hid it, but I used it anyways. I did the same tricks as yesterday making a wrapper function for the grand total based on this grandtotal query.

 

[Weekleyba]

Oh.... that's funny. I didn't think I needed that query but didn't want to delete it either so, I normally label those objects as 'JUNK' until I decide that I really don't need them. Hiding it was a mistake... I was going to rename it but, due to my inexperience, I wasn't sure where it went. Until you found it yesterday, I then decide to figure out how to unhide it, which I did. All that to say, I had a good laugh and that it was not intentional.

I do have a problem here that I can't figure out. (Imagine that...)
I want to populate the Additives_F with Additives with a Append button and when the form has met the criteria to create a record.
Let's just take the Append button issue.
Right now, if I click the Append button it gives me the error below. Run-time error '3073'.

The button has an OnClick event with the following code:

Private Sub AppendButton_Click()

Dim SQLAdditive As String

DoCmd.SetWarnings False

SQLAdditive = "INSERT INTO ProjectAdditive_Q (Additive, ProjectID_FK) " _
& "SELECT Additives_T.Additive, Project_T.ProjectID " _
& "FROM Additives_T,Project_T " _
& "WHERE(((Project_T.ProjectID)=[Forms]![ProjectScoreSheet_F]![ProjectID]))"

DoCmd.RunSQL SQLAdditive
DoCmd.Requery

DoCmd.SetWarnings True

End Sub

The query ProjectAdditive_Q appears to be updatable, as I can type directly into it.

How do I correct this?

 

[MajP]

Public Sub InsertAdditive(ProjID)
 Dim SQLAdditive As String
 
 SQLAdditive = "INSERT INTO Project_Additives_T (AdditiveID_FK, ProjectID_FK) "
 SQLAdditive = SQLAdditive & "SELECT AdditiveID," & ProjID & " AS ProjectID FROM Additives_T"
 'Debug.Print SQLAdditive
 CurrentDb.Execute SQLAdditive, dbFailOnError

End Sub

 

[MajP]

This query is not really updateable. In order to show it as updateable I had to change the property

RecordsetType Property - Microsoft Support

support.microsoft.com

Also you had problems with it anyways "Additive" instead of additiveID_FK
I always update a single table then if I have to I update the child records I do another insert. I have never done an insert into another query.

 

[Weekleyba]

I'm still struggling here but it's no doubt due to my inexperience.

The Public Sub InsertAdditve(ProjID) that you created above, what calls that? Is that in an event?
Are you placing that on the ProjectScoreSheet_F or the subform Additives_F?

 

[MajP]

Put that in a standard module and from your button call it.
You can get the project id off the main form using the parent property
dim ProjID
projID = me.parent.ProjectID ' or whatever the real name of the project id
insertAdditves projID

 

[MajP]

I like to build functions like this because I can test this easily.
for example in immediate window I can run this just like
InsertAdditive 74

 

[Weekleyba]

So I have this in my Additives_F, OnClick event.

With this in a module, PublicFunction2.

But when I click the button I get this compile error.

How to I correct?

Also, when I run it from the Immediate window, it returns the value I inputted. Is that correct? Trying to wrap my brain around this.

 

[MajP]

Look at my function name. I have a spelling error.

 

[Weekleyba]

Son of a bugger.... I was looking for that too and could not see it for nothing.
I'll keep working.
Thanks!

 

[MajP]

one little trick using intellisense if you forget the name. Call it by the whole module name.

 

[Weekleyba]

Nice! I'll remember that.

 

[Weekleyba]

MajP,
Can you walk me through how this Sub works?

Public Sub InsertAdditive(ProjID)
Dim SQLAdditive As String
SQLAdditive = "INSERT INTO Project_Additives_T (AdditiveID_FK, ProjectID_FK) "
This line above defines SQLAdditive and is going to insert something in the two fields, in the table shown.
SQLAdditive = SQLAdditive & "SELECT AdditiveID," & ProjID & " AS ProjectID FROM Additives_T"
I really don't understand this line at all.
'Debug.Print SQLAdditive
How do you use this to test?
CurrentDb.Execute SQLAdditive, dbFailOnError
I'm guessing this is needed to make this run.
End Sub

 

[Pat Hartman]

I modified a sample for you that might be useful. It has two subreports on a main report. The example isn't real. The main report breaks on date and so do the subreports. I've included a picture with three sets of calculationsl
Fuchia = reference to the DateFooter in the relevant subreport
Aqua = Add controls holding the subform references
Green = Since you cannot sumarize controls, you can only summarize field references, code is used to accumulate for the grand total.

Option Compare Database
Option Explicit

'Define variables in module header so they persist through multiple execution of form events.
Private PTSum   As Currency
Private CCSum   As Currency

Private Sub DateFooter_Format(Cancel As Integer, FormatCount As Integer)

'' only add the first time the format event runs for this section
'' The format event must run multiple times if Access has trouble breaking at the end of the page because of overflow.
'' FormatCount increments each time the Retreat event runs.

    If FormatCount = 1 Then
        PTSum = PTSum + Nz(Me.srptProductType.Report!DateFooterSum, 0)
        CCSum = CCSum + Nz(Me.srptCostCenter.Report!DateFooterSum, 0)
    End If
    Debug.Print Format(PTSum, "Currency") & " --- " & Format(CCSum, "currency") & " --- " & Format(PTSum + CCSum, "Currency")
End Sub

Private Sub Report_Open(Cancel As Integer)
    PTSum = 0
    CCSum = 0
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtGrandTotal = PTSum + CCSum
    '' reset accumulation.  Otherwise on print preview, the total will just keep incrementing
    PTSum = 0
    CCSum = 0
End Sub

 

[MajP]

When I write any Sql string in code I do something like

Dim strSql as string
strSql = " some complex sql string"
Debug.print strSql

This will print the resolved sql String in the immediate window. As I said I usually put functions and procedures like this in standard modules which can often be easily tested. I can run this using a literal project id with a simple Test procedure. Now I can see what the SQL string looks like and I know it is good to go.

CurrentDB.execute Sql is the same as docmd.runSQL

CurrentDb.Execute vs. docmd.runsql - Eileen's Lounge

Database.Execute method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

I often break up long strings as shown instead of using the _

Dim strSql
strSql = "some string ... "
strSql = strsql & " more stuff" ' just takes strsql and adds to it.

at this point strSql would equal
"some string ... more stuff"