trying to minimize Write Conflicts in a multi-user database

[Armen Stein]

I'm not certain this is correct. I would never leave it to chance --
if I know I have a possibility of two subforms based on the same
table and both are editable in different tabs, I would use the tab
control's OnChange event to make sure none of the subform's are
dirty.

Well, I am. It isn't chance - it's normal Access behavior.

That said, we don't normally have two *editable* subforms on the same
table on the same form anyway. If that's the only time you're doing
the explicit save, then I think that's fine to control the saves more
precisely. I just wouldn't recommend them as a standard practice -
only when we need them for some other reason. Basically we always try
for the simplest code possible.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

Well, I am. It isn't chance - it's normal Access behavior.

What about the point of having a line of code that explicitly saves
as the point of origin of any failure in the save process? That
seems pretty compelling to me.

That said, we don't normally have two *editable* subforms on the
same table on the same form anyway. If that's the only time
you're doing the explicit save, then I think that's fine to
control the saves more precisely. I just wouldn't recommend them
as a standard practice - only when we need them for some other
reason. Basically we always try for the simplest code possible.

I try for the most reliably code. The explicit save seems absolutely
required to me in order to troubleshoot save errors.

I was badly burned by an unheralded aspect of the bookmark bug,
i.e., that setting the bookmark to move the record pointer
implicitly saves any edits to the departed record, but errors in
that save were getting eaten by Access. Thus, this code was
dangerous:

With Me.RecordsetClone
.FindFirst "[PK]=" & Me!cmbComboBox
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The line where the bookmarks are set (which does the navigation)
implicitly saves any edits, and the bug was that in certain
circumstances I could never identify, those edits would *not* be
saved, and no error would be reported.

This code is safe:

With Me.RecordsetClone
.FindFirst "[PK]=" & Me!cmbComboBox
If Not .NoMatch Then
If Me.Dirty Then
Me.Dirty = False
End If
Me.Bookmark = .Bookmark
End If
End With

....and the reason it's safe is because you're not relying on the
implicit save -- you're explicitly telling Jet to save the edit, and
if any errors happen in that save, they will be reported.

This is the same reason I'd always explicitly save dirty subforms
when changing a tab, because it's essential to do the save
EXPLICITLY, rather than relying on an implicit save which
historically has been buggy in other circumstances.

 

[Armen Stein]

I was badly burned by an unheralded aspect of the bookmark bug,
i.e., that setting the bookmark to move the record pointer
implicitly saves any edits to the departed record, but errors in
that save were getting eaten by Access. Thus, this code was
dangerous:

With Me.RecordsetClone
.FindFirst "[PK]=" & Me!cmbComboBox
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Okay, I see your point in this case. I don't think we've ever used
Bookmark navigation on an editable record - we use it a lot on
read-only continuous forms, where this bug wouldn't occur.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Paul]

My thanks to all the luminaries who contributed to this conversation, for
their help and suggestions.

Some of the more advanced topics discussed were over my head, but you've
given me some great ideas for eliminating the write conflicts I had
originally asked about.

And thanks again for that handy navigation bar, John.

Paul

 

[David W. Fenton]

I was badly burned by an unheralded aspect of the bookmark bug,
i.e., that setting the bookmark to move the record pointer
implicitly saves any edits to the departed record, but errors in
that save were getting eaten by Access. Thus, this code was
dangerous:

With Me.RecordsetClone
.FindFirst "[PK]=" & Me!cmbComboBox
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Okay, I see your point in this case. I don't think we've ever
used Bookmark navigation on an editable record - we use it a lot
on read-only continuous forms, where this bug wouldn't occur.

But I was only using it as a clear case of where you really *must*
force the save explicitly. I believe that navigating out of a
subform is another such situation where you should explicitly save,
rather than depending on the implicit save occuring without problems
(or bugs!).