RE: Help with Delete Button

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

That would be as expected.

"Alex" wrote:

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" wrote:

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" wrote:

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" wrote:

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


"Alex" wrote:

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
.

Relevant Pages

  • Re: not responding
    ... Alex wrote: ... "Try deleting ALL the files in the \document and ... CS2 Settings folder" ...
    (alt.graphics.photoshop)
  • Re: Accidently deleted public folder
    ... Alex ... > I was distracted, and instead of deleting an email, I accidently deleted a ... > public folder from my list of folders in Outlook (was connected tghrough a ...
    (microsoft.public.windows.server.sbs)
  • Re: Deleting file beginning with - using rm
    ... > I tried deleting it using 'rm', but to no means was I successful. ... $ ls | grep foo ... [alex@bubonis alex]$ rm ./-foo ...
    (alt.linux)
  • RE: Help with Delete Button
    ... The form's recordset is a query that has more than one table in it. ... Are you actually deleting a record in the form's record source or a record ... On Error GoTo DelCurrentRec_Error ... Set rst = Nothing ...
    (microsoft.public.access.formscoding)