error message in form from table restriction

[Heidi]

I am very new to programming, and am trying to figure out a way to do two
things. First: I have required all of the fields in a table to be filled in
- they cannot be null. In my form, when a user tries to skip entering a
field, I want the error message to say something like "This field cannot be
null, please enter correct info or the default value." I think the error
code is 3314, but I'm not sure where to put this error handler, or how to
envoke it for multiple fields. Second: When this error occurs I want the
form to recognize which field was left blank, and after the user hits an OK
button, I want to focus to be on that field that needs to be filled in. Any
suggestions?

 

[Tim Ferguson]

In my form, when a user tries to
skip entering a field, I want the error message to say something like
"This field cannot be null, please enter correct info or the default
value."

Second:
When this error occurs I want the form to recognize which field was
left blank, and after the user hits an OK button,

A number of things to say about this.

Firstly, how many of these fields could have default values attached?
They will make the whole database much more friendly to live with, both
for you as the developer and for the user. If not defaults, then it may
be possible to make an initial guess based on other fields the user has
filled in already. What happens is if the information for one of them is
genuinely unavailable?

Secondly, do you want the message to pop up as the user tabs away from
the empty field? Or do you want him or her to get to the end of the form
thinking it is finished and then make him or her go all the way back? For
the first, you can use the control_Exit event; for the latter use the
Form_BeforeUpdate event. Both of these have a Cancel parameter you can
set to True to prevent the user moving on.

Hope that helps


Tim F

 

[Heidi]

Thanks, Tim. I have set up a number of defaults - as much as possible, but
if the user deletes the default, I want the reminder that they have to put
something back in. I am guessing this all needs to be done in the form, not
the table... the restriction is currently in the table, so in order to put
the code in as you have suggested, I need to take the table restriction off
and put the:
If IsNull(me.control)=true
MsgBox "Whatever I want to say"
me.control.setfocus
End If

So, I'll need to repeat this language over and over with different control
names... there's no way to call up the control name and put it in the code?

Your info was helpful, thank you.

Heidi

 

[Tim Ferguson]

For the first, you can use the control_Exit event; for the

I am guessing this all needs
to be done in the form, not the table

Both: there are always plenty of ways to edit data in tables without
going near your forms. The table-level constraint will protect the data
at the engine level against all comers; the form-level validation will do
as much as possible to protect the user from seeing the ugly engine-level
error messages.

need to take the table restriction off

NO - never!

and put the:
If IsNull(me.control)=true
MsgBox "Whatever I want to say"
me.control.setfocus

No - that line is wrong. Use the events that Access gives you and use the
Cancel parameter to do it right. If you try to setfocus in an event that
causes the focus to move anyway, it'll do your setfocus then carry on to
wherever it was going to go to anyway. Read the help files. Anyway, it
would be Me.ActiveControl.SetFocus but that is quite obviously a
tautology (it means "make the active control active"...)

End If

So, I'll need to repeat this language over and over with different
control names... there's no way to call up the control name and put it
in the code?

No: create one function and call it from each of the events:

private function CheckEmpty(AControl as Control) as Integer
' remember this
Const emptyError As String = _
"Silly boy, you forgot to put something in"
Const debugMessage as String = _
"CheckEmpty called on "


' a bit of defensive programming
' can't remember the syntax of TypeOf without looking it up
' so check this next line very carefully
if not typeof(AControl) Is Textbox Then
' just carry on...
Debug.Print debugMessage & AControl.Name
CheckEmpty = False

elseif len(AControl.Value)>0 Then
' not empty, okay
CheckEmpty = False

else
' it's empty
MsgBox emtpyError
CheckEmpty = True

End if

All you have to do is to stick the following in each of the events you
want to monitor

private sub MyTextBox_Exit(Cancel as Integer)
Cancel = CheckEmpty(MyTextBox)
end sub

but obviously you'll need to put in the correct control name each time.
For the Exit event, the control is still active, so you could do a

Cancel = CheckEmpty(Me.ActiveControl)

which could at least be done with a copy-paste or a smart text editor.

In the Form_BeforeUpdate event, you can check the whole lot with
something like


for each ctl in me.controls
if typeof(ctl) is textbox then
if me.recordset.fields(ctl.controlsource).Required then
if checkempty(ctl)=true then
' it's empty; jump to it
Cancel = True
ctl.setfocus
exit for
end if
end if
end if
next ' ctl

This is very much not tested, but it should give you an idea of where to
go. Hope it helps


Tim F

 

[Heidi]

Thanks for all of your help, Tim. I'll try the coding you suggested. I
really appreciate it.

Heidi