Link to home
Create AccountLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Best way to handle required fields on a form

I know I've been here before but I'm looking for advice on how to best handle required fields on a form. For example, if I have a form bound to a table and that table has 5 fields and I've set 3 of them as required...

When I fill out the form and if I've left any of the required fields blank and click the [Close Form] command button, it just closes without a warning and the record is not saved to the table.

There has to be a good way to deal with this.  Maybe some kind of code in the onclick event of the command button to alert the user to the fact that required fields were not entered and then set the focus on the first required field that is not filled in.

????
Avatar of Eric Sherman
Eric Sherman
Flag of United States of America image

Here is a simple basic method ...

Use the BeforeUpdate Event of your Form ...

If NZ(me.txtBox1, "") = "" OR NZ(me.txtBox2, "") = "" OR NZ(me.txtBox3, "") = "" Then
     Msgbox "Text1, Text2 and Text3 are required fields!!!"
     Cancel = True
End IF

ET
If you've set the field for Required, and set other properties correctly, then you should not be allowed to save that record. Access should complain and force you to either make corrections, or to undo your modifications and not save.

That said:

You can use the techniques suggested by etsherman, or you can use the Tag property method, or you can use the CONTROL's BeforeUpdate event.

The Tag property works like this: For each control which is "required", add the same value to the Tag property (perhaps add a "req" to the Tag property).

When the user tries to save the form, you then loop through the controls and determine if all those "tagged" controls have values:

Dim s As String
Dim ctl As Control

For Each ctl in Me.Controls
  If ctl.Tag = "req" Then
    If Nz(ctl.Value,"") = "" Then
      s = s & vbcrlf & ctl.Name " & must be filled in
    End If
  End If
Next

If Len(s) > 0 Then
  '/ one or more controls were not filled in
  Msgbox "Invalid or Missing Data:" & vbcrlf & s
Else
  '/ save the data here
End If

You can also use the Control's Before Update event. Same concept as the Form's Beforeupdate event, except it would require the user to enter the data when they try to move out of the control.
No points.

I use both methods described above, although I prefer the method described by etsherman, using the Form_BeforeUpdate event.  Rather than putting the tests all in one statement,  I usually do something like:
If NZ(me.txtBox1, "") = ""  Then
     Msgbox "Enter selection in Text1!!!"
     me.txtBox1.setfocus
     Cancel = True
Elseif NZ(me.txtBox2, "") = ""
     Msgbox "Enter selection in Text2!!!"
     me.txtBox2.setfocus
     Cancel = True
End IF
If Cancel then Exit Sub

Open in new window

I NEVER use the Control's BeforeUpdate event because with a Windows application, not all users want to enter info in the predefined tab order, and want to use the mouse to move between fields.  If you use the the controls beforeupdate event, you prevent them from moving between fields as they wish.
Hi LSM, the control's before update wouldn't work, because it will only fire if they remove the value, not if they just leave it null.  Anyway, the whole-form methods are better anyway, as they give the user more flexibility.

Cheers,
Armen Stein
Avatar of SteveL13

ASKER

I'm trying the tag method but when I copy/paste this in to the beforeupdate event of the form I get a red line of code on the:

s = s & vbcrlf & ctl.Name " & must be filled in


I copied this exactly:

Dim s As String
Dim ctl As Control

For Each ctl in Me.Controls
  If ctl.Tag = "req" Then
    If Nz(ctl.Value,"") = "" Then
      s = s & vbcrlf & ctl.Name " & must be filled in
    End If
  End If
Next

If Len(s) > 0 Then
  '/ one or more controls were not filled in
  Msgbox "Invalid or Missing Data:" & vbcrlf & s
Else
  '/ save the data here
End If
Now I'm trying this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.txtLastName, "") = "" Then
     MsgBox "Enter selection in Last Name!!!"
     Me.txtLastName.SetFocus
     Cancel = True
Else
If Nz(Me.txtFirstName, "") = "" Then
     MsgBox "Enter selection in First Name!!!"
     Me.txtFirstName.SetFocus
     Cancel = True
Else
If Nz(Me.txtEmpPassword, "") = "" Then
     MsgBox "Enter selection in Password!!!"
     Me.txtEmpPassword.SetFocus
     Cancel = True
End If


