Which event to attach code to

[teambond]

Hi,
I have the code below, that is required to calculate the cost of a part, based on the rollup of the component parts. I have the code attached to the subform On Current event, however it seems to be applying the RRP to all of the records currently showing in the subform of the main form, rather than just the current subform record.

When the record is first created I could apply this in the After Update of the Part number field, but this won't help when you are just viewing the form.

Is there an event you can use for loading a subform that will run the code against all the individual records for that linked main form? Does it need to be run from a main form event?

 Private Sub Form_Current()
    Dim TempOrderDate
    Dim strSQL
    Dim ParentPartNo
    Dim OrderedPartNo
    Dim TotalCost
    Dim RRP
    
    If Not (Form.NewRecord) Then
        TempOrderDate = Forms![CustomerOrderF]![OrderDate]
        ParentPartNumber = DLookup("PartNumber", "PartslistT", "ProductID=" & ProductID)
        OrderedPartNo = Me.ID & "_" & ParentPartNumber
    
        If TempOrderDate >= Date Then
            strSQL = "SELECT Sum(Query1.TotalCost) AS SumOfTotalCost, Query1.OrderedPartNo" & _
                    " FROM Query1" & _
                    " GROUP BY Query1.OrderedPartNo" & _
                    " HAVING (((Query1.OrderedPartNo)='" & OrderedPartNo & "'))"
            Debug.Print strSQL
        
            Dim RecordSet
            Set RecordSet = CurrentDb.OpenRecordset(strSQL)
                                 
                TotalCost = RecordSet("SumOfTotalCost")
            
            RecordSet.Close
                        
            RRP = TotalCost * 2.35 + 4.5
            
            Me.PartSellPrice.ControlSource = "=" & RRP
            
        End If
    End If 
                  
 End Sub

Thanks

 

[pr2-eugin]

Do not do this monstrosity ! Calculations are meant to be in Queries, not in tables. Your calculated fields will lead you straight to the paths of headache and patching up in future. Stop calculations now. Deal with them in Queries and Unbound controls on forms, do not use them in Tables. http://allenbrowne.com/casu-14.html

 

[teambond]

Thanks pr2-eugin,

The calculation is not going into a Table, that is exactly the point. I am unable to create a query that the whole form can run off as I have two different reference part numbers that can't be easily joined to make the form recordsource query. Hence I have to calculate the price each time the part number is being called. Believe me, it would be a lot easier to break the normalization rules and create a table of RRPs!

I've attached my other thread that explains why I have to do it this way.
http://www.access-programmers.co.uk/forums/showthread.php?t=264479

Any help on the form event, or a better way to do it, would be greatly appreciated.

Regards