Help with Delete Button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the following code in my delete button, which works fine as long as
the record that's being delete is not the last record. If the deleted record
is the last record, a new record is opened. Is there a way that if there is
more then one record in the recordset and a record is deleted, to avoid a new
record opening and make the form go to the previous record? I've tried
DoCmd.GoToRecord, , , acprevious and that doesn't work. Thank you.

If Me.Dirty Then
Me.Undo
End If

If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
' Me.Model_ = NewModel
End If

With Me.RecordsetClone
If .RecordCount = 0 Then
DoCmd.Close acForm, Me.Name
 
This does that quite nicely. Put it in a standard module, and you can use it
from any form. Call it from your form like this:

DelCurrentRec(Me)


Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function
 
Thank you. I tried this function but it didn't work. I actually reposted
with my current code and was just going to indicate to ignore this post when
I saw you had responded. Because my form's data source is a query that
includes more than one table, when I use the regular delete, records from
both of my tables were getting deleted which is not correct. To only delete
the record in my 'main' table I have changed the code in my delete button to:

Dim strSQL As String

If Me.New_Part_NHL <> "" And Me.New_Part_ <> "" Then

strSQL = "DELETE * FROM AllNewParts WHERE [Model#] = '" &
[Forms]![NewPartInputfrm]![Model] & "' And [Part#] = '" &
[Forms]![NewPartInputfrm]![Part] & "' And [NHL] = '" &
[Forms]![NewPartInputfrm]![NHL] & "'"
CurrentDb.Execute (strSQL), dbFailOnError

Call DelCurrentRec(Me)


Else
Me.Undo
End If



With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else


End If
End With

It seems like it should be quite simple to just delete a record and go to
previous record. Thanks.
 
Well, the code works, but because of the query with two tables, maybe it wont
work for you. You could use pieces of the code I posted to navigate to the
next or previous record depending on where you are in the recordset.

Are you actually deleting a record in the form's record source or a record
in one of the tables. it seems to me that unless you requery your form, your
recordset doesn't know the underlying data is gone.


Alex said:
Thank you. I tried this function but it didn't work. I actually reposted
with my current code and was just going to indicate to ignore this post when
I saw you had responded. Because my form's data source is a query that
includes more than one table, when I use the regular delete, records from
both of my tables were getting deleted which is not correct. To only delete
the record in my 'main' table I have changed the code in my delete button to:

Dim strSQL As String

If Me.New_Part_NHL <> "" And Me.New_Part_ <> "" Then

strSQL = "DELETE * FROM AllNewParts WHERE [Model#] = '" &
[Forms]![NewPartInputfrm]![Model] & "' And [Part#] = '" &
[Forms]![NewPartInputfrm]![Part] & "' And [NHL] = '" &
[Forms]![NewPartInputfrm]![NHL] & "'"
CurrentDb.Execute (strSQL), dbFailOnError

Call DelCurrentRec(Me)


Else
Me.Undo
End If



With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else


End If
End With

It seems like it should be quite simple to just delete a record and go to
previous record. Thanks.



Klatuu said:
This does that quite nicely. Put it in a standard module, and you can use it
from any form. Call it from your form like this:

DelCurrentRec(Me)


Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function
Click to expand...
Click to expand...
 
The form's recordset is a query that has more than one table in it. I'm
deleting a record from one of those tables. When I add me.requery in the
code I just get a message "record is deleted".

Klatuu said:
Well, the code works, but because of the query with two tables, maybe it wont
work for you. You could use pieces of the code I posted to navigate to the
next or previous record depending on where you are in the recordset.

Are you actually deleting a record in the form's record source or a record
in one of the tables. it seems to me that unless you requery your form, your
recordset doesn't know the underlying data is gone.


Alex said:
Thank you. I tried this function but it didn't work. I actually reposted
with my current code and was just going to indicate to ignore this post when
I saw you had responded. Because my form's data source is a query that
includes more than one table, when I use the regular delete, records from
both of my tables were getting deleted which is not correct. To only delete
the record in my 'main' table I have changed the code in my delete button to:

Dim strSQL As String

If Me.New_Part_NHL <> "" And Me.New_Part_ <> "" Then

strSQL = "DELETE * FROM AllNewParts WHERE [Model#] = '" &
[Forms]![NewPartInputfrm]![Model] & "' And [Part#] = '" &
[Forms]![NewPartInputfrm]![Part] & "' And [NHL] = '" &
[Forms]![NewPartInputfrm]![NHL] & "'"
CurrentDb.Execute (strSQL), dbFailOnError

Call DelCurrentRec(Me)


Else
Me.Undo
End If



With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else


End If
End With

It seems like it should be quite simple to just delete a record and go to
previous record. Thanks.



Klatuu said:
This does that quite nicely. Put it in a standard module, and you can use it
from any form. Call it from your form like this:

DelCurrentRec(Me)


Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function


:

I'm using the following code in my delete button, which works fine as long as
the record that's being delete is not the last record. If the deleted record
is the last record, a new record is opened. Is there a way that if there is
more then one record in the recordset and a record is deleted, to avoid a new
record opening and make the form go to the previous record? I've tried
DoCmd.GoToRecord, , , acprevious and that doesn't work. Thank you.

