B
Bob
Hi folks,
I have a continuous subform (subfrm_ContactWebComs) which links to a
junction table called ContactWebComs joined to a main table called
WebComs.
WebComs contains internet related communication details for contacts in
my database - eg email addresses, ftps, websites etc. There is a many
to many relationship between WebComs and my main contact table - called
Contacts.
Anyways, in my subform, I have two main textbox controls: WebComType
(stores a description - like "Home Email") and WebComDetails (stores
the actual email address etc).
Because of the many to many relationship, when a user changes either
the WebComType or the WebComDetails, I want Access go invoke the
following procedure:
1. Check if the record is linked (ie whether the record is related to
other contacts or related only to the current contact).
2. If the record is unlinked, simply update the record.
3. If the record is linked, advise user that the record is linked and
ask whether the user wants to:
(a) cancel the update;
(b) update the linked record - ie reflect the update for all contacts;
(c) create an unlinked record - ie reflect the update for the current
record only. This requires Access:
(i) delete the current entry in the junction table;
(i) insert a new entry in the WebComs table;
(ii) insert a new entry in the ContactWebComs table identifying the new
WebComs record.
4. If the user elects to create an unlinked record:
(a) requery after the table operations (ie the delete and two insert
operations) to reflect the change; and
(b) locate the new record on the form (the underlying recordsource is
set to Order by WebComType); and
(c) put the cursor in the correct (ie edited) textbox - this will
either be the WebComType or the WebComDetails text box depending on
which one triggered the whole thing.
I have managed to cobble together the code to achieve all of the above,
but I have found that if I put the code in the BeforeUpdate Event
(which intuitively seems to be the correct location for the code), the
last step (4(c)) results in an error. The last step uses the SetFocus
method. The error says something about needing to save the current
record first.
I googled this error and the general advice was that I need to shift
the setfocus part of the code to the AfterUpdate Event. I tried doing
this but found that the event was not triggering if the user elected to
create an unlinked record. The reason for this, I think, is that
immediately before the delete operation (3(c)(i) above) I call the
following code:
Me.Undo
Me.Cancel
To get around the problem, I have relocated all of my code from the
BeforeUpdate event to the AfterUpdate event.
Everything now seems to work fine. My concern is that by putting the
code in the AfterUpdate event (for the edited control) I would have
thought that this means that the changes to the current record would be
posted to the database before any of the code actually gets to run. In
other words, I expected that the changes would be reflected in the
underlying record (which applies to all contacts) before my delete and
insert operations take effect - with the result being that although I
would get a new and unlinked record for the current contact, the
changes would still be reflected in linked record for all other
contacts.
So far, however, everything seems to work as it should. The original
(linked) record remains unchanged, and a new (unlinked) record is
created for the current contact only.
Whilst I am obviously happy with this result, I still feel uneasy about
putting the code in the AfterUpdate event procedure. I particular, I
am wondering whether it is safe to put the code in the AfterUpdate
event from the perspective of avoiding errors and loss of data (other
than the edits to the current control).
I am also wondering why the errors I was anticipating are not
occurring. I assume it is because of the Undo and Cancel methods
described above. Obviously these methods are being operated on the
form itself, rather than the current control (WebComType or
WebComDetails). I understand that the form's afterupdate event occurs
after the control's afterupdate event. For this reason, I assume that
the reason why the undo/cancel is working (rolling back all the
changes) is because although the control has been updated by the time
my code is running, the form still has not been updated. Consequently,
I am assuming that although the changes to the current control have
already been updated (ie posted) to the database by the time the
control's AfterUpdate event is called, those changes are being
successfully wound back by calling the undo/cancel method on the form
itself. Is this correct?
What I am keen to avoid, obviously, is letting this thing to live only
to discover later on that the linked record is in fact being updated as
well as a new unlinked record being created. As I've said though, at
the moment this is not happening.
I hope this is all clear.
Regards
Bob
I have a continuous subform (subfrm_ContactWebComs) which links to a
junction table called ContactWebComs joined to a main table called
WebComs.
WebComs contains internet related communication details for contacts in
my database - eg email addresses, ftps, websites etc. There is a many
to many relationship between WebComs and my main contact table - called
Contacts.
Anyways, in my subform, I have two main textbox controls: WebComType
(stores a description - like "Home Email") and WebComDetails (stores
the actual email address etc).
Because of the many to many relationship, when a user changes either
the WebComType or the WebComDetails, I want Access go invoke the
following procedure:
1. Check if the record is linked (ie whether the record is related to
other contacts or related only to the current contact).
2. If the record is unlinked, simply update the record.
3. If the record is linked, advise user that the record is linked and
ask whether the user wants to:
(a) cancel the update;
(b) update the linked record - ie reflect the update for all contacts;
(c) create an unlinked record - ie reflect the update for the current
record only. This requires Access:
(i) delete the current entry in the junction table;
(i) insert a new entry in the WebComs table;
(ii) insert a new entry in the ContactWebComs table identifying the new
WebComs record.
4. If the user elects to create an unlinked record:
(a) requery after the table operations (ie the delete and two insert
operations) to reflect the change; and
(b) locate the new record on the form (the underlying recordsource is
set to Order by WebComType); and
(c) put the cursor in the correct (ie edited) textbox - this will
either be the WebComType or the WebComDetails text box depending on
which one triggered the whole thing.
I have managed to cobble together the code to achieve all of the above,
but I have found that if I put the code in the BeforeUpdate Event
(which intuitively seems to be the correct location for the code), the
last step (4(c)) results in an error. The last step uses the SetFocus
method. The error says something about needing to save the current
record first.
I googled this error and the general advice was that I need to shift
the setfocus part of the code to the AfterUpdate Event. I tried doing
this but found that the event was not triggering if the user elected to
create an unlinked record. The reason for this, I think, is that
immediately before the delete operation (3(c)(i) above) I call the
following code:
Me.Undo
Me.Cancel
To get around the problem, I have relocated all of my code from the
BeforeUpdate event to the AfterUpdate event.
Everything now seems to work fine. My concern is that by putting the
code in the AfterUpdate event (for the edited control) I would have
thought that this means that the changes to the current record would be
posted to the database before any of the code actually gets to run. In
other words, I expected that the changes would be reflected in the
underlying record (which applies to all contacts) before my delete and
insert operations take effect - with the result being that although I
would get a new and unlinked record for the current contact, the
changes would still be reflected in linked record for all other
contacts.
So far, however, everything seems to work as it should. The original
(linked) record remains unchanged, and a new (unlinked) record is
created for the current contact only.
Whilst I am obviously happy with this result, I still feel uneasy about
putting the code in the AfterUpdate event procedure. I particular, I
am wondering whether it is safe to put the code in the AfterUpdate
event from the perspective of avoiding errors and loss of data (other
than the edits to the current control).
I am also wondering why the errors I was anticipating are not
occurring. I assume it is because of the Undo and Cancel methods
described above. Obviously these methods are being operated on the
form itself, rather than the current control (WebComType or
WebComDetails). I understand that the form's afterupdate event occurs
after the control's afterupdate event. For this reason, I assume that
the reason why the undo/cancel is working (rolling back all the
changes) is because although the control has been updated by the time
my code is running, the form still has not been updated. Consequently,
I am assuming that although the changes to the current control have
already been updated (ie posted) to the database by the time the
control's AfterUpdate event is called, those changes are being
successfully wound back by calling the undo/cancel method on the form
itself. Is this correct?
What I am keen to avoid, obviously, is letting this thing to live only
to discover later on that the linked record is in fact being updated as
well as a new unlinked record being created. As I've said though, at
the moment this is not happening.
I hope this is all clear.
Regards
Bob