BeforeUpdate event: Trouble with Cancel = True

[Clif McIrvin]

My environment is XP Pro SP2, Access 2003 SP3 w/Hotfix using DAO 3.6.

I'm having trouble with a validation routine in a control's before
update event.

The control in question is bound to an indexed text field:
Required = Yes
Zero length = No
Allow Duplicates = No.

The validation I'm attempting could be left to Access, but I'd prefer my
own error popups to the "Too much information for a user" error text
that Access supplies.

When I detect a violation, I display a MsgBox, set Cancel = True and
exit the function. To my surprise, Access seems to be ignoring the
Cancel = True and displays its own error message.

Part two of my question: I would like to reset the control in question
to empty as a visual cue, but Access insists that I'm violating data
integrity rules: it returns error 2115,
"The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing Microsoft Office Access from
saving the data in the field."

Any suggestions?

 

[Gina Whipp]

Clif,

One suggestion... Could we see the event? You have told us all about it
except what it is...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Dirk Goldgar]

My environment is XP Pro SP2, Access 2003 SP3 w/Hotfix using DAO 3.6.

I'm having trouble with a validation routine in a control's before update
event.

The control in question is bound to an indexed text field:
Required = Yes
Zero length = No
Allow Duplicates = No.

The validation I'm attempting could be left to Access, but I'd prefer my
own error popups to the "Too much information for a user" error text that
Access supplies.

When I detect a violation, I display a MsgBox, set Cancel = True and exit
the function. To my surprise, Access seems to be ignoring the Cancel =
True and displays its own error message.

Part two of my question: I would like to reset the control in question to
empty as a visual cue, but Access insists that I'm violating data
integrity rules: it returns error 2115,
"The macro or function set to the BeforeUpdate or ValidationRule property
for this field is preventing Microsoft Office Access from saving the data
in the field."

Any suggestions?

It's har dot say without seeing your code. Have you tried something like
this?

'----- start of example code -----
Private Sub YourControl_BeforeUpdate(Cancel As Integer)

If YourControl <> SomeValidValue Then
Cancel = True
MsgBox "Not a valid value!"
Me.YourControl.Undo
End If

End Sub
'----- end of example code -----

 

[Clif McIrvin]

Clif,

One suggestion... Could we see the event? You have told us all about
it except what it is...

Ok ... the code follows below

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

My environment is XP Pro SP2, Access 2003 SP3 w/Hotfix using DAO 3.6.

I'm having trouble with a validation routine in a control's before
update event.

The control in question is bound to an indexed text field:
Required = Yes
Zero length = No
Allow Duplicates = No.

The validation I'm attempting could be left to Access, but I'd prefer
my own error popups to the "Too much information for a user" error
text that Access supplies.

When I detect a violation, I display a MsgBox, set Cancel = True and
exit the function. To my surprise, Access seems to be ignoring the
Cancel = True and displays its own error message.

Part two of my question: I would like to reset the control in
question to empty as a visual cue, but Access insists that I'm
violating data integrity rules: it returns error 2115,
"The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing Microsoft Office Access from
saving the data in the field."

Any suggestions?

Private Sub Contractor_BeforeUpdate(Cancel As Integer)
' Prevent attempt to add duplicate record
' .ListIndex returns -1 for unique value

Dim sName As Variant
sName = "Contractor Name"

If Me.NewRecord Then 'Adding new record?
With Me.Contractor 'yes, check for duplicate
If .ListIndex >= 0 Then 'Requested value already exist?
sName = .ItemData(.ListIndex) 'yes, get existing name
MsgBox sName & " already exists." & vbCrLf & _
"Duplicate Values cannot be added.", vbInformation
Cancel = True
rem -- i've tried both
.Undo
rem -- and
.Value = Null
rem -- here
Else
If IsNull(.Value) Then 'trap null value
MsgBox sName & " cannot be empty.", vbInformation
Cancel = True
dp .ListIndex, .ListCount, .Text
dp Me.Contractor
End If
End If
End With
End If

End Sub

 

[Clif McIrvin]

Thanks, Dirk -- I thought so

My code is in my reply to Gina.

 

[Gina Whipp]

Clif,

