Summing Continuous Form and Multiple Forms

[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

 

[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"

 

[MajP]

Here is another useful example.
I made a function to get the additive score. This could be used in place of the more complex query.

Public Sub test()
  Debug.Print GetAdditiveScore(1, 10)
 
End Sub

Public Function GetAdditiveScore(AddID As Variant, AddValue As Variant) As Integer
  If Not IsNull(AddValue) And Not IsNull(AddID) Then
    Dim strWhere As String
    strWhere = "AdditiveID = " & AddID & " AND " & AddValue & " >= RangeStart AND " & AddValue & " < RangeEnd"
    Debug.Print strWhere
    GetAdditiveScore = Nz(DLookup("score", "additiveRanges_T", strWhere), 0)
  End If
End Function

I can test this function and verify the somewhat complex criteria and see if I get the correct result.

Public Sub test()
  Debug.Print GetAdditiveScore(1, 10)
End Sub

In the immediate window

AdditiveID = 1 AND 10 >= RangeStart AND 10 < RangeEnd
3

So for urgency and a value of 10 I get a score of 3 which is correct because it is between 6 and 12. Debug.print is the most useful thing to debug your code.

 

[Weekleyba]

I finally got most things to work. Still plugging away.
I will definitely work through your example. So much great stuff to learn here.
I spent a little time looking into Debug.Print. I never know how that worked with the Immediate window. Very helpful!
I also didn't know you need to hit F5 to get it to execute. Ha.... just learning here.
I need to play with that some more and commit that to memory.
Thanks for the all the help/teaching.
I'm sure I'll be back once I have more time to on Sunday.

 

[MajP]

Do your self a favor and read this

Debugging VBA

www.cpearson.com

It is just a starter, but you will save hours and hours if you learn the debuging tools and tricks. There is more than just debug.print to help you.