Validating a field in data entry form after comparison of cumulati

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a data entry form where among other fields, a field 'amount' needs
to be validated befor the entry. Several different amounts (i.e. data entry) are made for a particular case. The sum of these amounts should be less than that of the total award amount which is in a field in a different table. i.e. I need to make sure for an amount entered for a particular case in the data entry screen, the sum
of all the amounts for that particular case should be less than the reward
amount for that case. The reward amount is a field in a different table. I have
no idea how to proceed.

Any help is appreciated. Thanks
 
Use the BeforeUpdate event of the form where amounts are entered, to sum the
amounts and compare to the RewardAmount in the other table.

Use DLookup() to get the reward amount. Details:
http://allenbrowne.com/casu-07.html

You will end up with something like this in the Event Procedure of your
form. It assumes your form's table has a primary key named "ID" and a
foreign key named "RewardID", both Number fields:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curReward As Currency 'The reward amount.
Dim curActual As Currency 'The amount entered so far.

strWhere = "(RewardID = " & Nz(Me.RewardID,0) & ")"
curReward = Nz(DLookup("RewardAmount", "RewardTable", strWhere),0)

strWhere = "(RewardID = " & Nz(Me.RewardID,0) & ") AND (ID <> " &
Nz(Me.ID,0) & ")"
curActual = Nz(DSum("Amount", "MyTable", strWhere), 0) + Nz(Me.Amount,
0)

If curActual > curReward Then
Cancel = True
MsgBox "Total cannot be greater than " & Format(curReward,
"Currency")
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Jack said:
I have a data entry form where among other fields, a field 'amount' needs
to be validated befor the entry. Several different amounts (i.e. data
Click to expand...
entry) are made for a particular case. The sum of these amounts should be
less than that of the total award amount which is in a field in a different
table. i.e. I need to make sure for an amount entered for a particular case
in the data entry screen, the sum
 
Back
Top