I can't speak for others, but IMO, Allen Browne and Dirk Golgar are a couple of Access gods (with small g). You should bookmark that site June7 gave you.
As far as error handling goes, I've been reading up on it and planned on adding some in today. Do you have a suggestion for this particular function?
In general, you have to try and adopt a somewhat 'computer illiterate' approach and apply it as stringently as the case requires. F'rinstance, I used to have the function of Beta tester, trying to break an application. Common sense would dictate that a number is expected in a control, but what happens if you enter 'dog'? Or what if the entered start date is after the end date? Stuff like that.
As for your particular situation, what comes to mind is 1) ensuring data that is going to be passed is of the right type and 2) ensuring controls that can have Null or empty strings as values are dealt with. For #1, I think validation in the property sheet is the simplest when you can use it. Or you could use a function that takes two arguments; the control and the expected data type, but why complicate things? For#2:
Code:
Function IsNullEmpty(Ctl as Control) As Boolean
IsNullEmpty = False
If IsNull(ctl) or ctl="" Then IsNullEmpty = True
End Function
Most likely you'd call this on a button click for unbound controls (and you're not duplicating this type of validation in the property sheet).
Most times, I'll iterate through a form's controls and look at the tag property. If it says (no quotes) "Reqd" (NOT Required) then I'll pass the control to the function to test it and if it returns True, add it to a list then msgbox the list. This way, I can list all the controls at once rather than make them fill in 5 missing ones, one at a time, with each click of the button they're trying to save the form with. Some will disagree with this method, implying it takes a long time to go through them all. My use of this has demonstrated that even on an old Dell with XP, a hundred controls takes a second or so. However, there may be valid reasons that haven't been pointed out to me yet, beyond personal preference or maybe having to redirect error handling, so at the end I included a way to skip the only two controls I know of that you'd want to bypass. This is air code, so there might be a boo-boo or two in it.
Code:
Private Sub cmdSave_Click()
Dim ctl As Control
Dim strlList As String, strMsg As String
strList=""
strMsg = "Please supply value(s) for: " & vbCrLf
On Error Resume Next 'need to redirect error handling for when ctl type has no tag property
For Each ctl In Me.Controls 'as long as this code resides on the form you're checking
If ctl.Tag = "Reqd" Then
If IsNullEmpty (ctl) = True Then
strList = "- " & ctl.Controls(0).Caption & vbCrLf 'get the ATTACHED label text. If not attached, cannot use as written
End If
End If
Next
If strList <> "" Then
msgbox (strMsg & strList)
Exit Sub 'don't move on to form save or whatever
End If
End Sub
To bypass labels and command buttons:
For Each ctl In Me.Controls
If ctl.ControlType = acLabel Or ctl.ControlType = acCommandButton Then GoTo bypassControl
bypassControl: goes just before the Next statement