Subform/Continuous Form

[Blanic]

If I have a field called [field1] in a continuous form, how do I access the data in record 1 or record 2 of [field1].
Is it an indicie? like [field1](1), [field1](2), etc..
I am trying to add a field from the main form and keep a running count on the subform . Example Mainform [fielda]+ Subform [field1]= Subform [field2] of the 1st record

Next,
Subform [field1]+ Mainform [fielda]=Subform[field2] of record 2. I need it to add them but it wont add correctly when I do a running count.
Basically I need to know how to identify record 1 from record 2 etc of the subform
Thanks

 

[charityg]

If I'm reading your post correctly, it doesn't look like you need a running sum at all.

Are you just adding the value of a field on your main form to a value of a field on a subform?

I'll try to make sense of this. If you have a continuous subform, your Current event is pretty useless for setting the values of unbound controls. Saying that, I think what you need to do is this.

Add textbox to your subform. Set the visible property to false, the name to txtMainFieldA, and controlsource to "=Parent!fieldA".

Then the field you want to contain the addition (I believe you call it field2) set the controlsource to "=field1+txtMainFieldA"

I hope this is what you are looking for, if not let me know.

~Charity

 

[Blanic]

Ok you following along what I was doing, but my problem appears when I add the first line. Example
MainForm [fielda]=10
Subform [Field1]=1
[Fielda]+[Field1]=11
Now I want to take that 11 and place it into another field and add that field to fielda + field1, but when I do the totals dont add up correctly. I want to run a count so that when I start with a number and add to it, it keeps adding off of the main number, plus any other number, but shows the totals as I add to it. Make sense?
Thanks again Charity

 

[charityg]

Okay, I've got your answer. First copy and paste this function into a new module.

        Function RunSum (F As Form, KeyName As String, KeyValue, _
        FieldToSum As String)
      '***********************************************************
      ' FUNCTION: RunSum()
      ' PURPOSE:  Compute a running sum on a form.
      ' PARAMETERS:
      '    F        - The form containing the previous value to
      '               retrieve.
      '    KeyName  - The name of the form's unique key field.
      '    KeyValue - The current record's key value.
      '    FieldToSum - The name of the field in the previous
      '                 record containing the value to retrieve.
      ' RETURNS:  A running sum of the field FieldToSum.
      ' EXAMPLE:  =RunSum(Form,"ID",[ID],"Amount")
      '***********************************************************
         Dim RS As Recordset
         Dim Result

         On Error GoTo Err_RunSum

         ' Get the form Recordset.
         Set RS = F.RecordsetClone

         ' Find the current record.
         Select Case RS.Fields(KeyName).Type
            ' Find using numeric data type key value?
            Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
               DB_SINGLE, DB_DOUBLE, DB_BYTE
               RS.FindFirst "[" & KeyName & "] = " & KeyValue
            ' Find using date data type key value?
            Case DB_DATE
               RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find using text data type key value?
            Case DB_TEXT
               RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
            Case Else
               MsgBox "ERROR: Invalid key field data type!"
               GoTo Bye_RunSum
         End Select

         ' Compute the running sum.
         Do Until RS.BOF
            Result = Result + RS(FieldToSum)

            ' Move to the previous record.
            RS.MovePrevious
         Loop

      Bye_RunSum:
         RunSum = Result
         Exit Function

      Err_RunSum:
         Resume Bye_RunSum

      End Function

Forget everything I told you to do before. In the textbox that you want to display the running sum, name it runningsum, enter this as the control source, (change fieldnames and form/subform names where appropriate.)

=RunSum([forms!mainform!subform],"uniqueID",[UniqueID],"field1")+forms!main!fielda

UniqueID is the name of the field that is your "primarykey" for the subform records. If you don't have this field in your underlying table, you need to add one. Also add that field to your subform (you can set visible to false if you don't want to see it).

Let me know how it works

~Charity