You will need to use several events to achieve what you want, so it makes
sense to write a function that highlights the boxes, and call it from all
the events where you need it.
A text box named "Name" is going to give you problems: since the form has a
Name property, Access is going to get confused and think you are talking
about the name of the form instead of the value in the control named Name. I
suggest you rename this field, and change the Name and ControlSource of the
text box before you start. The example below assumes the text box is called
ClientName. It assumes the 2nd text box is called ClientDescrip.
The example shows what to put into the module of your form. As always, the
module begins with the Option statements. With them are the definitions of
the colors to use for the highlighted text box or normal text box. Change
those colors to suit. (Note that the code uses the Border of the text boxes
instead of needing extra rectangles.)
Next is the function that does the highlighting. It accepts an argument
telling it whether to check the OldValue of the text boxes, since that's
what you need to do in the form's Undo event to reset them. In all other
events, this argument will be False.
The function then sets a yes/no variable named bNull to True if any of the
text boxes has the Null value (or if the OldValue is null for the undo
case.) It then tests if the BorderColor is set correctly, and if not changes
the BorderColor to the appropriate value.
This function is called by the AfterUpdate event of all 4 text boxes. It is
also called by the Current event of the form so they are set correctly when
you move record, and by the Undo event of the form so it is reset when you
undo a record.
Finally, the BeforeUpdate event of the form tests for nulls to prevent an
unacceptable record being saved. This is necessary since you will have to
remove the Required field from the fields in your table, so as to avoid the
error message that is triggered when you clear a text box bound to a
Required field. (If you want engine-level validation, you could enter a
Validation Rule for the table instead.)
Okay, here's the kind of thing you would end up with in the module of your
form:
*************************code begins***********************
Option Compare Database
Option Explicit
Const mlngcHighlight = vbRed
Const mlngcNormalBorder = vbBlack
Private Function DoHighlight(bUseOldValue As Boolean)
Dim bNull As Boolean
If bUseOldValue Then
bNull = (IsNull(Me.ClientName.OldValue) Or _
IsNull(Me.ClientDescrip.OldValue) Or _
IsNull(Me.StartDate.OldValue) Or _
IsNull(Me.EndDate.OldValue))
Else
bNull = (IsNull(Me.ClientName) Or _
IsNull(Me.ClientDescrip) Or _
IsNull(Me.StartDate) Or _
IsNull(Me.EndDate))
End If
If bNull Then
If Me.ClientName.BorderColor <> mlngcHighlight Then
Me.ClientName.BorderColor = mlngcHighlight
Me.ClientDescrip.BorderColor = mlngcHighlight
Me.StartDate.BorderColor = mlngcHighlight
Me.EndDate.BorderColor = mlngcHighlight
End If
Else
If Me.ClientName.BorderColor <> mlngcNormalBorder Then
Me.ClientName.BorderColor = mlngcNormalBorder
Me.ClientDescrip.BorderColor = mlngcNormalBorder
Me.StartDate.BorderColor = mlngcNormalBorder
Me.EndDate.BorderColor = mlngcNormalBorder
End If
End If
End Function
Private Sub ClientDescrip_AfterUpdate()
Call DoHighlight(False)
End Sub
Private Sub ClientName_AfterUpdate()
Call DoHighlight(False)
End Sub
Private Sub StartDate_AfterUpdate()
Call DoHighlight(False)
End Sub
Private Sub EndDate_AfterUpdate()
Call DoHighlight(False)
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.ClientName) Then
Cancel = True
strMsg = strMsg & "Name required." & vbCrLf
End If
If IsNull(Me.ClientDescrip) Then
Cancel = True
strMsg = strMsg & "Description required." & vbCrLf
End If
If IsNull(Me.StartDate) Then
Cancel = True
strMsg = strMsg & "Start Date required." & vbCrLf
End If
If IsNull(Me.EndDate) Then
Cancel = True
strMsg = strMsg & "End Date required." & vbCrLf
End If
If Cancel Then
strMsg = strMsg & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid data"
End If
End Sub
Private Sub Form_Current()
Call DoHighlight(False)
End Sub
Private Sub Form_Undo(Cancel As Integer)
Call DoHighlight(False)
End Sub
**************************code ends************************