'If Cancel Then Exit Sub
End If
End If
End Sub

But what happens is the form closes without staying open and focused on the password field (which is the one I left blank intentionally) when I click on the [Close Form] command button I have on the form.

???
Anyway, the whole-form methods are better anyway, as they give the user more flexibility.
I agree fully, but many people want the ability to validate data as the user enters it - so they can have a NULL value, but if the user enters anything, they must enter a valid value.

form closes without staying open and focused on the password field (which is the one I left blank intentionally)
Try moving Cancel = True above the SetFocus statement.
I removed the Cancel = True and the form still closes after I get the message to enter the password when I click the close button.
I removed the Cancel = True
You should not REMOVE the Cancel = True line ... I suggested you MOVE the line.
Since you are using the OnClick Event of your Close Command Button and not a Save Button on the Form ....  Try the code below.  Replace CmdClose and YourFormName with your actual Control Names.

Private Sub CmdClose_Click()

If Nz(Me.txtLastName, "") = "" Then
     MsgBox "Enter selection in Last Name!!!"
     Me.txtLastName.SetFocus
     Exit Sub
Else
If Nz(Me.txtFirstName, "") = "" Then
     MsgBox "Enter selection in First Name!!!"
     Me.txtFirstName.SetFocus
     Exit Sub
Else
If Nz(Me.txtEmpPassword, "") = "" Then
     MsgBox "Enter selection in Password!!!"
     Me.txtEmpPassword.SetFocus
     Exit Sub
End If
End If
End If

DoCmd.Close acForm, "YourFormName", acSaveYes

End Sub

ET
To etsherman:

Getting close.  Now the only problem is that if I start to enter a new record and undo it with the [Undo Record] command button on the form, it tries to make me enter a last name.

--Steve
Sounds like you have some other code that is also running.  Can you post all of the code for events behind your form???

When I test using a form with a Undo command button I do not experience the problem mentioned in your latest post.  See attached picture.

User generated image
ET
Close Form event:

Private Sub cmdCloseForm_Click()

If Nz(Me.txtLastName, "") = "" Then
     MsgBox "Enter selection in Last Name!!!"
     Me.txtLastName.SetFocus
     Exit Sub
Else
If Nz(Me.txtFirstName, "") = "" Then
     MsgBox "Enter selection in First Name!!!"
     Me.txtFirstName.SetFocus
     Exit Sub
Else
If Nz(Me.txtEmpPassword, "") = "" Then
     MsgBox "Enter selection in Password!!!"
     Me.txtEmpPassword.SetFocus
     Exit Sub
End If
End If
End If

DoCmd.Close acForm, "frmEmployees", acSaveYes

End Sub

*****

Add Record Event:

Private Sub cmdAddRecord_Click()
On Error GoTo Err_cmdAddRecord_Click

    On Error GoTo Err_cmdAddRecord_Click


If Nz(Me.txtLastName, "") = "" Then
     MsgBox "Enter selection in Last Name!!!"
     Me.txtLastName.SetFocus
     Exit Sub
Else
If Nz(Me.txtFirstName, "") = "" Then
     MsgBox "Enter selection in First Name!!!"
     Me.txtFirstName.SetFocus
     Exit Sub
Else
If Nz(Me.txtEmpPassword, "") = "" Then
     MsgBox "Enter selection in Password!!!"
     Me.txtEmpPassword.SetFocus
     Exit Sub
End If
End If
End If

    DoCmd.GoToRecord , , acNewRec

Exit_cmdAddRecord_Click:
    Exit Sub

Err_cmdAddRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddRecord_Click
   
End Sub

******

Save Record Event:

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

    DoCmd.RunCommand acCmdSaveRecord

Exit_cmdSaveRecord_Click:
    Exit Sub

Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click
   
End Sub

******

OnCurrent Event:

Private Sub Form_Current()

    Me.txtEmpName = Me.txtFirstName & " " & Me.txtLastName

End Sub

******

OnOpen Event:

Private Sub Form_Open(Cancel As Integer)

    Me.cmdCloseForm.SetFocus

End Sub

Field AfterUpdateEvent:

Private Sub txtFirstName_AfterUpdate()

    Me.txtEmpName = Me.txtFirstName & " " & Me.txtLastName

End Sub
Try this ...


*****Close Form event:

