To expand a bit on what John wrote, the form's After Update event is
generally the place to perform data validation.
Here is something I copied from a posting by Albert Kallal. I didn't note
the date or newsgroup of the posting. I have used this code to good effect.
Note that you can customize the error message (the second item within the
quotes) to whatever extent you want. You can even have the full message
there, and leave out "is required" in the message box, if you like.
***************
I use the following "general" code routine to give custom messages for
fields that are not filled out.
The code below is a great way to verify fields that you want to be requited.
Another nice feature is that after the given message, the cursor (focus)
moves to the field in question.
The code is used as follows:
in the forms before update event..you go:
Cancel = MyVerify.
And, then the two following routines need be put into the forms module. You
can see how in the first example, you just put in the list of controls that
you want requited, and also the text "error" message to display. Note
carefully how the full string is enclosed in quotes.
This routine is called in the forms Load event:
Private Function MyVerify() As Boolean
Dim colFields As New Collection
MyVerify = False
colFields.Add "TourDate,Tour date"
colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"
MyVerify = vfields(colFields)
End Function
Private Function vfields(colFields As Collection) As Boolean
Dim strErrorText As String
Dim strControl As String
Dim i As Integer
vfields = False
For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then
MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i
End Function
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
***************************
Here is another one. I don't recall if I have tried this one, but I don't
think so:
*****************************
Here is a function I use routinely to check for required fields:
Public Function fm_CheckRequiredFields(f As Form, FieldList As Variant, _
Optional NormalColour As Long = vbWhite, _
Optional HighlightColour As Long = &H60FFFF) As Integer
Dim iFirstTab As Integer, sFirstTab As String
Dim c As Control, i As Integer, iBadFields As Integer
For i = LBound(FieldList) To UBound(FieldList)
Set c = f.Controls(FieldList(i))
If IsNull(c) Then
If iBadFields = 0 Or c.TabIndex < iFirstTab Then
iFirstTab = c.TabIndex
sFirstTab = c.Name
End If
iBadFields = iBadFields + 1
c.BackColor = HighlightColour
Else
c.BackColor = NormalColour
End If
Next
If iBadFields Then
f.Controls(sFirstTab).SetFocus
fm_CheckRequiredFields = iBadFields
End If
End Function
You pass it the current form object (Me) and an array of control names. It
checks each of the controls for null. If a control is OK (not null) it sets
its BackColor to NormalColour (default white), otherwise it sets it to
HighlightColour (default pale yellow). It sets focus to the first (if any)
of the invalid (null) controls according to the tab order of the form, and
then returns the number of invalid controls.
So, you can say something like this:
Select Case Me.Openargs
case 1 ' recommendation
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field1", "Field2", "Field3")
case 2 ' response
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field4", "Field5", "Field6")
... etc
End Select
If Cancel then
If msgbox( "Input is required in the highlighted fields. Click OK
to fix " _
& "this, or Cancel to undo all your changes and close the
form", _
vbOkCancel ) = vbCancel Then
Cancel = false
Me.Undo
DoCmd.Close acForm, Me.name
End If
End If
From Graham Mandeno, microsoft.public.access posting 12-May-2005
***********************