If Me.Dirty Then
Me.Undo
End If

If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
' Me.Model_ = NewModel
End If

With Me.RecordsetClone
If .RecordCount = 0 Then
DoCmd.Close acForm, Me.Name
Click to expand...
Click to expand...
Click to expand...
 
That would be as expected.

Alex said:
The form's recordset is a query that has more than one table in it. I'm
deleting a record from one of those tables. When I add me.requery in the
code I just get a message "record is deleted".

Klatuu said:
Well, the code works, but because of the query with two tables, maybe it wont
work for you. You could use pieces of the code I posted to navigate to the
next or previous record depending on where you are in the recordset.

Are you actually deleting a record in the form's record source or a record
in one of the tables. it seems to me that unless you requery your form, your
recordset doesn't know the underlying data is gone.


Alex said:
Thank you. I tried this function but it didn't work. I actually reposted
with my current code and was just going to indicate to ignore this post when
I saw you had responded. Because my form's data source is a query that
includes more than one table, when I use the regular delete, records from
both of my tables were getting deleted which is not correct. To only delete
the record in my 'main' table I have changed the code in my delete button to:

Dim strSQL As String

If Me.New_Part_NHL <> "" And Me.New_Part_ <> "" Then

strSQL = "DELETE * FROM AllNewParts WHERE [Model#] = '" &
[Forms]![NewPartInputfrm]![Model] & "' And [Part#] = '" &
[Forms]![NewPartInputfrm]![Part] & "' And [NHL] = '" &
[Forms]![NewPartInputfrm]![NHL] & "'"
CurrentDb.Execute (strSQL), dbFailOnError

Call DelCurrentRec(Me)


Else
Me.Undo
End If



With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else


End If
End With

It seems like it should be quite simple to just delete a record and go to
previous record. Thanks.



:

This does that quite nicely. Put it in a standard module, and you can use it
from any form. Call it from your form like this:

DelCurrentRec(Me)


Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function


:

I'm using the following code in my delete button, which works fine as long as
the record that's being delete is not the last record. If the deleted record
is the last record, a new record is opened. Is there a way that if there is
more then one record in the recordset and a record is deleted, to avoid a new
record opening and make the form go to the previous record? I've tried
DoCmd.GoToRecord, , , acprevious and that doesn't work. Thank you.

If Me.Dirty Then
Me.Undo
End If

If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
' Me.Model_ = NewModel
End If

With Me.RecordsetClone
If .RecordCount = 0 Then
DoCmd.Close acForm, Me.Name
Click to expand...
Click to expand...
Click to expand...
 
You said to requery my form. How does that help when I keep getting the
message? Can I get past it and set the recordset to previous record?

Klatuu said:
That would be as expected.

Alex said:
The form's recordset is a query that has more than one table in it. I'm
deleting a record from one of those tables. When I add me.requery in the
code I just get a message "record is deleted".

Klatuu said:
Well, the code works, but because of the query with two tables, maybe it wont
work for you. You could use pieces of the code I posted to navigate to the
next or previous record depending on where you are in the recordset.

Are you actually deleting a record in the form's record source or a record
in one of the tables. it seems to me that unless you requery your form, your
recordset doesn't know the underlying data is gone.


:

Thank you. I tried this function but it didn't work. I actually reposted
with my current code and was just going to indicate to ignore this post when
I saw you had responded. Because my form's data source is a query that
includes more than one table, when I use the regular delete, records from
both of my tables were getting deleted which is not correct. To only delete
the record in my 'main' table I have changed the code in my delete button to:

Dim strSQL As String

If Me.New_Part_NHL <> "" And Me.New_Part_ <> "" Then

strSQL = "DELETE * FROM AllNewParts WHERE [Model#] = '" &
[Forms]![NewPartInputfrm]![Model] & "' And [Part#] = '" &
[Forms]![NewPartInputfrm]![Part] & "' And [NHL] = '" &
[Forms]![NewPartInputfrm]![NHL] & "'"
CurrentDb.Execute (strSQL), dbFailOnError

Call DelCurrentRec(Me)


Else
Me.Undo
End If



With Me.RecordsetClone
If .RecordCount = 1 Then
DoCmd.Close acForm, Me.Name
Else


End If
End With

It seems like it should be quite simple to just delete a record and go to
previous record. Thanks.



:

This does that quite nicely. Put it in a standard module, and you can use it
from any form. Call it from your form like this:

DelCurrentRec(Me)


Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function


:

I'm using the following code in my delete button, which works fine as long as
the record that's being delete is not the last record. If the deleted record
is the last record, a new record is opened. Is there a way that if there is
more then one record in the recordset and a record is deleted, to avoid a new
record opening and make the form go to the previous record? I've tried
DoCmd.GoToRecord, , , acprevious and that doesn't work. Thank you.

If Me.Dirty Then
Me.Undo
End If

If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
' Me.Model_ = NewModel
End If

With Me.RecordsetClone
If .RecordCount = 0 Then
DoCmd.Close acForm, Me.Name
Click to expand...
Click to expand...
Click to expand...
 
Back
Top