Go to Next record following delete

[helmpost]

My club management access database has been developed by several owners and through several different access versions - I am the latest owner and am on 2002 SP3.

The main membership form includes custom buttons to Add or Delete new memebrs in VBA. The Delete code includes a requery that restarts the form from record 1. The users would like to simply go to the next or perhaps the previous record following the deletion.

If I remove the requery, the current record is displayed with all fields marked as "Deleted".

Experimenting, I could test for a unique piece of data from the next record - for example using

Application.DoCmd.RunCommand acCmdRecordsGoToNext

but am a bit stuck from then on ...

This problem (feature ?) must be quite common - is there an accepted best practice to delete the current record and move to the next or previous record from within a form ?

Many Thanks

Helmpost

 

[Remou]

Hi
It may be best to post the Record Source for the form.

 

[helmpost]

Thanks Remou

Here's the record source for the form. I hope this is what you wanted ?

SELECT DISTINCTROW Contacts.*
FROM Contacts INNER JOIN Members ON Contacts.ContactID = Members.ContactID
WHERE (((Contacts.ContactID)=[Members].[ContactID]))
ORDER BY Contacts.SortName;

Records in the contacts table are unique. There is a one to many relationship between the contacts table, the members table and other tables that might or might not contain data. The key tables are all linked by contactID.

 

[smedvid]

U could also use the Record Set Clone option to MoveNext and grab the next position? htwh.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Remou]

I think you should change your record source to:

SELECT Contacts.*
FROM Contacts INNER JOIN Members ON Contacts.ContactID = Members.ContactID;

In a test database, and then see what happens when you delete. The reason I say this is that the default behaviour is to show the next or previous record. You can get rid of the Refresh, in this test, too.

 

[TheAceMan1]

How are ya helmpost . . . . .

For your [blue]Delete Button[/blue], substitute the following code. The code sets focus to the next record if one exist, previous record otherwise, allowing for new or no records. Note: [blue]you![/blue] substitute proper names/values in [purple]purple[/purple]:

[blue]   Dim rst As DAO.Recordset, pkName As String, hldID
   
   pkName = "[purple][b]PrimaryKeyName[/b][/purple]"
   
   If Me.NewRecord Then
      If Me.Dirty Then Me.Undo
   Else
      Set rst = Me.RecordsetClone
      rst.FindFirst "[" & pkName & "] = " & Me(pkName)
      rst.MoveNext
      
      If rst.EOF Then
         rst.MovePrevious
         rst.MovePrevious
         If Not rst.BOF Then hldID = rst(pkName)
      Else
         hldID = rst(pkName)
      End If
      
      Set rst = Nothing
      
      DoCmd.RunCommand acCmdDeleteRecord
      Me.Requery
      
      If Not IsEmpty(hldID) Then
         Set rst = Me.RecordsetClone
         rst.FindFirst "[" & pkName & "] = " & hldID
         Me.Bookmark = rst.Bookmark
         Set rst = Nothing
      End If
   End If[/blue]

The code assumes the [blue]PrimaryKey[/blue] is numeric. If text, slight modifications are required.

Special Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[purple]Report any problems . . .[/purple]

See Ya! . . . . . .

 

[helmpost]

Hi AceMan1

That looks good (there's nil on Recordset Clone in my system Help and my books are all old !). Since all the records have at least one join and possibly more, there's a need to delete these subordinate records before the top level record can be deleted. In my current code, this is achieved via sql statements as follows where the contacts table is the top level record. I've tried adding this sort of syntax just before the statement DoCmd.RunCommand acCmdDeleteRecord and I get ther error "Could not update, currently locked by another sesion on this machine". I guess I'm holding the record open at this point and therefore need to modify the code .... ?

Dim wkSpc As Workspace
Dim db As Database
Dim sSQL As String

' attach to the open database
Set wkSpc = DBEngine.Workspaces(0)
Set db = wkSpc.Databases(0)

wkSpc.BeginTrans

' Delete the boat information
sSQL = "DELETE * FROM Boats WHERE ContactID=" & lContactID
db.Execute (sSQL)

' Delete the members information
sSQL = "DELETE * FROM Members WHERE ContactID=" & lContactID
db.Execute (sSQL)

' Delete the contact address
'sSQL = "DELETE * FROM Contacts WHERE ContactID=" & lContactID
'db.Execute (sSQL)

wkSpc.CommitTrans

With thanks
Helmpost

 

[Remou]

Have you looked at cascade delete? Also, I think AceMan would agree that your record source needs looking at, if not my suggestion, at least some tidying .

SELECT DISTINCTROW Contacts.*
FROM Contacts INNER JOIN Members ON Contacts.ContactID = Members.ContactID
WHERE (((Contacts.ContactID)=[Members].[ContactID]))
ORDER BY Contacts.SortName;

 

[helmpost]

Remou

Sorry not to have responded.

The Form's underlying query does look a little odd (developd before my time). I found that the records in the top table are not unique - don't yet understand why in the design. I can get the results required on the form using this query

SELECT DISTINCTROW Contacts.*, Contacts.SortName
FROM Contacts INNER JOIN Members ON Contacts.ContactID = Members.ContactID
ORDER BY Contacts.SortName;

To be honest, I'm not enough of a programmer to see much of a difference !

 

[Remou]

It might be worth making them unique, if they ought to be. There is an FAQ on getting unique records here:
Delete Duplicates (Save One From Each Group)
faq701-5721

 

[TheAceMan1]

helmpost . . . . .

[blue]Remou[/blue] is on target. In fact, have a look at both [blue]Cascade Update & Delete![/blue] With Cascade Delete you can get rid of that chunk of code.

[blue](there's nil on Recordset Clone in my system Help and my books are all old !)[/blue]

Have a look here: Using Recordset Objects

See Ya! . . . . . .

 

[helmpost]

Fixed !

I'd like to say thank you and award stars to Remou and TheAceman1 - Aceman1 for the code and Remou for telling me about Cascading Deletes (never looked at this before - so much tidier) and advice regarding the source query.

Best Regards
Helmpost