Validating a field value based on another field.

  • Thread starter Thread starter Gina
  • Start date Start date
G

Gina

Hi!

I have a form called frmMatlDetail, with a subform on it
called frmMatlActivity (the subform is based on a query
called qryMatlActivityForm). On the subform I have a
field called Activity (which can have a value of P, A, U,
R, or ADJ) and a field called Qty.
If a user enters P in the Activity field, I want to be
sure that the Qty field remains Null.
If a user enters A or U in the Activity field, the Qty
field must be a negative number.

Is there a way to do this with a macro or code?

Thanks so much.
 
Gina,

On the subform, do the following:

1. Create the following procedure in the form's class module:
Public Function CheckQty()
Select Case Me.Activity
Case "P"
Me.Qty = Null
Case "A", "U"
If Me.Qty > 0 Then
DoCmd.Beep
MsgBox "Qty must be negative!"
Me.Qty = ""
End If
Case Else
'Do whatever...
End Select
End Function

2. In Activity's AfterUpdate property (the property - not the event
procedure), add the following code:
=CheckQty()

3. In Qty's AfterUpdateEvent, add the following code
=CheckQty()

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks so much...works great!

Gina

-----Original Message-----
Gina,

On the subform, do the following:

1. Create the following procedure in the form's class module:
Public Function CheckQty()
Select Case Me.Activity
Case "P"
Me.Qty = Null
Case "A", "U"
If Me.Qty > 0 Then
DoCmd.Beep
MsgBox "Qty must be negative!"
Me.Qty = ""
End If
Case Else
'Do whatever...
End Select
End Function

2. In Activity's AfterUpdate property (the property - not the event
procedure), add the following code:
=CheckQty()

3. In Qty's AfterUpdateEvent, add the following code
=CheckQty()

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd- 0764559036.html





.
Click to expand...
 
Back
Top