Why does Access not display Validation Text?

[zakrocz]

Can somebody tell me why when entering a new record onto a form if a Required field does not meet it's validation rule Access does not display the Validation text but displays it's own 'user unfriendly' message.

The database is split.

 

[Junior1544]

My first question is: Where are the two values stored?? (the validation rule, and the validation text)

If they are both in the same place, I'd try doing the input directly on the open table. I'd do this to see if it'll give you the text to make sure it works at all...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[zakrocz]

The validation rule and text are stored in the table, this info is pulled over automatically by access in the controls properties of the form.

It does the same thing when entering data directly into the table (in the backend).

My validation text only appears when I enter something in the required field and then delete it and try to move off the field.

So at the moment if I click on "add new record" add any data into any field, which in turn triggers Access into writing the new record, then press close form access will display it's validation text not mine.

 

[Junior1544]

hmm, Interesting...

Put this into the Tag property of the control.

NotNull

And put this into a module:

Function frm_CheckNull(frm As Form, blnSetColor)

Dim ctl As Control
Dim strName As String
Dim varTag As Variant
Dim blnCheckNull As Boolean

On Error GoTo err_frm_CheckNull

blnCheckNull = True

'loop through all controls on form and check if the control has a
'not null tag. IF it does, then check if control value is blank
'if it is blank, then set background color to red and inform user.
'It also sets the background color to white and black on all controls
'so they are updated when user enters data.
'Also, pass result back to calling program.

For Each ctl In frm.Controls
If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
strName = ctl.name
varTag = ctl.Tag
If varTag Like "*NotNull*" Then
If blnSetColor = True Then
ctl.BackColor = vbWhite
ctl.ForeColor = vbBlack
End If

If IsNull(frm(strName)) Or Len(frm(strName)) = 0 Then
blnCheckNull = False
If blnSetColor = True Then
ctl.BackColor = vbRed
ctl.ForeColor = vbWhite
End If
End If
End If
End If

Next ctl

frm_CheckNull = blnCheckNull

exit_frm_CheckNull:
Exit Function
err_frm_CheckNull:
MsgBox "Calling Routine: basForms.frm_CheckNull" & vbCrLf & vbCrLf & "Error Code: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & CStr(Err.Description), vbCritical
Resume exit_frm_CheckNull

End Function
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

I forgot to tell you this to... In your save button VBA, put this...

If frm_CheckNull(Me, True) = False Then
MsgBox "Fields in red must be filled in.", vbExclamation
else
'Here put the code to save the information.
End If

Hope this helped...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[zakrocz]

Hi James

Thanks for the reply and the code. I haven't tried it out yet due to the company I've developed the database for getting shirty about payment, so I may not be using Access for much longer.

The fact that Access is not doing what it should is kind of bugging me. We shouldn't have to write code to do something that Access claims to do.

Thanks again for your input.

Best Regards

 

[Junior1544]

I am currently developing a web based database to replace our Access one... I'm almost done with it...

I'm using Visual Studio.net and it's wonderfull... I program in vb (very close to vba)...

I need to do more then i would have in access, But that also gives me control over so much more then I would have had in access...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[zakrocz]

I done an Epos project at college back in 99 using VB 5.0, and your right it's so much better, you have far more control and can spend your time writing routines rather than do battle with Access and it's quirks. I used Access to create my tables but used VB for the front end.

I was a lot happier solving my own coding/program flow mistakes than unlocking the secrets of Access.

Having previously used the old cumbersome and painful Quick Basic, VB was like a breath of fresh air.