Delete record in multi-user database

[Guest]

I'm using some custom navigation buttons I created, and have added a Delete
button to the form.

I am currently using code that identifes which record I am on
(currentrecord), then deletes the applicable record, requeries the form (it
has a order that ensures the records will return in the same order each
time), then loops through the forms recordset to find the record after (or
before) the one that was deleted. But this loop flashes each records data to
the screen as it loops through the records. Is there a way to prevent the
form from refreshing as I loop through the records?

I am sure there are better techniques for this, and would like to hear other
options. I checked Steve Laben's custom nav buttons, but he doesn't include
a delete button on his subform.

Also, I have already limited the "delete" button to records created by that
user. What will happen if someone else is viewing or editing that record
when the creator deletes it? How can I prevent this?

 

[John Nurick]

Hi Dale,

I presume that before deleting the record you store its primary key
value, or the primary key value of the record you want to return to.

The usual technique is to use the form's RecordsetClone and Bookmark
properties. Try something like this:

Dim lngPKValue 'stored primary key value of record to go to

'Store primary key...
'Delete record...
'Requery form...

'Find the record in the form's RecordsetClone
With Me.RecordsetClone
.FindFirst "PK = " & lngPKValue
If .NoMatch Then
MsgBox "Record not found"
Else
'Move the form to the same record
Me.Bookmark = .Bookmark
End If
End With

If you stored the PK value of the record you deleted, use (e.g.)
.FindLast "PK < " & lngPKValue
or
.FindFirst "PK > " & lngPKValue

 

[Guest]

Thanks, John.

I use the bookmark property all of the time, but didn't even think of using
it to get the PK value of the next record, before the deletion.

Dale