Okay, let me see if I understand this... the Contractor, which you are
trying to prevent duplicates this is not a combo box. (Not sure why not
because then you could use the Not_In_List event. But moving along, I
attempt to add but it's already in the list which suggest it is a combo box,
so why doesn't it just show thereby eliminating the need for the Cancel =
True. And then setting the box to Null which you do not allow? Could be me
but I need more information as to what you are doing...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Clif,

One suggestion... Could we see the event? You have told us all about it
except what it is...

Ok ... the code follows below

 

[John W. Vinson]

Private Sub Contractor_BeforeUpdate(Cancel As Integer)
' Prevent attempt to add duplicate record
' .ListIndex returns -1 for unique value

The record isn't saved to disk in the *control's* update event, but in the
*form's*. Might it be easier to detect the error there?

 

[Clif McIrvin]

Gina, thanks for responding.

Perhaps I'm coming at this from the wrong direction. I set out to state
the error I was getting, but your reply makes me realize that I never
said what I was attempting to accomplish. [Sheesh --- as often as I've
seen someone say "You've told us *how* you're trying to do something,
but not *what* you're trying to do.... you'd think I'd have started
there!]

Okay: here's the setup:

I have three tables:

Contractors
Jobs
Production

Production is related many-to-one to Jobs [JobID - Autonumber]
Jobs is related many-to-one to Contractors [ContractorID - Autonumber]
Referential integrity is enforced.

Due to a variety of work environment factors, frequently either the
required Job or Contractor has not been previously entered prior to the
data entry clerk attempting to enter daily production data.

I'm attempting to create a mechanism so when a job doesn't exist when
entering production data, the user can easily call up a form to create
the job. Since the data entry clerk will frequently have minimal
training, I'd like to make this "Add a new job / Add a new Contractor"
process as bullet-proof as possible. [Thanks for your reference to the
"not in list" event -- I only do this development stuff occasionally,
and it's easy to forget things between attempts <g>. I may use it to
trigger the "Add new Job" process!]

I want to prevent "accidental" duplicate and/or empty entries; but I
fear that the default Access error messages in such cases will be
confusing to the users.

I think I know how to trigger a pop up form to add a new Job; I *don't*
know how to replace the Access error messages with my own.

[The event code I posted was from the form I'm creating which will
become the pop up "Add new Job" form.]

It seems to me that the mechanism for "Add new Job" and "Add new
Contractor" is nearly identical ... so when I find the solution to one,
I'll have it for both.

Does this clarify what I'm asking for?

Thanks again for helping me learn!

[Oh -- yes, Contractor is a ComboBox on the Jobs form.

As to not using the Not_In_List event -- I'd simply forgotten all about
that whole set of tools in a Combo Box. They sure make life easier!

Why setting the box to Null? My thought was to 1) notify the user that
they had attempted an invalid addition and 2) present them with an empty
box for their next attempt. [I'm now planning to experiment with the
Selection Start and Selection Length properties so that after the msgbox
warning the user is presented with a fully selected value.]

Why use the control's before update instead of the form's? Simply to
notify the user immediately, before they have filled out the remaining
fields.

As to my original question: It appears to me that the Access (Jet?)
validation routines occur *after* the field's before update event, but
do not test "Cancel" --- after posting I happened across the form's "On
Error" event documentation ... that looks promising for what I wish to
accomplish.]

Again, thanks for the assistance; I believe I've learned far more in
this forum than from the included documentation!

--
Clif

Clif,

