Making Fields Required On a Form so You Must Enter Something to Save Your Work

[kf1894]

I have a form with 25 questions that must be answered. Each question has an option group with the choices. How can I insure all questions are answered before the form is closed and/or saved. Or if they want to move on to the next record. I believe I need to add code to the before update field of the form, but I was hoping to find a way to avoid having list all 25 questions and associated messages. Any and all help is greatly appreciated, as I am new to Access and coding.

[mrojas]

You may want to add code such that it loops through all controls on the form checking for a given condition, in your case, that a field or fields are filled in or selected, whichever is the case.
The code would look something like this:

Dim ctl As Object
For Each ctl In Me.Controls
If TypeOf Ctl Is CheckBox Then
' some action
ElseIf TypeOf Ctl Is TextBox Then
ctl.Text = ""
ElseIf TypeOf Ctl Is Combobox Then
ctl.Text = ""
ctl.Clear
End If
Next
You could also use a Case structure instead of an If.

Add this code to the Close button at the OnClick event.

[kf1894]

Thanks - I have added the following code ....BUT The message comes up 25 times. How can I have the message appear only 1 time?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = False
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acOptionGroup
If ctl.Tag = "Req" Then
If Nz(ctl, "") = "" Then
MsgBox "Questions 1-25 are Required", vbCritical, "Required Field Missing"
ctl.SetFocus
Cancel = True
End If
End If
End Select
Next ctl
' check other controls here as needed
' you could use the .Tag property of controls to flag which ones are required
' Use code to loop through the controls to perform the validation
If Not Cancel Then
' passed the validation process
If Me.NewRecord Then
If MsgBox("Data has been saved", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code for new record before saving
End If
Else
If MsgBox("Data will be modified, Are you Sure?", vbYesNo, "Confirm") = vbNo Then
Cancel = True
Else
' run code before an existing record is saved
' example: update date last modified
End If
End If
End If
End Sub
https://www.accessforums.net/forms/m...2-newpost.html

[mrojas]

Check that at the first instance of a field validation failure, you exit the validation process and set the focus to the offending control.

[kf1894]

I'm new to this and do not understand your fix. Can you please show me what you mean or just correct the code.

[Missinglinq]

This will check each OptionGroup, one at a time, and allow you to correct any Null before moving on to check the next OptionGroup:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

Dim CName As String


For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acOptionGroup
            If Nz(ctl, "") = "" Then
              CName = ctl.Controls(0).Caption
              MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
              MsgBox CName & " is Required", vbCritical, "Required Field Missing"
              Cancel = True
              ctl.SetFocus
              Exit Sub
             End If
    End Select
Next ctl

End Sub

The secret, in checking multiple-controls, is that you have to use the line

Exit Sub

after each validation. This allows you to move to the errant Control and correct it. Without this, Access simply drops from one validation to the next to the next, not allowing any corrections to be made.

And this code has to be in the Form_BeforeUpdate event! It cannot be placed in the OnClick event of a 'Close Button,' as a Click event cannot be Canceled.

For checking other types of Controls, you'd simply add the type to the

Case acOptionGroup

line or add them as a separate Case. Other ControlTypes are acTextbox, acCombobox, etc.

You have a boat load of code, copied from somewhere, and the above is only intended to fix your validation of your OptionGroups. Once you get that part straight, if you need to do more, such as ask users if they want to save the Record, post back, and we can add that.

Linq ;0)>

[mrojas]

Well said MissingLinq. Very concise and elegant code

[Missinglinq]

Here's the same code I posted previously with the addition of a hack asking the users if they want to Save or Dump a New Record and Save or Dump changes to an existing Record:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

Dim CName As String


For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acOptionGroup
            If Nz(ctl, "") = "" Then
              CName = ctl.Controls(0).Caption
              MsgBox CName & " is Required", vbCritical, "Required Field Missing"
              Cancel = True
              ctl.SetFocus
              Exit Sub
             End If
    End Select
Next ctl

If Not (Me.NewRecord) Then
  If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
   Me.Undo
  End If
Else
  If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This New Record ???") = vbNo Then
   Me.Undo
  End If
End If

End Sub

Linq ;0)>

[kf1894]

Hi - when I added the code I get a runtime error 438 - Object doesn't support this property or method. it's hanging on CName = ctl.Controls(0).Caption. is this my lack of knowledge again?