Mandatory Entry based on rest of form

  • Thread starter Thread starter pfm721
  • Start date Start date
P

pfm721

I have a subform that has a combo box where the user selects the appointment
type. I want to prevent the user from leaving the current record in any way
IF cboAppType IsNull and any of the other controls on the subform have a
value. I just don't know how to check the other controls for value and force
the user to enter a value in cboAppType if the other controls have a value.

any help is appreciated.
 
My first thought is to put some code into the subform's BeforeUpdate event.
Try this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ControlsHaveData Then
If IsNull(Me.cboAppType) Then
Cancel = True
MsgBox "You must select an Appointment Type"
End If
End If
End Sub

Private Function ControlsHaveData() As Boolean
Dim varCtl As Control
Dim varJunk As Variant

On Error Resume Next

'Cycle through all form controls
For Each varCtl in Me.Controls
'Get each control's Value.
varJunk = varCtl.Value

'If the control doesn't have a Value property, that's fine,
' just ignore it and move to the next control.
'If the control does have a Value property and the property
' has something in it, then return True.
If (Err = 0) And Not IsNull(varJunk) Then
'A valid control has data in it.
ControlsHaveData = True
Exit Function
End If

'If the control didn't have a Value property, clear the error
' and keep going.
Err.Clear
Next varCtl
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top