Delete Record Form Refresh 1

[corner40]

Hi Everyone
Seems like all my questions start out with this:
I have a form(f_bookinghead, PK BookingGroupID) that has a sub-form(f_bookingheadform, PK BookingHeadID FK BookingGroupID) and also a sub-subform(f_BookingDetail, PK BookingDetailID, FK BookingHeadID).

On f_bookingdetail i have a delete record cmdbutton. It deletes the bookingheadID and obvisouly the bookingdetailID. What I would like is to go back to the last "good" record instead of showing the "#deleted" in all the fields. here is my code to delete the record and I have tried a refresh and also gotoprevious record.
Let me know what I should do...all suggestions are greatly appreciated

Code:

Private Sub imgRemoveDay_Click()
Dim strSQL As String
Dim strSQL1 As String
Dim tempDay As Integer
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim bgid As Integer
Dim bhid As Integer
Dim bdid As Integer
Dim stDocName As String
Dim stLinkCriteria As String
tempDay = Me.Day

strSQL = "SELECT BookingGroup.BookingGroupID, BookingHead.BookingHeadID, BookingDetail.BookingDetailID, BookingDetail.Day " & _
         "FROM (BookingGroup INNER JOIN BookingHead ON BookingGroup.BookingGroupID = BookingHead.BookingGroupID) " & _
         "INNER JOIN BookingDetail ON BookingHead.BookingHeadID = BookingDetail.BookingHeadID " & _
         "WHERE (((BookingGroup.BookingGroupID)= " & Forms![f_bookinghead].BookingGroupID & ") " & _
         "AND ((BookingDetail.Day)=" & tempDay & ")); "
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
bgid = rs.Fields(0)
bhid = rs.Fields(1)
bdid = rs.Fields(2)

If Me.Day > 0 Then
    strSQL1 = "DELETE FROM [bookinghead] WHERE [bookingheadID] = " & Val(Me.BookingHeadID)
    CurrentDb.Execute strSQL1
End If

Refresh

End Sub

thanks everyone
Jeremy

 

[seleceus]

Instead of trying the GoToPrevious, from a deleted record, try using <code>DoCmd.GoToRecord , , acLast</code> just before your Refresh statement.