Private Sub cmdCloseForm_Click()

If Nz(Me.txtLastName, "") = "" Then
     MsgBox "Enter selection in Last Name!!!"
     Me.txtLastName.SetFocus
     Exit Sub
Else
If Nz(Me.txtFirstName, "") = "" Then
     MsgBox "Enter selection in First Name!!!"
     Me.txtFirstName.SetFocus
     Exit Sub
Else
If Nz(Me.txtEmpPassword, "") = "" Then
     MsgBox "Enter selection in Password!!!"
     Me.txtEmpPassword.SetFocus
     Exit Sub
End If
End If
End If

DoCmd.Close acForm, "frmEmployees", acSaveYes

End Sub


*****Add Record Event:

Private Sub cmdAddRecord_Click()
    DoCmd.GoToRecord , , acNewRec
End Sub



******Save Record Event:

Private Sub cmdSaveRecord_Click()
    Me.Dirty = False    
End Sub


******OnCurrent Event:

Private Sub Form_Current()
    Me.txtEmpName = Nz(Me.txtFirstName) & " " & Nz(Me.txtLastName)
End Sub

******OnOpen Event:

Private Sub Form_Open(Cancel As Integer)
    Me.cmdCloseForm.SetFocus
End Sub



******Field AfterUpdateEvent:

Private Sub txtFirstName_AfterUpdate()
    Me.txtEmpName = Nz(Me.txtFirstName) & " " & Nz(Me.txtLastName)
End Sub


ET
Still can't close the form after undo event.
Steve,

I usually include two buttons on my forms, one to Cancel and the other to Save.  Both of these buttons include the Close command, but the Cancel button just does a

Docmd.Undo
docmd.close acform, me.name

While the Save button tests for the required values and then saves the record.  Occassionally, I will feel the need to include a close button, and when I do, I check to see whether the form is dirty.  If so, I present my user with an message box that asks if they want to save the dirty record before closing.  If so, I call the cmd_Save_Click event.  If not, I call the cmd_Cancel_Click event.

I believe LSM mentioned similar to this somewhere much earlier in this thread.
<<<<<Still can't close the form after undo event.>>>>>

Load up a sample of your db.  Will have to look at it.  It's probably something simple that we are missing.

ET
Here is the sample.  Try to start a new record entry.  But don't finish it.  Click [Undo Record].  Then click [Close Form].

You can't get the form to close even though you undid the record.
Sample.accdb
Sorry.  Use this sample d/b.  The other one had a linked table.
Sample.accdb
SOLUTION
Avatar of Eric Sherman
Eric Sherman
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This didn't work correctly.  I was able to close the form without having a required filed filled in.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi Steve ... Based on your form's setup .... The Events below will accomplish what you are trying to do.  Also, in your table, the Required fields (Last Name, First Name and EmpPassword) should not be set to Allow Zero Length Strings.

Please test the attached fixed sample db.

Private Sub cmdCloseForm_Click()
    If Nz(Me.txtLastName, "") = "" Then
         MsgBox "Enter selection in Last Name!!!"
         Me.txtLastName.SetFocus
         Exit Sub
    Else
    If Nz(Me.txtFirstName, "") = "" Then
         MsgBox "Enter selection in First Name!!!"
         Me.txtFirstName.SetFocus
         Exit Sub
    Else
    If Nz(Me.txtEmpPassword, "") = "" Then
         MsgBox "Enter selection in Password!!!"
         Me.txtEmpPassword.SetFocus
         Exit Sub
    End If
    End If
    End If

DoCmd.Close acForm, "frmEmployees", acSaveYes

End Sub

Private Sub cmdAddRecord_Click()

    DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub cmdSaveRecord_Click()
    Me.Dirty = False
End Sub

Private Sub Form_Current()
    Me.txtEmpName = Nz(Me.txtFirstName) & " " & Nz(Me.txtLastName)
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.txtFirstName.SetFocus
End Sub
Private Sub cmdUndoRecord_Click()
    DoCmd.RunCommand acCmdUndo
    DoCmd.GoToRecord , , acPrevious
End Sub

Private Sub txtLastName_AfterUpdate()

    Me.txtEmpName = Nz(Me.txtFirstName) & " " & Nz(Me.txtLastName)

End Sub


ET
Sample.accdb