Updating SubForm Data

[newbie87]

Hi,

Im having an issue that I'm hoping somebody could help with?

I have a main form called frmDetails, on this form there are a number of textfields, with one in particular that I use quite a bit, this is called Total, with adds values together from the other textfields and displays the total.

I also have a sub form on the frmDetails, called SubDetails which is based on a query., the query allows me to enter a code on frmDetails and populates the form and subform with the data with the matching code. so for example if i use code 321, it brings back all data related to that code (which is unique)


On the subform I need to divide the Total from the main form and display the answer in the sub form subTotal, I can get this to work one row at a time but the OnClick event in the SubTotal textfield event.

But I need to allow the subform to update all rows under the column SubTotal at the same time depending on the quantity in the quantity column. this is the code i have at the moment for the OnClick event:

me.SubTotal = Forms!frmDetails!Total / me.Quantity

I've tried inserting the calculation on the OnExit event for Total (frmDetails) but again this only updates row 1 instead of all rows. My subform is as follows:

ID | Quantity | SubTotal | Cost
1      1200                 15
2      1300                 16
3      1400                 17

Can anybody help me?

 

[MStef]

Try next:
The code put in "On Current" event, (in subform).

 

[newbie87]

Hi,

Thanks for this, but the code didn't do anything.

 

[vbaInet]

Why are you trying to save the calculated value? You shouldn't be doing this:

http://allenbrowne.com/casu-14.html

If you set the Control Source of the textbox it will calculate automatically for all records. I can tell you are using a Continuous Form or a form in Datasheet view.

 

[MStef]

Try this:
me.SubTotal = Me!Parent!Total / me.Quantity

 

[vbaInet]

Try this:
me.SubTotal = Me!Parent!Total / me.Quantity

It won't work if this is a Continuous Form or a form in Datasheet view. We can deduce this from here:

My subform is as follows:

ID | Quantity | SubTotal | Cost
1      1200                 15
2      1300                 16
3      1400                 17

 

[newbie87]

Hi,

Many thanks for your responses.

vbaInet: I understand where you are coming from, but I have been told to do it this way after many many conversations.

MStef: Many thanks for you help.

I have solved this issue now by creating a function (SubTotal) and calling that function into the following procedure, this updates all rows with the calculation:

Dim tmp As Variant
Dim tmprs As Variant
Set tmprs = [SubDetails].Form.Recordset
Set tmp = [SubDetails].Form.Controls

    If Not tmprs.BOF Then
        tmprs.MoveFirst
    End If
    While Not tmprs.EOF

        For Each tmpcontrol In tmp
            If tmpcontrol.Name = "SubTotal" Then
                Call SubTotal (SubTotal, total)
            End If
            
        Next tmpcontrol

        tmprs.MoveNext
    Wend
    tmprs.MoveFirst
End If