Problem updating main form control from subform in Access 2003 Project (ADP) (1 Viewer)

[bluegno]

I'm supporting some Access/VBA code which includes the following behavior:

When an update is made in a (linked) child form, the current date/time is written to a textbox on the main form via

Me.Parent!txtMod = Now

Likewise, the BeforeUpdate event of the main form updates the same field (and then does a bunch of form validity checks, possibly followed by a Save).

When we upgraded to Access 2003 from Access 2000, a problem erupted:

When the user returns to the main form (by clicking on any control outside the subform), the following message is issued: "This record has been changed by another user since you started editing it (etc.)..."

At this point the user must choose between "Save Record", "Copy to Clipboard" and "Drop Changes".

Conceptually, I see why this is happening (though I wonder why Access 2000 didn't flag it). The subform is the "other user". When the main form is returned to, it is "Dirty" (unsaved update), so the main form's BeforeUpdate event fires, and attempts to update the field again (Me.txtMod = Now). Since there is a pending change to the field, an error dialog pops up.

As a quick fix, I replaced the code in the subform with a flag ("Hey, I've been updated!"), which the main form checks on the Exit event of the subform control. But that seems a horrible solution. Surely there's a better way?

Notes:

1. It would be preferable to have the timestamp updated right away by the subform, though having it updated when the subform loses focus is better than nothing.

2. Since a subform is a control from the main form's perspective, I'm surprised that a change to that control doesn't render the main form "Dirty". I guess from a database standpoint it makes sense (the form's table isn't getting updated at all; just the subform's). But then, how does the main form know that the subform has been updated?

3. I tried catching the error via Form_Error. I can make the dialog not appear via "Response = acDataErrContinue", but unfortunately it defaults to dropping the changes (old and new), rather than saving the new.

 

[bluegno]

Still scratching my head....

3 hours since posting, and no responses. Gosh, I feel like such a loser.

Actually, I have something to add. I tried doing a Cmd.Save before updating that "timestamp" text box on the main form, and I still get the same popup.

Now I'm completely mystified....

 

[bluegno]

Still need help

Hmmm... Let me give a less verbose statement of the problem:

What I need is a way for a subform to update a field on the main form, without the main form issuing the message "this record has been changed by another user since you started editing it".

This message started popping up in Access 2003 (not in 2000).

Work-arounds, such as trapping the error and defaulting to "Save" (is that possible?) are acceptable.

Thanks in advance.

 

[yeatmanj]

Bluegno, have you come up with a solution to this? I am encountering the same problem for many different reasons. I would have never encountered this problem in Access 2000, it only started happening to me when I upgraded to 2003.

 

[bluegno]

Unfortunately, I have received no assistance on this, and have found nothing on the web. So... I am still living with the "horrible solution" I mentioned.

I do need to look into this some more, as the users are getting extra message boxes as a result of the increased event firing -- rather annoying.

I'm not sure if this problem was intorduced in Access 2002 or 2003.

If you find anything, please let me know!

 

[yeatmanj]

All I've been able to find on the forum so far is references to trapping the error, but I did run across one that I haven't been able to test yet.

DoCmd.RunCommand acCmdSaveRecord

If there is an event that you can activate this command on prior to records being edited, it may help prevent the write conflict.

 

[bluegno]

I did attempt that route, and in fact started another thread on it. As you can see, I ran into a dead end here also.

 

[dc.gypsy]

I had a similar problem with the write conflict error. I was able to stop the error by using the AfterUpdate event in the subforms and BeforeUpdate in the main form.

 

[bluegno]

Thank you to dc.gypsy for the reply. I cannot remember if I tried that AfterUpdate/BeforeUpdate combination or not. I have had to put this tool on hold (my company won't let me work on it until it gets rebudgeted -- funny how that works), but will try it out as soon as I can.

If anybody else has had success with this method or any other, I would certainly like to hear about it.

 

[peej228]

Timestamp Adp problem

I had the same problem and researched and found that with a access mdb microsoft recommends adding a timestamp field to the table to resolve write conflicts. However Access Adp's do not use linked tables like in an access mdb linked table

I found in a different forum that Access Adp's and Timestamps fields cause the write conflict error.

I removed the timestamp field from my sql server table and the write conflict errors immediately stopped.

 

[bluegno]

Problem Solved!

The AfterUpdate/BeforeUpdate solution proposed by dc.gypsy actually fixed the problem, and I was able to remove my hideous kludge! Thank you dc.gypsy, wherever you are!

Thanks also to peej228 for the alert on the timestamp. Though that isn't the culprit in this case, this is good information.

 

[dadigu]

I am experiencing this problem and am just an average Access/VBA guy. Could you be so kind to elaborate a bit on what you are doing with the respective after and before update events to deal with this.
Thanks.

 

[yeatmanj]

I'm not exactly certain what bluegno is doing here, but my solution to this problem was a lot different. I converted the forms to unbound objects and did all of my manipulations using VBA code. In this way I was able to lock the records in different instances and save them to prevent the error.

 

[bluegno]

dadigu,

It's been a while (I'd have to dig through my deltas), but I think I did basically this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.txtLastModifiedBy = GetUserName()
Me.txtLastModifiedDate = Now
End If
End Sub

In the subform:
Private Sub Form_AfterUpdate()
Forms!frmMain!txtLastModifiedBy = GetUserName()
Forms!frmMain!txtLastModifiedDate = Now
. . .
End Sub

 

[dadigu]

Thanks for the quick replies. I appreciate it.

I'm going to try these suggestions out, starting with the one from bluegno since it requires less changes to other things.

bluegno, what is the operative concept here? Is Access being fooled to think that the same "user" made the update in question, therefore, no update conflict?