Deleting Records FROM VBA

[Patrick]

HI!!

I want to delete a record via a button on one of my forms
and this is the VBA code behind it:

*****************************
Call GetAppPath ' gets the path

Set dbs = OpenDatabase(Path)
Set rsnew = dbs.OpenRecordset("SELECT * " _
& " FROM " & tableName & " " _
& " WHERE so_code=" & code & " and FY='" & fyr & "' " _
& " and ID=" & Ide & "")

With rsnew
..Delete
End With

Application.RefreshDatabaseWindow

DoCmd.GoToRecord , , acPrevious
*********************************************
UP TO HERE everything is working.

The part that's not working is this:

Once the focus is given to the previous record available,
If I press the 'Goto Last Record' in the navigation bar,
its allows me to go there and the form as 'DELETE' writen
in all of my fields. I DON'T want that.My last record
should be the same one that my 'Previous' code his know
pointing towards.

Can You help me make sense of this...

Patrick..

 

[John Vinson]

HI!!

I want to delete a record via a button on one of my forms
and this is the VBA code behind it:

*****************************
Call GetAppPath ' gets the path

Set dbs = OpenDatabase(Path)

I'd use

Set dbs = CurrentDb

rather than opening an entire new instance of the database! Much
faster.

Set rsnew = dbs.OpenRecordset("SELECT * " _
& " FROM " & tableName & " " _
& " WHERE so_code=" & code & " and FY='" & fyr & "' " _
& " and ID=" & Ide & "")

With rsnew
.Delete
End With

And here, you can do it much more simply:

Dim qd As Querydef
Set qd = dbs.CreateQuerydef("", "DELETE * FROM " & tableName & _
& " WHERE so_code=" & code & " and FY='" & fyr & "' " _
& " and ID=" & Ide & "")
qd.Execute dbFailOnError
Set qd = Nothing

Application.RefreshDatabaseWindow

DoCmd.GoToRecord , , acPrevious
*********************************************
UP TO HERE everything is working.

The part that's not working is this:

Once the focus is given to the previous record available,
If I press the 'Goto Last Record' in the navigation bar,
its allows me to go there and the form as 'DELETE' writen
in all of my fields. I DON'T want that.My last record
should be the same one that my 'Previous' code his know
pointing towards.

Save the Primary Key of the current record before running the delete
query, and Requery the form afterward; then use the Form's
Recordsetclone to find the Primary Key:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[IDfield] = " & lngSavedID
If rs.NoMatch Then rs.MoveFirst
Me.Bookmark = rs.Bookmark