T
Taylor.Ralston
I'm using Access 2007, and am extremely frustrated. I've searched all
of the newsgroups and web and have found nothing for this particular
situation. From the beginning:
I have a table ("Table A"). In this table, among other things, is a
lookup field ("Supplier IDs" or "SIDs"). This lookup field is a query
to another table ("Table B"). I have enabled it to have multiple
values. In the table view, when I click in that field, a drop down box
opens and I can check the boxes next to any number of these items.
So far, so good. Now I have created a form bound to this table ("Form
A"). In this form, the SID field transferred over as a combobox (when
done through the wizard). I converted it to a listbox, to be able to
show all those values at the same time. Things work great up to this
point.
I need to add some validation code to this. The purpose of this
validation will be: if any particular value is currently checked and a
user unchecks it, peform a query of certain criteria to determine
whether or not that value can be unchecked or not. So, let's say in
the SID listbox, I have values "apple", "banana", and "orange". All
three of them are currently checked. If the user tries to uncheck
"apple", I want to intercept the event and show a warning message and
undo the change.
I've been using the BeforeUpdate event (on the control, not the form).
The reason I'm doing it here, is because other parts of the form are
connected to those values in the SID listbox. If something is
unchecked accidentally, it can mess other stuff up. Hence the
validation.
Using the ever so common Cancel and Undo concepts may work
indepedently, but when used together, I get nasty results.
********
Private Sub SID_BeforeUpdate(Cancel as Integer)
' For test purposes, this will always try to undo the changes
Cancel = True
End Sub
********
This will cancel the update process, not posting the changes to the
current record bound to the form. This is exactly what I want.
However, in the SID listbox, the now empty checkbox next to the
"apple" item isn't consistent with the data on the record. Thus, I
tried to use the Undo method by itself. This works as well (for what
it's intended), but unfortunately while the listbox has been restored
to its previous state (sometimes it isn't), the bad data is still
written to the underlying recordset.
So I tried using both (I ordered the statements Cancel first then
Undo, and vice-versa). However, when I use both of these statements I
run into the dreaded ......
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".
It's an odd error, because I'm trying to cancel the saving, and it's
telling me it can't do that, even though the Cancel and Undo
functionality is already built into the database interface. Here's
something even more bizarre. If I close this Form A, and re-open it,
the first time I uncheck any box, it does the undo and cancel for me
flawlessly. But if I either check that particular item or try to
uncheck another item, the error 2115 comes up. Why? It worked the
first time, why not the second? I even tried checking the Dirty
property of the form in between the first and second times I test
this, and it's not dirty before the error occurs.
The only way I can get it to reset its behavior is to close the form
and reopen it. It still produces the error if I navigate to another
record or refresh the form.
I am desperate, I've tried everything I can think of. Is there some
undocumented criteria that I'm not meeting (or messing up) that's
preventing me from getting this right? I do appreciate any help, and
even more so as soon as possible as this project is approaching
deadline.
Thank you,
Taylor
of the newsgroups and web and have found nothing for this particular
situation. From the beginning:
I have a table ("Table A"). In this table, among other things, is a
lookup field ("Supplier IDs" or "SIDs"). This lookup field is a query
to another table ("Table B"). I have enabled it to have multiple
values. In the table view, when I click in that field, a drop down box
opens and I can check the boxes next to any number of these items.
So far, so good. Now I have created a form bound to this table ("Form
A"). In this form, the SID field transferred over as a combobox (when
done through the wizard). I converted it to a listbox, to be able to
show all those values at the same time. Things work great up to this
point.
I need to add some validation code to this. The purpose of this
validation will be: if any particular value is currently checked and a
user unchecks it, peform a query of certain criteria to determine
whether or not that value can be unchecked or not. So, let's say in
the SID listbox, I have values "apple", "banana", and "orange". All
three of them are currently checked. If the user tries to uncheck
"apple", I want to intercept the event and show a warning message and
undo the change.
I've been using the BeforeUpdate event (on the control, not the form).
The reason I'm doing it here, is because other parts of the form are
connected to those values in the SID listbox. If something is
unchecked accidentally, it can mess other stuff up. Hence the
validation.
Using the ever so common Cancel and Undo concepts may work
indepedently, but when used together, I get nasty results.
********
Private Sub SID_BeforeUpdate(Cancel as Integer)
' For test purposes, this will always try to undo the changes
Cancel = True
End Sub
********
This will cancel the update process, not posting the changes to the
current record bound to the form. This is exactly what I want.
However, in the SID listbox, the now empty checkbox next to the
"apple" item isn't consistent with the data on the record. Thus, I
tried to use the Undo method by itself. This works as well (for what
it's intended), but unfortunately while the listbox has been restored
to its previous state (sometimes it isn't), the bad data is still
written to the underlying recordset.
So I tried using both (I ordered the statements Cancel first then
Undo, and vice-versa). However, when I use both of these statements I
run into the dreaded ......
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".
It's an odd error, because I'm trying to cancel the saving, and it's
telling me it can't do that, even though the Cancel and Undo
functionality is already built into the database interface. Here's
something even more bizarre. If I close this Form A, and re-open it,
the first time I uncheck any box, it does the undo and cancel for me
flawlessly. But if I either check that particular item or try to
uncheck another item, the error 2115 comes up. Why? It worked the
first time, why not the second? I even tried checking the Dirty
property of the form in between the first and second times I test
this, and it's not dirty before the error occurs.
The only way I can get it to reset its behavior is to close the form
and reopen it. It still produces the error if I navigate to another
record or refresh the form.
I am desperate, I've tried everything I can think of. Is there some
undocumented criteria that I'm not meeting (or messing up) that's
preventing me from getting this right? I do appreciate any help, and
even more so as soon as possible as this project is approaching
deadline.
Thank you,
Taylor