Okay, let me see if I understand this... the Contractor, which you are
trying to prevent duplicates this is not a combo box. (Not sure why
not because then you could use the Not_In_List event. But moving
along, I attempt to add but it's already in the list which suggest it
is a combo box, so why doesn't it just show thereby eliminating the
need for the Cancel = True. And then setting the box to Null which
you do not allow? Could be me but I need more information as to what
you are doing...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Clif,

Yes, it does but right now a little busy BUT I will get back to you... Have
a conference call with a Client. I will tell you I would generate the Job
ID and let Autonumber stay in the background as my 'secret' link. I would
also do the same for Contractor ID. That eliminates the need for the one of
the messages as I'm just checking to see if it's in the list and not trying
to prevent duplicates. I would then trap the Not_In_list error message
either in No-In_List or Form_On_Error depending on where and what the error
number is.

I hope that's enough to get you started... I'll be back!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina, thanks for responding.

Perhaps I'm coming at this from the wrong direction. I set out to state
the error I was getting, but your reply makes me realize that I never
said what I was attempting to accomplish. [Sheesh --- as often as I've
seen someone say "You've told us *how* you're trying to do something,
but not *what* you're trying to do.... you'd think I'd have started
there!]

Okay: here's the setup:

I have three tables:

Contractors
Jobs
Production

Production is related many-to-one to Jobs [JobID - Autonumber]
Jobs is related many-to-one to Contractors [ContractorID - Autonumber]
Referential integrity is enforced.

Due to a variety of work environment factors, frequently either the
required Job or Contractor has not been previously entered prior to the
data entry clerk attempting to enter daily production data.

I'm attempting to create a mechanism so when a job doesn't exist when
entering production data, the user can easily call up a form to create
the job. Since the data entry clerk will frequently have minimal
training, I'd like to make this "Add a new job / Add a new Contractor"
process as bullet-proof as possible. [Thanks for your reference to the
"not in list" event -- I only do this development stuff occasionally, and
it's easy to forget things between attempts <g>. I may use it to trigger
the "Add new Job" process!]

I want to prevent "accidental" duplicate and/or empty entries; but I
fear that the default Access error messages in such cases will be
confusing to the users.

I think I know how to trigger a pop up form to add a new Job; I *don't*
know how to replace the Access error messages with my own.

[The event code I posted was from the form I'm creating which will
become the pop up "Add new Job" form.]

It seems to me that the mechanism for "Add new Job" and "Add new
Contractor" is nearly identical ... so when I find the solution to one,
I'll have it for both.

Does this clarify what I'm asking for?

Thanks again for helping me learn!

[Oh -- yes, Contractor is a ComboBox on the Jobs form.

As to not using the Not_In_List event -- I'd simply forgotten all about
that whole set of tools in a Combo Box. They sure make life easier!

Why setting the box to Null? My thought was to 1) notify the user that
they had attempted an invalid addition and 2) present them with an empty
box for their next attempt. [I'm now planning to experiment with the
Selection Start and Selection Length properties so that after the msgbox
warning the user is presented with a fully selected value.]

Why use the control's before update instead of the form's? Simply to
notify the user immediately, before they have filled out the remaining
fields.

As to my original question: It appears to me that the Access (Jet?)
validation routines occur *after* the field's before update event, but do
not test "Cancel" --- after posting I happened across the form's "On
Error" event documentation ... that looks promising for what I wish to
accomplish.]

Again, thanks for the assistance; I believe I've learned far more in this
forum than from the included documentation!

 

[Clif McIrvin]

Thanks, Gina. I don't know if I'll have a chance to get bact to this
project today or not, myself. You've certainly given me ideas to pursue!

 

[Ken Sheridan]

Cliff:

Here's some code for an analogous situation. In this case its for
adding a new city in a combo box if a record for a site in a city not
yet in the database is being entered, triggering (after user
conformation) the opening of a second form in dialogue mode with the
new city name already in place as the default value so that the user
can enter other values, e.g. the state in which the city is located.
By opening the form in dialogue mode code execution in the calling
procedure is suspended until the second form is closed (or at least
hidden), so when code execution starts again the new city will usually
have been added. Not necessarily so, however, as the user has a
second escape route in that, because the city name is assigned to the
DefaultValue property of the control on the cities form rather than to
its Value property (which is the default property of the control) the
cities form is not 'dirtied', so the user can abort the new record
simply by closing the form before inserting any additional data.

All you really need to do is change the references to city to jobs or
contractors. The following is the code for the NotInList event
procedure of the city combo box (equivalent to your job or contactor
combo box on your production form) which is triggered when the user
types a city name not yet in the list into the combo box:

