Here's a generic code block I use for unbound controls and forms. In bound forms I will set, at the table level, the field required property to true and let Access do the heavy lifting.
+ This makes use of the Tag property of the control by setting the value to the text you want displayed in the message.
+ There is an assumption here that only data-entry controls such as textbox, combobox, listbox, option buttons/groups, will have this information set.
++ one glitch is with the option button... it's always in the null state if unbound when the form loads. Unfortunately, this appears as a non-selected option which can confuse the user depending on use. I typically use this to force confirmation of action... ie, must select the option button allow the delete/save and then there's code to verify the state of the option control - I'll tend to use the toggle button in triple state in these cases as it's clearer to the end user that some action need be taken (I set the caption here to for user feed back :-) )
+ I have a modified version that handles control tags with multiple entries as I often use this property for other tasks. This is a modification of the hidden label method described bellow.
[{required(0/1),{CtrlName4Msg/0},{other flags}]
Check
Left(1) if not zero then parse the tag, Once again the
Split() comes in handy here.
+ I typically use this method in a command button; however, I have had success using it in the form's before_update event. It really depends on the form's design.
+ I have also used a version where I use a hidden
label control (typically I place this in the form footer) that has a comma-delimited list for the caption [{ControlName},{UserText}...] that I pull into an array using the
Split() and step thru. I have however, abandoned this method and as I find it in my older databases have replaced it with on of my tag property versions as several people have deleted this control off of forms by accident when adding/changing the design while I was on vacation - arrrghhhh.
+ You will see the use of the tabindex property here, the focus will be set to the first control in the tab order that has a missing entry. SO, take a few moments with the tabstop tool in the form design to logically order your data entry controls! Often the default is correct; however, adding and subtracting controls during design can really muck this order.
+ I've avoided
NZ() here because I have had users go back and clear their entry; thus, the control is no-longer null.
- Private Sub Command11_Click()
-
Dim zCtrl As Control
-
Dim zMsg As String
-
Dim zTabIndex As Long
-
Dim zCtrlname As String
-
'
-
'seed the message:
-
zMsg = "The following controls are required entry: " & vbCrLf
-
'
-
'initialze the placeholder.
-
zTabIndex = Me.Controls.Count
-
For Each zCtrl In Me.Controls
-
With zCtrl
-
If Not "" & .Tag = "" Then
-
' Debug.Print .Name, .Tag, .TabIndex, .Value
-
If "" & zCtrl.Value = "" Then
-
zMsg = zMsg & .Tag & vbCrLf
-
If .TabIndex <= zTabIndex Then
-
zTabIndex = .TabIndex
-
zCtrlname = .Name
-
End If
-
End If
-
End If
-
End With
-
Next
-
Debug.Print "setfocusto: " & zCtrlname
-
If Not "" & zCtrlname = "" Then
-
Me.Controls(zCtrlname).SetFocus
-
MsgBox prompt:=zMsg, Buttons:=vbOKOnly, Title:="Missing data in required fields"
-
End If
-
zcleanup:
-
Exit Sub
-
zerrtrap:
-
MsgBox prompt:="Oh Bother, Contact the DBA and report the following information" & _
-
vbCrLf & "errS: " & Err.Source & _
-
vbCrLf & "errN: " & Err.Number & _
-
vbCrLf & "errD: " & Err.Description, _
-
Title:="Requred Dataentry Validation Failure"
-
Resume zcleanup
-
End Sub
I've used this version dozens of times with a 90% success rate... typically the error was due to my mistake (accidentally setting the tag in a label :) ); however, I have occasionally had weird things occur that went away after the database was closed and re-opened.
70
