Validating fields before record update

Talismanic

Registered User.
Local time
Today, 09:25
Joined
May 25, 2000
Messages
377
I have a form with 15 textbox controls, 10 of them are required fields. I have a button that I am using to update the table and start a new record. Now I want to check for null values in the 10 fields before the data goes to the table and then mark the "bad" records to the user by turning the label red.

I know I could do it with a series of Case Selects or If Thens like this:

If IsNull(Me.WeekEndHD) Then
Cancel = True
WeekEndHD.SetFocus
lblWeekEndHD.ForeColor = vbRed

ElseIf IsNull(Me.PayDate) Then
Cancel = True
PayDate.SetFocus
lblPayDate.ForeColor = vbRed

etc.....

The only problem I have doing it this way, besides all the extra typing is that it can only validate and mark one record at a time. How can do the whole form at once?
 
I believe you to be adept at Access, so I am going to put this at a high level ...

You would need to have unbound text boxes with the same name and an index #, ie tbEmp1, tbEmp2, tbEmp3 ..., tbEmp10 with the Tag property set to the corresponding invisible bound control control.(Also need text labels, tlEmp1 ...)

To check their values, run a loop at, say, the exit of the last control (you may need to set the form's property to tab through the same record) that will check for Null values:

For i = 1 to 10
if isnull(me.tbEmp(i)) then
me.tlEmp(i).forecolor = vbRed
HaveError = True
endif
next i

if not HaveError then UpdateRecord
:
:
Where UpdateRecord is a function using the Tag property to set those values.

Maybe this can at least get you on a different thread of thought.

GL


[This message has been edited by pdx_man (edited 07-24-2001).]
 
Ok I think I understand you, lets leave changing the color of the label or setting focus out of this for a second. Is there a way that I can go through the controls with something like this (looking for nulls):

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then

In this case I just want to grab the controls that are null and put the name in a pop up form marking them OK or Not OK depending on the values contained in them.
 
Public Function fnValidateForm(frmA As Form) As Boolean
Dim ctl As Control
fnValidateForm = True
For Each ctl In frmA.Controls
'value in the control is required
If InStr(1, ctl.Tag, "Required") > 0 Then
' no value entered or value is null
' or zero for numeric fields
If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
ctl.SetFocus
MsgBox "Value required"
fnValidateForm = False
Exit For
End If
If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
If ctl.Value = 0 Then
ctl.SetFocus
MsgBox "Value required"
fnValidateForm = False
Exit For
End If
End If
End If
Next
End Function
To use set the tag line for each control you require to AlphaRequired or NumberRequired.
HTH
 
Sure, Rich's code is good, too, because you can validate which text/combo boxes have this restriction. Otherwise, you might pick up a text/combo box where it might be OK to have a Null or zero length string.
 
Rich, How do I use this in my database. I have added the Function and now I need to start testing for it. What do I do next?
 
Open the property sheet for textbox select other tab, on the tag line simply type Required. Add for each field you need entries in. Try it and let us know how you get on. You may have to put NumberRequired for numeric fields.
HTH
 
Ok I added the Required to the tag but Access is still letting nulls or empty strings get through. I added the new function from the forms code window, is that where it should be?

Shouldn't I be calling the function from somewhere? If not when should the fields get their evaluations?
 
The Function needs to be in a module of it's own,add it using the module tab in the db window. the tag should be AlphaRequired or NumberRequired, but there needs to be at least one entry made somewhere on your form for the procedure to start working, if the form isn't activated no procedure will function.
HTH
 
Rich, what am I missing. I have the function in its own module, I have the tag properties marked, and I have entered data but it still doesnt work.

You mentioned that the tag should be AlphaRequired or NumberRequired. Doesn't this evaluate the tag property If InStr(1, ctl.Tag, "Required") > 0 Then? If so, shouldn't the "Required" be the string I need in the tag property?

What tells Access to check this particular forms fields? Is the presence of the tag property? If not, how do I make sure the form can use the function?

Thanks for the help!
 
Sorry Tal brain is disfunctional at the moment too many heatwaves:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not fnValidateForm(Me) Then
Cancel = True
End If
End Sub
 
I got it, finaly. Thanks!

Say I want to grab the name of the field and put it in the message box. How do I get that from the MsgBox (ctl.[THIS] & " Value required")

Edit<--- I figured this one out, ctl.Name. Thanks anyways.---> End Edit

[This message has been edited by Talismanic (edited 07-31-2001).]
 

Users who are viewing this thread

Back
Top Bottom