Deleting Records FROM VBA

  • Thread starter Thread starter Patrick
  • Start date Start date
P

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..
 
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)
Click to expand...

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
Click to expand...

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.
Click to expand...

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
 
Back
Top