Field calcs continuous forms - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images Link Email

Field calcs continuous forms

Michael Medici

13 months ago

Hi all,

Running into a problem that should be relatively simple to fix.. I have a continuous form that allows me to add records at the bottom. This form is to track overtime charges to the customer for various positions (many different rates). The fields in question are 'laborrate' - that is the rate for the position, 'laborhours' the hours and 'totalcharge'. As I add a record, the laborrate is autofilled based on information I add in (i.e. employee name, position, location, etc). I manually add the laborhours and have a calculation on exit to populate totalcharge = labourhours*laborrate. Well, it looks like regardless of the laborrate, the calculation is using the highest laborrate previously used. Any clue how to make sure that the calculation only looks at the value within the current record? Is this a me.recordset moment?

	Sami Shamma 13 months ago
Without seeing your code, when you say you autofill it from information, there is a chance that you are still on a dirty record and your calculation is reading it from the last record that was saved. This is just an educated guess. Please share how you do your rate calculation.

Michael Medici OP

13 months ago

I am using several fields in the new record that I fill out and using dlookups to other tables.  It is a little complex as I threw it together to get reports out to our customer and I will look to streamline it in version 2.  The record does populate the correct laborrate for that position it is just the final calculation of the totalrate that is not right.

[ratecombo] = [ChargeType] & "-" & [EmpPosition] & "-" & [EmpSiteCode]
    If Not IsNull([EmpSiteCode]) Then
        'Populate Location, Local and Region
        [EmpSite] = DLookup("location", "qryotsitecodes", "rsitecode=empsitecode")
        [EmpLocal] = DLookup("localregion", "qryotsitecodes", "rsitecode=empsitecode")
        [EmpRegion] = DLookup("Region", "qryotsitecodes", "rsitecode=empsitecode")
        [ratecombo] = [ChargeType] & "-" & [EmpPosition] & "-" & [EmpSiteCode]

        'Populate Rate
        If [ChargeType] <> "Expenses" Then
           [LaborRate] = DLookup("otrate", "qryotrates", "ratecombo=" & "'" & ratecombo & "'")
           [LaborCurr] = DLookup("currency", "qryotrates", "ratecombo=" & "'" & ratecombo & "'")
        ElseIf [ChargeType] = "Expenses" Then
            [LaborRate] = 0
            [LaborRate].Locked = True
            [LaborHours] = 0
            [LaborHours].Locked = True
            DoCmd.GoToControl "totalcharge"
        End If

	Sami Shamma 13 months ago
Throw in. `Me.refresh` Before your calculations.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.