Need help with Rich's Error Trapping Routine

[Premy]

Hi guys, I was looking for a way to trap err.number 3314 (when required field is null) before Jet generates its warning. I came across an old post from Rich (below), but I couldn't make it work as yet. In the calling form, under the Form_Error event I wrote the following:

Dim f As Form
Set f = Me
Call fnValidateForm(f)

Could anyone please tell me where my error lays here? I have several forms which have several text and/or combo boxes bound to required fields and I would want to have a generic code, like the one here to trap errors before Jet shows it's Error Message.

Thanks in advance
Regards
Jaime Premy - Belém-Brasil

******************Rich's Function********************
Public Function fnValidateForm(frmA As Form) As Boolean
Dim ctl As Control
Dim Msg, Style, Title, Response, MyString
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 "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If

If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
If ctl.Value = 0 Then
ctl.SetFocus
MsgBox "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If
End If
End If

Next
End Function

 

[Rich]

It doesn't belong in the Error trapping event, you Call the function in the BeforeUpdate Event of the form, such as
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not fnValidateForm(Me) Then
Cancel = True
End If
End Sub

 

[Premy]

Wow that was fast! thanks a lot, I'll check it out at once.

 

[gemma-the-husky]

probably not this, but you may be trying to set the focus to a ctl that cant take the focus

eg enabled and locked both false

 

[Premy]

Alas... it did not work!

Hi Rich I did as u suggested but to no avail: the Jet error msg ("Field MyField can not be null, since it's required property is set to true" -please note that I translated this msg from portuguese, since my Access 2003 is a brazilian version) keeps coming up, and your function is never executed.

I put together a tiny mdb (252KB) with just a few tables and a form, stripped versions of the ones in my mdb, which allow to see exactly what I'm talking about. I uploaded it to my esnips folder, so if you or anyone else would have the time to take a look, it can be downloaded by following this link:

http://www.esnips.com/r/hmfl/doc/529078ff-9309-4140-9871-6f86193b27fa/Test

On the form is a text primary key entry field "Nr. Processo" which will accept any entry. There is also a combo box with the first record selected by default
and a second combo box with no record selected by default. After u enter something in "NrProcesso", say 1111, and try to close the form without entering anything in the empty combo, the Jet msg fires up at once for the field in that combo "CodAdvCon". The other combo being filled with a default value does not cause any msg.

Of cours your code is in place in a module "Funções Diversas" and so is th calling code in the form's module.

Thank you for the trouble

Regards
Jaime Premy

 

[Premy]

Mea Culpa

Ok guys/Rich, Mea Culpa.

I overlooked the fact that I first needed to put the Tag property to "Required" for all the controls with required fields.

Sorry, and thanks again...

Regards,
Jaime