Color on Required Form Fields

  • Thread starter Thread starter A. Name
  • Start date Start date
A

A. Name

Hello All,

I want to build in some functionality with VBA, I'm new to Access VBA,
so please be patient.

Hopefully someone can help me implement it. I have a form with bound
fields on them. I want to make the form's background color red if it
is empty and when they enter something in there, it changes to white.
Also, I would like to add some error checking to the system to
ultimately make sure that these forms are completed before saving the
record.

Can someone point me in the right direction?

Thanks.
 
On changing the background color, it depends on how quickly you want it to
change.

If you have Access 2000 or newer, you could use Conditional Formatting. In
form design mode, right click the control (i.e. textbox) and choose
Conditional Formatting. Set the drop down box to Expression Is and set the
expression to Nz(NameOfControl,"")<>"". Set the background to white for this
and the default item above to a red background. This will update when the
user leaves the control and the control updates itself to the new value.

If you want it to update immediately when the user starts typing or clears
the control, then you'll need to use the control's Change event. Set the
Change event in the Properties sheet to [Event Procedure] and click the ...
button next to it. This will open the VBA code editor at that event with the
event title information already in place. In the event you would use code
similar to this:

If Nz(Me.NameOfControl.Text, "") = "" Then
Me.NameOfControl.BackColor = 16512
Else
Me.NameOfControl.BackColor = 16777215
End If

The easiest way to get these numbers for the colors is to go to the Format
tab in the control's Properties sheet and copy them from the Back Color
Option. You can select the color you want by clicking the ... button, the
copy and paste the resulting number.

For the error checking, in the Form's BeforeUpdate event, go though the
controls you want to check. If they are all ok, then do nothing. If you find
one that isn't, set Cancel = True, popup a message box telling the user what
the problem is, and set the focus to the control. The Cancel = True will
halt the update.

Example:
If Nz(Me.txtMyTextbox, 0) > 10 Or Nz(Me.txtMyTextbox, 0) < 1 Then
Msgbox "Please fill in the field xxx with an appropriate value!",
vbOkOnly + vbExclamation, "Validity Check"
Cancel = True
Me.txtMyTextbox.SetFocus
Exit Sub
End If

You would do this for each control you want to check. If there are errors on
several controls, the user may be prompted several times before they get
everything correct. Setting an Input Mask on the control will sometimes help
prevent errors also, especially if you need the answer in a particular
format.
 
Back
Top