Error trap

[murali]

Hi There,
I am facing a small difficulty when i am dealing with access database from an access form.
I have 'Field' in a table whose required pr. is true.
A table is linked to the form as source
when i enter nothing in that required field field and try to save, it is throwing an error No.3314 from the database when captured in form_error event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3314 Then
Response = 0 'i dont want the message displayed by DB
MsgBox "Please Enter a valid value in '?' " 'custom message

End Sub

Here I can display custom message by supreesing the message
thrown by database.
Problem is I cannot catch that source field in DB which causing error as I want to set focus to that field.
Please help me to find that source.
Thanks
-Murali

 

[cogent1]

I got your e mail

Murali

I'd like to help, but I don't quite understand what you want.

The error you refer to, appears when there is a null entry in a field that has the "Required "property set to True. In other words, you must key some entry into this field before the table row can be saved. So you should look through your tables to check fields where you have this condition set.

This property carries through to any form that is based on the table or a query that contains the field. If you don't put all the fields from the table/query on the form, you will end up with a null entry if one of the omitted fields is set to "Required Entry"

If one or more Required fields are left blank, your message will be generated when you step out of the record. if you want to make immediately sure that your user doesn't leave a required field blank, you have to put error trapping code in the On_Exit event of the field, that issues a message and steps the cursor back to the field whenever the field is left null.

Is this what you require. Post back with more details if not...

 

[murali]

Trap error-Reply

Hi Cogent,
Thanks for the reply.

1. All field in the table are used in form.

2. We cannot trap by writing a code in VBA either in on_exit or lost_focus because database error fires before our vba events.

database display an error message what a programmer can understand.
but user always confuse with the description what it gives.
so i want to trap that database desc. and source, modify the content and display.
In my code i posted i can modify the description but unable to
get the source(Filed name).

So in the help it is telling we can trap those dao.errors by
creating 'ERROR' object.
Dim er as ERROR
if I say "er.description" it gives an error saying
"Object variable or withblock variable not set"

Please try out.
Thanks
-Murali

 

[Rich]

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

 

[murali]

Hi

Rich
Thanks for a module level code.
Problem is when i have to call this.

If a control is bound to a table in database, database fires the
event as soon as u try to leave the field blank.(once u started typing).

If u have a subform with a database field required and if it is combo which fills/sorts automatically by a value in main form
u can't toggle between main form and subform as that sub form field keeps on firing the validation from database until u choose a value(which i am going to change).

Anyway thanks.
-Murali

 

[Rich]

Not sure I understand your response, the code I gave you when used with the tag property will fire before any event and set focus to the control, which is what I assumed you where looking for.

 

[cogent1]

Murali

If you are having difficulty in the timing of this error message, you can still use Rich's code, or something very like it, in your error-handling procedure. Suppress the Jet error, step through the controls as Rich suggests and use ctl.name if you want to refer to the field by name in your custom error message. I see no problem there.

The alternative of course is to set "required" fields to a default empty string or 0 and have code to trap these instead of nulls.

 

[murali]

Hi Rich & Cogent

Thanks for ur advice.
I think it is working fine when i call this proc
in form_error event.First i forgot to set Tag for controls.

The loop has to check all the controls evrytime(including non validate).It is ok. Not my problem.

Thanks for ur timely help.

Reagards
Murali

Little about me
------------------
I am from India
I am a civil engineer working for a consulting compqany in UAE.
As I am not a full time programmer and I am just learning now.
Hope i got a good support of u.
Thanks