Private Sub cboCities_NotInList(NewData As String, Response As
Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub


The following is the code for the Open event procedure of the
frmCities form (equivalent to your jobs or contactors form) which
assigns the value passed to the form via the OpenArgs mechanism:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Note BTW that the value being assigned to the DefaultValue property is
wrapped in literal quotes characters. This property of a control is
always a string expression regardless of the data type of the column
to which the control is bound. Often it won't matter if the quotes
are omitted, but it can be crucial in situations where you might not
expect it to be, so its prudent to always include the quotes.

In the code for the NotInList event procedure above the system
generated messages are suppressed by setting the return value of the
Response argument to the constants acDataErrAdded (when a new item row
is inserted into the referenced table – this requeries the combo box
to include the new item in its list), or acDataErrContinue (this
simply carries on without displaying the system generated message if
the row is not inserted. You'll see that its followed by calling the
control's Undo method to remove the value which the user typed in).

Ken Sheridan
Stafford, England

Gina, thanks for responding.

Perhaps I'm coming at this from the wrong direction. I set out to state
the error I was getting, but your reply makes me realize that I never
said what I was attempting to accomplish. [Sheesh --- as often as I've
seen someone say "You've told us *how* you're trying to do something,
but not *what* you're trying to do.... you'd think I'd have started
there!]

Okay: here's the setup:

I have three tables:

Contractors
Jobs
Production

Production is related many-to-one to Jobs [JobID - Autonumber]
Jobs is related many-to-one to Contractors [ContractorID - Autonumber]
Referential integrity is enforced.

Due to a variety of work environment factors, frequently either the
required Job or Contractor has not been previously entered prior to the
data entry clerk attempting to enter daily production data.

I'm attempting to create a mechanism so when a job doesn't exist when
entering production data, the user can easily call up a form to create
the job. Since the data entry clerk will frequently have minimal
training, I'd like to make this "Add a new job / Add a new Contractor"
process as bullet-proof as possible. [Thanks for your reference to the
"not in list" event -- I only do this development stuff occasionally,
and it's easy to forget things between attempts <g>. I may use it to
trigger the "Add new Job" process!]

I want to prevent "accidental" duplicate and/or empty entries; but I
fear that the default Access error messages in such cases will be
confusing to the users.

I think I know how to trigger a pop up form to add a new Job; I *don't*
know how to replace the Access error messages with my own.

[The event code I posted was from the form I'm creating which will
become the pop up "Add new Job" form.]

It seems to me that the mechanism for "Add new Job" and "Add new
Contractor" is nearly identical ... so when I find the solution to one,
I'll have it for both.

Does this clarify what I'm asking for?

Thanks again for helping me learn!

[Oh -- yes, Contractor is a ComboBox on the Jobs form.

As to not using the Not_In_List event -- I'd simply forgotten all about
that whole set of tools in a Combo Box. They sure make life easier!

Why setting the box to Null? My thought was to 1) notify the user that
they had attempted an invalid addition and 2) present them with an empty
box for their next attempt. [I'm now planning to experiment with the
Selection Start and Selection Length properties so that after the msgbox
warning the user is presented with a fully selected value.]

Why use the control's before update instead of the form's? Simply to
notify the user immediately, before they have filled out the remaining
fields.

As to my original question: It appears to me that the Access (Jet?)
validation routines occur *after* the field's before update event, but
do not test "Cancel" --- after posting I happened across the form's "On
Error" event documentation ... that looks promising for what I wish to
accomplish.]

Again, thanks for the assistance; I believe I've learned far more in
this forum than from the included documentation!

--
Clif

Clif,

Okay, let me see if I understand this... the Contractor, which you are
trying to prevent duplicates this is not a combo box. (Not sure why
not because then you could use the Not_In_List event. But moving
along, I attempt to add but it's already in the list which suggest it
is a combo box, so why doesn't it just show thereby eliminating the
need for the Cancel = True. And then setting the box to Null which
you do not allow? Could be me but I need more information as to what
you are doing...

"I feel I have been denied critical, need to know, information!" -
Tremors II

 

[Clif McIrvin]

Cliff:

Here's some code for an analogous situation. In this case its for
adding a new city in a combo box if a record for a site in a city not
yet in the database is being entered


<snip>

Thanks, Ken! sounds exactly like what I was getting ready to try to do.
I'll take a close look at your code, and see just how little
modification it'll require!

 

[Clif McIrvin]

Ken and Gina ---

I had a little time to follow-up on this this morning ....

It's obvious I was heading down the wrong path and trying to make things
*way* too complicated.

Thanks to the input from both of you I'm confident I now know what I
need to do.

Thanks again.

 

[Gina Whipp]

Thanks for the feedback!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Clif McIrvin]

You're welcome. Seems like a small courtesey in exchange for all the
help you folks provide!

I've got it working ... feels pretty good, too!

--
Clif

Thanks for the feedback!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II

http://www.regina-whipp.com/index_files/TipList.htm