Summing Continuous Form and Multiple Forms (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
Error. I'll change the 'UrgencyID' in the AdditiveRanges_T to 'AdditiveID'.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
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.

1609981983212.png


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

1609982503103.png



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.


1609982700602.png
 

Attachments

  • 1609982670772.png
    1609982670772.png
    81.4 KB · Views: 99
  • MIRAC ver10_FE - 3.zip
    332.8 KB · Views: 103

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
grandT.jpg
 

Attachments

  • MIRAC ver10_FE -MajP.accdb
    4.7 MB · Views: 123

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
Thanks MajP! I'll chew on this for awhile tomorrow. I am stretching my limits....... but I sure want to learn!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
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.
Qry.jpg

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" :cry: 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

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
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'.
1610036554378.png

The button has an OnClick event with the following code:
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

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
Code:
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

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
This query is not really updateable. In order to show it as updateable I had to change the property
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

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
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

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
So I have this in my Additives_F, OnClick event.

1610048645708.png


With this in a module, PublicFunction2.

1610048693771.png


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

1610048779497.png


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.

1610049051779.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
Look at my function name. I have a spelling error.
 

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
Son of a bugger.... I was looking for that too and could not see it for nothing.
I'll keep working.
Thanks!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
one little trick using intellisense if you forget the name. Call it by the whole module name.

intell.jpg
 

Weekleyba

Registered User.
Local time
Today, 14:36
Joined
Oct 10, 2013
Messages
586
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

Super Moderator
Staff member
Local time
Today, 15:36
Joined
Feb 19, 2002
Messages
42,973
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.

Code:
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
SubformReference.JPG
 

Attachments

  • ReferenceSubforms20210107c.zip
    1.8 MB · Views: 101

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:36
Joined
May 21, 2018
Messages
8,463
When I write any Sql string in code I do something like

Code:
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

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"

Insert.jpg
 

Users who are viewing this thread

Top Bottom