Form Calculations

  • Thread starter Thread starter Jamie Dickerson
  • Start date Start date
J

Jamie Dickerson

I have a form that is used to input production. I have added coding to the
form to perform simple calculations (time saving for data entry). The form
seemed to be working fine. All calculations were correct and all information
was being sent to the correct table. Last week I noticed that if you are
working on record 10 in the form and go back to record 3 to make changes, all
calculations on record 4 through 10 are recalculated incorrectly. I find by
moving my curser to the cycle count field and tabbing fixes this error. But
this solution can be time consuming as it must be performed on every record
that has been changed.

Does anyone have an idea why this is happening and a possible solution to
the problem. I have included my code.
Private Sub Production_Cycles_AfterUpdate()
Dim myQuantity As Long

myQuantity = Nz(DLookup("[Lot Size]", "Standards", "MachineID = " _
& Me!Machine.Column(0) & " And Item=""" & Me!Item & """"), -1)


If myQuantity <> -1 Then
Me![Lot Size] = myQuantity
Else
MsgBox "No qty. available for this record"
End If
End Sub



Private Sub Production_Cycles_Exit(Cancel As Integer)
Me!Quantity.Value = Me!Cycles * Me![Lot Size]
End Sub
 
This is an example of why you shouldn't store calculated values in tables.

Since it can be calculated whenever you want, put the calculation in the
query for the form. Then the calculation becomes a "field" (a "virtual
field") in the query (a "virtual table") and can still be bound to a control
on a form/report.

In the query, the calc would look like

Quantity: [Cycles] * [Lot Size]


HTH
 
Back
Top