How Do I Check the Sum Of A Range Based On Another Table Entry

[lccrews]

This is going to sound weird, but I'm hoping this is possible.

There are two tables involved in my question. Tbl_1 contains an amount of 'raw material' to be used. Tbl_2 contains multiple steps wherein the raw material is mixed in batches. So let's say Magnesium Oxide (raw material) has an amount of 1000kg from tbl_1. There will be a few steps on tbl_2 where that ingredient is mixed in (step 1: 500kg, step 2: 0, step 3:500kg). I want to know if there is a way to check and balance the numbers. If I place 501kg in step 1 and 500kg in step 3, I now have 1001kg. I'd like an error message saying that I've exceeded the allowable limit for that raw material.

Is this possible? If so, how?



I was thinking of writing 2 strSQL in VBA: 1 to call the total of entries on tbl_2 and 1 to call the reference field from tbl_1. Then I'd do an IF > statement to present the error message. Am I on the right track?

[June7]

Can write a procedure with arguments and call that procedure from anywhere.

The procedure would be in a general module. It can be a Sub or Function depending on what you want to do.

Is each step a field or a record?

Might even be able to use Domain Aggregate functions as opposed to declaring and setting recordset objects.

Sub CheckMix(strRawMat As String, intRecID As Integer)
If DSum("Qty", "tbl_2", "ID=" & intRecID & " AND RawMaterial='" & strRawMat & "'") > DLookup("Amount", "tbl_1", "RawMaterial='" & strRawMaterial & "'") Then
MsgBox "Mix exceeds maximum allowed."
End If
End Sub

The real trick is figuring out what from what event to call the procedure.

[lccrews]

So tbl_2 would look like this:

[CO] [Step] [RawMaterial] [Weight]*
C0001 Step1 8005- Magnes. 30.4
C0001 Step3 8005- Magnes. 10.0

So each step is a record.

Real trick? I thought for sure that I should be coding the After Update Event...

The real dilemma for me is this: tbl_1 houses multiple formulas. I can't just do a DLookup without an SQL because the same ingredient may be used in multiple formulas. I think I need to write an SQL statement to aggregate the tbl_2 entries into one total and write an SQL statement to find the ingredient that belongs to the right formula. Yes?

IF DSum("Weight","sSQL" AND RawMaterial='" & RawMaterial & "'") > DLookup("Amount","sSQL2","RawMaterial='" & RawMaterial & "'")

I wrote '" & RawMaterial & "" because the field name is [RawMaterial]. Was that right?

[June7]

Right, domain aggregates can pull from queries as well as tables.

The DSum() syntax is not correct. Put a comma in place of the AND.

AfterUpdate event of what - [Weight] textbox?

Actually, record must first be committed to table before any of this will work. Record is committed when: 1) close table/query/form or 2) move to another record or 3) run code to save.

Otherwise, the values retrieved will not include the new record and you will have to add that weight into the If conditional.

[lccrews]

I got it working. Thanks June for the help!!!! I just added a DoCmd.Save before starting the raw material check.