CODEWITHSUNDEEP
formsms-accessvba
Pinnichio
Pinnichio

Reputation: 15

Require that form fields are filled before saving a record

I have a form with fourteen fields, ten of which are always required, and three of which (Other Absence Type, Illness Type and Other Illness Type) are conditionally required. There is also a Save Record button on the form, which has a subroutine meant to check that all required fields are filled before saving the record. The ten required fields have their Required property set to Yes in the table.

The code for enabling/disabling the three conditionally required fields appears to work on its own, and the sub-routine for checking if all required fields are filled also appears to work on its own. However, whenever one of the conditionally required fields is enabled but also left blank and I try to save the record, the form freezes and I can no longer make changes.

Private Sub Form_Load()
    With Me
        .FirstName.SetFocus
        .Other_Absence_type.Enabled = False
        .Illness_type.Enabled = False
        .Other_Illness_type.Enabled = False
    End With    
End Sub

Private Sub Absence_type_Click()
    If [Absence type] = "Staff illness" Then
        [Illness type].Enabled = True
    Else
        Me.[Illness type].Value = ""
        Me.[Illness type].Enabled = False
        Me.[Other Illness Type].Value = ""
        Me.[Other Illness Type].Enabled = False
    End If

    If Me.[Absence type] = "Other (Please specify)" Then
        Me.[Other Absence Type].Enabled = True
        Me.Other_Absence_type.SetFocus
    Else
        Me.[Other Absence Type].Value = ""
        Me.[Other Absence Type].Enabled = False
    End If
End Sub

Private Sub Illness_type_Click()
    If Me.[Illness type] = "Other (Please specify)" Then
        Me.[Other Illness Type].Enabled = True
        Me.Other_Illness_type.SetFocus
    Else
        Me.[Other Illness Type].Value = ""
        Me.[Other Illness Type].Enabled = False
    End If    
End Sub

Private Sub SaveRecordNew_Click()
    On Error GoTo ErrorHandler
    DoCmd.RunCommand acCmdSaveRecord
ErrorHandler:
    MsgBox "One or more required fields are blank", vbExclamation

Upvotes: 0

Views: 7756

Answers (1)

kiks73
kiks73

Reputation: 3758

Supposing that validate() is the function that will check that every field is filled, you need to call it in the before_update event of the form. This is the function:

Function validate() as Boolean
  Dim ret as Boolean
  If (textbox1.Value & "") = "" or (textbox2.Value & "") = "" or ... Then
    ret = false
  Else
    ret = true
  End If
  validate = ret
End Function

Before_Update form event:

Private sub Form_BeforeUpdate(cancel as integer)
    If not validate() then 
       MsgBox "Fill all required fields, please"
       cancel = true
    End If
End Sub

Upvotes: 1

Related Questions