asked on

MS Access Field depends on other fields

I have a subform that is accessed from a main form. In the subform, I have a field that if the user selects "Yes" there are four other fields that need to be filled out (they cannot be empty).

I need to know:
a) What event do I attach the code to? (I need the code to execute whether the user moves to a different part of the application or closes the application altogether.)

b) Also need some sample code, if possible

Using MS Access 2003

Thanks so much in advance.

Ramanhp

i assume u have a SAVE button

on click event of SAVE button, check

if urFieldtoCheck.value = "Yes"
then
if otherField1.value <> "" and otherField2.value <> "" and otherField3.value <> "" and otherField4.value <> "" then
msgbox " values for field 1 to 4 cannot be blank, please fill all "
endif
endif

ste5an

Use the forms Before Update event to check your condition, see attached example. [payload1] is a boolean field, payload2 a text field.

This code must be placed in your subform. It prevents that Access saves this record.

It does not fire when your moving to another form. For this case you need the forms Deactivate event. But a form is not in all cases deactivated.
The best solution here is quite simple:

Use a modal, pop-up dialog form. Set the forms properties Pop Up and Modal to True, set the forms Border Style to Dialog and use the WindowMode parameter acDialog with DoCmd.OpenForm.

mfG
--> stefan <--

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

  Cancel = False
  If Not Me![payload1] Then
    Cancel = Len(Trim(Nz(Me![payload2], ""))) = 0
    MsgBox "Insert missing values."
  End If

End Sub

Open in new window

ste5an

A, a minor glitch...

mfG
--> stefan <--

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

  Cancel = False
  If Len(Trim(Nz(Me![payload1], ""))) > 0 Then
    Cancel = Len(Trim(Nz(Me![payload2], ""))) = 0
  End If

  If Cancel Then
    MsgBox "Insert missing values."
  End If
  
End Sub

Open in new window

Scott McDaniel (EE MVE )

ste5an's suggestion to use the Form's BeforeUpdate event would work. This event fires anytime you change data in a bound form, so it would detect when the user makes ANY modification to a value in your form.

From there, I'd do it something like this:

Sub Form_BeforeUpdate(Cancel As Integer)
Dim bOK As Boolean
If Me.YourField = True '/or If Me.YourField = "Yes"
'/check the other 4 values
bOK = Nz(Me.OtherField1, "") = ""
bOK = Nz(Me.OtherField2, "") = ""
bOK = Nz(Me.OtherField3, "") = ""
bOK = Nz(Me.OtherField4, "") = ""
End If
Cancel = bOK = True
End Sub

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial