We help IT Professionals succeed at work.

MS Access   Field depends on other fields

jonsuns7
jonsuns7 used Ask the Experts™
on
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.
   
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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
ste5anSenior Developer

Commented:
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

ste5anSenior Developer

Commented:
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 )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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
Top Expert 2016
Commented:
you have to check for the other four fields...
place a value to the Tag property of the 4 controls, < Req >

private sub form_beforeUpdate(cancel as integer)
dim ctl as control

if me.checkbox=true then
   for each ctl in me.controls
       if ctl.tag="Req" then
          if ctl="" then
             msgbox "Required field"
             cancel=true
             exit sub
          end if
      end if
    next
end if

end sub