compare 2 field values before allowing further action.

[Robbyp2001]

Hello folks
I have a form [StockSub1] which contains a field [CurrBalance] and another [Numentered]. The user enters the number of items ordered and this is displayed in a subform below. The new current [CurrBalance] is recalculated, ready for the next [Numentered].
Is it possible to write some code that will produce a message for the user when the [CurrBalance] figure is lower than the [Numentered]? Eg CurrBalance = 50, Numentered = 51. I would like to cancel the request at this point.
What is happening is that the database is performing the calculation and in the cases like EG above, a negative figure is displayed.
The lowest figure for current stock is of course 0 (zero)
So:
If [Numentered] is < [CurrBalance] then perform the action (as it does already)
If {Numentered] is > [CurrBalance] then produce a MsgBox Warning to Cancel the action.
Many thanks
Rob

 

[Ranman256]

in the 'BeforeUpdate' event ,run the check

sub numEntered_BeforeUpdate()
 If [Numentered] <= [CurrBalance] then
    'run action
else
   msgbox "WARNING YOU CANNOT.."
   cancel = true
endif

 

[arnelgp]

yes of course you can

add code the NumEntered BeforeUpdate Event:

Private Sub NumEntered_BeforeUpdate(Cancel As Integer)

	If Trim(Me.NumEntered & "")<>"" Then
		If Me.Parent!StockSubForm.Form!CurrBalance < Me.NumEntered Then
			Msgbox "Insufficient stock!"
			Cancel = True
		End If
	End If
End Sub

 

[Robbyp2001]

Many thanks Ranman

This works to a point. The error message appears as planned, however when I click OK, it is followed by a message "The value violates the validation rule for the field or record". I then have to click OK then press ESC in order to clear the box.

This may be something to do with the way the database is set up. but I was hoping that by clicking OK on your MSG Box, everything would be reset. Is there a way to suppress any further messages and return the record to its prior condition? Any thoughts?

 

[Robbyp2001]

Thank you ArnelGP. Unfortunately I'm encountering the same problem as with Ranman's solution.

 

[arnelgp]

Private Sub NumEntered_BeforeUpdate(Cancel As Integer)

	If Trim(Me.NumEntered & "")<>"" Then
		If Me.Parent!CurrBalance < Me.NumEntered Then
			Msgbox "Insufficient stock!"
			Cancel = True
		End If
	End If
End Sub

 

[Robbyp2001]

Unfortunately, I'm now getting another error messaging saying cannot find the field CurrBalance.

If it helps, I have attached a copy of the dbase in order to show the error.
Fro the Switchboard select Staff Resources Orders

Then

select an item from the Item Description Combo box. The Current Stock Value will populate.

Enter a value in No Ordered > Current Stock Value.

 

[Robbyp2001]

Unfortunately, I'm now getting another error messaging saying cannot find the field CurrBalance.

If it helps, I have attached a copy of the dbase in order to show the error.
Fro the Switchboard select Staff Resources Orders

Then

select an item from the Item Description Combo box. The Current Stock Value will populate.

Enter a value in No Ordered > Current Stock Value.

Sorry, after you enter a value, press the Commit button to run the queries.

 

[arnelgp]

if this a stripped down version of your db.
something is lacking in it, anyway check this out.

 

[Robbyp2001]

Still the same issue. I'll have another look at the DB construction. Maybe that's where the problem lies. Thanks for your efforts.

 

[moke123]

you have

If Me.Parent!CurrBalance < Me.Numentered Then

however the field CurrBalance is on the subform not the Parent form
try

If Trim(Me.Numentered & "") <> "" Then
        If Me.CurrBalance < Me.Numentered Then
            MsgBox "Insufficient stock!"
            Cancel = True
            Me.Undo  ' added to clear the form
        End If
    End If

 

[Robbyp2001]

Hey Moke, this seems to have solved the problem. Bravo!!! You are a star!