Field Required msg

[Guest]

Hi I am really new to Access. I would like to check if a field has a order
number entered. Right now I have:

Private Sub Order_Num_AfterUpdate()
If Len(Me.[Order_Num] & "") = 0 Then
MsgBox "Please Enter a Order Number"
Me.[Order_Num].SetFocus
Cancel = True
End If
End Sub

Private Sub Order_Num_BeforeUpdate(Cancel As Integer)
If Len(Me.[Order_Num] & "") = 0 Then
MsgBox "Please Enter a Order Number"
Me.[Order_Num].SetFocus
Cancel = True
End If
End Sub


Both in the Before and After Updates withing the field. I tried putting the
expressions in the Unload section of the form and this worked but not the way
I wanted. The field is a key (dups ok), required = yes. So this message is
for information only and I want the form to stay up with all the fields that
have already been entered.

Please Help and Thank You!

 

[BruceM]

Did you compile the code? If so, in the VBA editor click Tools > Options.
Click the Editor tab, and check the box for "Require Variable Declaration".
This would have prevented Cancel = True in an After Update event. An
example of an After Update event would be the After Update event of a combo
box from which a user selects marital status. If Married is selected, the
After Update event could send the user to a text box in which the spouse's
name is entered. Since the update has already happened, there is nothing to
cancel. Notice that Cancel as Integer does not appear on the first line.
On the other hand, a Before Update event can be cancelled because the update
has not yet happened. Cancel = True in a Before Update event prevents the
update from happening at all.
Your best option for form-level validation (as opposed to requiring at the
table level that the field contain a value) is probably the form's Before
Update event. Your Before Update code in the form's Before Update event
would probably do what you need. You could try using the control's Before
Update event for validation, but in order to do that you need to assure that
the user is in the control in the first place. You may be able to do that
by setting the focus to a particular control when you first go to the record
(placing the control first in the tab order should accomplish this), then
using that control's Exit event to set the focus to OrderNum, but you will
need to make sure that things such as attempting to navigate to another
record or close the database will force the validation.
By the way, if OrderNum is a sequential number, you can probably automate
it. Let me know if that is the situation.

 

[Maurice via AccessMonster.com]

What I don't get is if you've set the field to required why would you want to
check for a 0 value. If the user doesn't supply an ordernumber then Access
will prompt you telling that a ordernumber should be entered. Do you want to
customize the errormessage or do you want to do something else...

Maurice

Hi I am really new to Access. I would like to check if a field has a order
number entered. Right now I have:

Private Sub Order_Num_AfterUpdate()
If Len(Me.[Order_Num] & "") = 0 Then
MsgBox "Please Enter a Order Number"
Me.[Order_Num].SetFocus
Cancel = True
End If
End Sub

Private Sub Order_Num_BeforeUpdate(Cancel As Integer)
If Len(Me.[Order_Num] & "") = 0 Then
MsgBox "Please Enter a Order Number"
Me.[Order_Num].SetFocus
Cancel = True
End If
End Sub

Both in the Before and After Updates withing the field. I tried putting the
expressions in the Unload section of the form and this worked but not the way
I wanted. The field is a key (dups ok), required = yes. So this message is
for information only and I want the form to stay up with all the fields that
have already been entered.

Please Help and Thank You!