MsAccess form - empty records originally, but needed to be filled mandatory, once the data entry started

Question

I have MsAccess Form:

ClientName, ID fields - records filled with the information
Gender, Race, PrimaryLanguage fields (combo box type) are empty records

Users will fill Gender, Race and PrimaryLanguage, PrimaryDisability fields with the information. My goal is - before user will move to the next record, I want he to be unable to proceed until those fields (Gender / Race) are filled with its content.

The problem is - Gender / Race / PrimaryLanguage, PrimaryDisability for the existing clients have to stay empty, but at the same time - when user starts filling it, it needs to be filled mandatory.

It works this way only in case if I am adding a new record. (I have Gender / Race fields as required) - but doesn't work with the existing records.

PS - the answer provided below is great, but worked on my end for 2 fields only What about 3 or more fields?

I had idea of adding the event procedure to each button in the Form, not to the whole Form. code should be something like:

Private Sub Gender_BeforeUpdate(Cancel As Integer)
    If Nz(Me.Gender, "") = "" And (Nz(Me.Race, "") <> "" 
       Or (Nz(Me. PrimaryLanguage, "") <> ""  
       Or (Nz(Me. PrimaryDisability, "") <> "")
    Then
       MsgBox "Select Gender", vbInformation
       Cancel = True
   End If
End Sub

But it says about syntax mistakes

Please help!

While the answer from Santosh below will do what you need and is probably sufficient for the scope of your project, the proper way of doing it is to use an unbound form. That allows the user to add all relevant data and the developer to run any validation necessary before a record is even created. By using a bound form, a blank record is added as soon as the user starts adding data. While you can prevent the user from moving to the next record, you already have trash, in the form of an empty or incomplete record, in your database every time anything goes remotely wrong. — SunKnight0, Jul 18 '18 at 19:53

Santosh · Accepted Answer · 2018-07-18T08:45:45.620

2

Use BeforeUpdate event to cancel updating of a record before moving to another record

 Private Sub Form_BeforeUpdate(Cancel As Integer)


  If Nz(Me.ClientName, "") <> "" And Nz(Me.Gender, "") = "" And Nz(Me.Race, "") = "" And Nz(Me.PrimaryLanguage, "") = "" And Nz(Me.PrimaryDisability, "") = "" Then
          Exit Sub
  End If

  If Nz(Me.Gender, "") = "" Then
          MsgBox "Select Gender", vbInformation
          Cancel = True
          Exit Sub
     End If

    If Nz(Me.Race, "") = "" Then
          MsgBox "Select Race", vbInformation
          Cancel = True
          Exit Sub
     End If

     If Nz(Me.PrimaryLanguage, "") = "" Then
          MsgBox "Enter PrimaryLanguage", vbInformation
          Cancel = True
          Exit Sub
     End If

     If Nz(Me.PrimaryDisability, "") = "" Then
          MsgBox "Enter PrimaryDisability ", vbInformation
          Cancel = True
          Exit Sub
     End If
End Sub

edited Jul 18 '18 at 08:45

answered Jul 18 '18 at 00:49

Santosh

12,175
4
41
72

Sorry, I have to slightly modify my post - Your code works for the two conditions But - What if I have 3 or 4 fields - Gender / Race / PrimaryLanguage? Thank you in an advance! – Hell-1931 Jul 18 '18 at 03:56
Yes, I'd like that after user entered info into Gender, all of those fields - Gender, Race, PrimaryLanguage, PrimaryDisability would became mandatory to have data in them. Even though they are empty before user started to fill them out – Hell-1931 Jul 18 '18 at 04:21
1

Just as an additional tip, I would build a single message string with all the missing info, and then display that in one message box at the end, and only need one cancel event based on the msg string not being empty. It will simplify the code and not be as annoying for the end user. You then wouldn't need the first check either. – Minty Jul 18 '18 at 08:25
@Santosh - Now !!! Works perfectly!!! I also added a similar code (slightly changed) to "After Update" event procedure, so that if user will accidentally remove the info, message will pop up Thank you so much !!! – Hell-1931 Jul 19 '18 at 05:15
@Minty - I'll try that as well :); just for my own "playing & testing" But for our users - it's great to have a reminder after each skipped field Otherwise, they'll keep entering & skipping and will have to go back to that record again, slower I think But I'll try – Hell-1931 Jul 19 '18 at 05:18

MsAccess form - empty records originally, but needed to be filled mandatory, once the data entry started

1 Answers1