Validate subform value with Message box
I have a form [Order Entry] and subform [Order Details Subform] and fields on the subform [Quanity] and [Product Description].
I want to run a before update that checks the quanity. If it is greater than 10 I want to prompt the user to verify amount.
If the amount is okay set focus on product description if not set focus on quantity to make correction.
I want to run a before update that checks the quanity. If it is greater than 10 I want to prompt the user to verify amount.
If the amount is okay set focus on product description if not set focus on quantity to make correction.
GRayL
What are the names of the controls containing Quantity And Product Description. We can only set focus on a control, not a field.
Also, we should assume that this check is in the subform record?
You can make a macro in the fields subform to check for quantity, pop a msg box if greater than 10. No need to move to description if not greater than 10 if that is the next tab stop of the form.
ASKER
Control Name and field name are the same
ASKER
Yes the validation check is in the subform. Forgot to include that.
ASKER
DoDahD,
It is possible for the quantity value to be greater than 10, but would not happen often. this VBA validation would be a more for verifying user's data enter accuracy.
It is possible for the quantity value to be greater than 10, but would not happen often. this VBA validation would be a more for verifying user's data enter accuracy.
Use
Private Sub Quantity_BeforeUpdate()
If Me.Quantity>10 then
Dim i as integer
i=msgbox("Are you sure this is the Correct value",vbYesNo)
If i=vbNo
Cancel=True
Undo
End If
Private Sub Quantity_BeforeUpdate()
If Me.Quantity>10 then
Dim i as integer
i=msgbox("Are you sure this is the Correct value",vbYesNo)
If i=vbNo
Cancel=True
Undo
End If
What version of access are you using.
sb9 has provided a VBA code for the Before Update event in the Quantity field of the subform. This will provide the logic you requested.
ASKER
I am getting a syntax error and it is pointing to the if i=vbno portion
Private Sub Quantity_BeforeUpdate(Cancel As Integer)
If Me.Quantity > 10 Then
Dim i As Integer
i = MsgBox("Quantity is 10 or more. Is this correct", vbYesNo, "Verify Quantity")
If i=vbNo
Cancel = True
Undo
End If
End SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry that what a mistake in my original post
ASKER
Thanks for the help.
Hi,
I think you need a "Then" after the IF i=vbNo Statement:
If i=vbNo Then
Cancel = True
I think you need a "Then" after the IF i=vbNo Statement:
If i=vbNo Then
Cancel = True
I hope the other thing you learned is to never accept the default control names for controls on an Access form. They will always be identical to the field name to which they are bound. Headaches will abound when troubleshooting. Have a good one!