code to validate data in Access withOUT using messageboxes

rhenerlau 1 122 Views

This code uses field level validation to check for data in fields in an Access form, and instead of using message boxes (as the "Validation Rule" and "Validation Text" properties of a field (in the table design window) do, it turns the associated label and control (textbox/listbox/combobox) background color to RED, and then freezes the cursor on the field that requires attention

Private Sub txtZipCode_Exit(Cancel As Integer)
Trim (txtZipCode)
If IsNull(txtZipCode) Then
Cancel = 1
Else: Cancel = 0
End If
If Cancel = 1 Then
txtZipCode.BackColor = vbRed
lblZIPCode.ForeColor = vbRed
txtZipCode.SetFocus
Else
txtZipCode.BackColor = vbWhite
lblZIPCode.ForeColor = vbBlack
txtEmailAddress.SetFocus
End If
End Sub
======================================================
Use the "Exit" routine - you need the "Cancel" property of that routine to freeze the cursor on the field.  Trim the field - that removes leading and trailing spaces (" " is not the same as "") - then you check for a null value...If the value is null, set the cancel (as integer) property to one...Otherwise set it to zero....If the cancel property is one, then (the tab was canceled) set the label and field control color properties (forecolor is the text color of the label, backcolor is the background color of the field) to red, and freeze the cursor...otherwise, set the background color (backcolor) of the field control to white, and the foreground color (forecolor) of the label to black...and allow a tab to the next field
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview