Managing mandatory fields in data entry forms

[stevekos07]

Hi. I have some data entry forms which I need to apply some mandatory fields for and manage them so that it is both user friendly and prevents missing data or errors. I want to be able to highlight fields that are mandatory, as well as notifications when trying to exit a client without completing these fields.

Are there any good tutorials or guides that anyone knows of that I can refer to?

 

[MajP]

Here is a start. Click through the links.
http://www.databasedev.co.uk/confirm-record-update.html

 

[Solo712]

Hi. I have some data entry forms which I need to apply some mandatory fields for and manage them so that it is both user friendly and prevents missing data or errors. I want to be able to highlight fields that are mandatory, as well as notifications when trying to exit a client without completing these fields.

Are there any good tutorials or guides that anyone knows of that I can refer to?

Generally speaking, data checking for all fields is best done via the form's BeforeUpdate event. This event is triggered automatically when you attempt to step off the record or by a save command. See here for examples how to verify that entries on the form are valid.

As for highlighting mandatory fields, you may do so by setting the back color to the field to a special color on the Format tab of the Property sheet.

Best,
Jiri

 

[pbaldy]

I have used Conditional Formatting (on the ribbon) to highlight required fields. Using that means the highlight goes away once they've filled out each required control.

 

[Deleted member 147267]

Re the code samples at databasedev: only one field at a time will be evaluated. As soon as the message is dismissed, user goes back to form and enters data in the messaged control. Repeat one at a time for each one that's empty and required, plus the name of the control often is confusing txtFname??
Colored fields should help identify the required fields and minimize messages but won't enforce data entry.
I like to build a string list and incorporate the label caption of the controls, (however they have to be attached to the control in order to use the caption) and present all the missing ones at the same time. Would look something like

Dim strList As String
'do the loop on tag property /control type and if no data

strList = strList & ctl.Controls(0).Caption & vbCrLf
end of loop
If strList  <> "" Then
  strList = "Please enter values for " & strList
  Cancel = True
  Exit sub
End If

 

[MushroomKing]

I would use conditional formatting for your fields that are required. Try this:

- Right click on the field that's mandatory in Design View.
- Then click "conditional formatting".
- Add a new rule.
- Use: Field Value Is Equal To Null
- Set your background color to red. (So the field will be red if there is no value)

Then make another rule:
- Use: Field Value Is Not Equal To Null
- Set your background color to green.


Also you want to check the data.
I assume you have a "submit" button of some kind:

- Right click on your button and go to properties
- Go to "Event" tab and then to "On Click:
- Set it to "Event procedure"
- Hit the little dots icon to go to your VBA editor.

In here pase the following code for every field you have and edit to match yours:

Private Sub NAMEOFYOURBUTTON_Click()

If Me.YOURFIELDNAME.Value = Null Then
msgbox "Please, fill in all fields"
Exit sub
Else
'do nothing

End Sub

Hope this helps

 

[The_Doc_Man]

Other than color choices, I very often did the same thing but it was done at Form_Current time. In the form's OnCurrent event, I wrote a loop through all of the controls. The loop looked at each control to see if it was bound (.ControlSource not empty) and if so, whether the underlying field was marked as required (.Required=True). If so, I made the .Backcolor of that control = vbYellow but if not bound and required, I set it = vbWhite.

Then in the .LostFocus routine of each form I ran a test for the same thing (but this only had to test the control that just lost focus). So if you tabbed through the control without changing it, the color didn't change, but if you stopped at that control and entered something, it stayed yellow.

Finally I used the BeforeUpdate event to test whether all required fields were now filled in. If they were, well and good. But if not, the code would cancel the update and would pop up a message box saying "You have not provided a value for the highlighted elements on this form."