calculation question

[csolomon]

I have an unbound text box which I use to get a calculation, called DM_SampleWt. This text box's control source is:
=GetSize([cbo_matTypeID],Nz([matBatchweight],0),[Form].[Parent]![txtpan],[Form].[Parent]![cbo_panID].[column](2))
I have written a function that will get the required sample weight of a material selected from my form
[cbo_matTypeID]- represents a combo box that allows the user to select the material type (bound)
matBatchweight- represents the batchweight of each material (user input, bound)
[txtpan]-bound text box representing the number of pans that need to be made
cbo_panID.colum n(2)- bound combo box, represents the pansize needed for the job, the column 2 represents the formula used in the calculation needed.

Here is my function:
Public Function GetSize(matType As Integer, BatchWeight As Double, NoOfPans As Double, PanFormulaValue As Double) As Double
'returns the required sample weight, 'DM_ReqSampWt' calculation

Select Case matType
'Cement, Coarse, Fine
Case 1, 2, 3
GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27
Case 4
'Pigment
GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27 / 0.0022
Case Else
GetSize = 0
End Select
End Function

DM_SampleWeight is automatically calculated after all of the above are input. The problem I am having (the function works perfectly) is that for material cement (cbo_matTYpeID = 1), I need to sum the DM_SampleWeight and I can't because I can not get a TOTAL SUM for a calculated value. I tried to use the same function like this:

=GetSize(Sum([cbo_matTypeID]=1),Nz([matBatchweight],0),[Form].[Parent]![txtpan],[Form].[Parent]![cbo_panID].[column](2))

How can I approach this problem? Thank you

[ADezii]

I have an unbound text box which I use to get a calculation, called DM_SampleWt. This text box's control source is:
=GetSize([cbo_matTypeID],Nz([matBatchweight],0),[Form].[Parent]![txtpan],[Form].[Parent]![cbo_panID].[column](2))
I have written a function that will get the required sample weight of a material selected from my form
[cbo_matTypeID]- represents a combo box that allows the user to select the material type (bound)
matBatchweight- represents the batchweight of each material (user input, bound)
[txtpan]-bound text box representing the number of pans that need to be made
cbo_panID.colum n(2)- bound combo box, represents the pansize needed for the job, the column 2 represents the formula used in the calculation needed.

Here is my function:
Public Function GetSize(matType As Integer, BatchWeight As Double, NoOfPans As Double, PanFormulaValue As Double) As Double
'returns the required sample weight, 'DM_ReqSampWt' calculation

Select Case matType
'Cement, Coarse, Fine
Case 1, 2, 3
GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27
Case 4
'Pigment
GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27 / 0.0022
Case Else
GetSize = 0
End Select
End Function

DM_SampleWeight is automatically calculated after all of the above are input. The problem I am having (the function works perfectly) is that for material cement (cbo_matTYpeID = 1), I need to sum the DM_SampleWeight and I can't because I can not get a TOTAL SUM for a calculated value. I tried to use the same function like this:

=GetSize(Sum([cbo_matTypeID]=1),Nz([matBatchweight],0),[Form].[Parent]![txtpan],[Form].[Parent]![cbo_panID].[column](2))

How can I approach this problem? Thank you

You could try building the Summation Logic into the Function like so, but I have no idea if this will actually work:

Public Function GetSize(matType As Integer, BatchWeight As Double, NoOfPans As Double, _
                        PanFormulaValue As Double) As Double
'returns the required sample weight, 'DM_ReqSampWt' calculation
Select Case matType
  'Cement, Coarse, Fine
  Case 1    'Unique case?
    Dim MyDB As DAO.Database
    Dim rstCalc As DAO.Recordset
    Dim dblRunningSum As Double
    
    Set MyDB = CurrentDb()
    'Could also use an SQL Statement as basis for Recordeset ([Type] = 1)
    Set rstCalc = MyDB.openrecordset("tblCalculation", dbOpenForwardOnly)

    With rstCalc
      Do While Not rstCalc.EOF
        If ![Type] = 1 Then
          dblRunningSum = dblRunningSum + (![BatchWeight] * ![NumOfPans] _
                          * ![PanFormulaValue] / 27)
        End If
          .MoveNext
      Loop
      GetSize = dblRunningSum    'Set the Function = the Aggregate Total
    End With
    rstCalc.Close
    Set rstCalc = Nothing
  Case 2, 3
    GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27
  Case 4
    'Pigment
    GetSize = BatchWeight * NoOfPans * PanFormulaValue / 27 / 0.0022
  Case Else
    GetSize = 0
End Select
End Function