CODEWITHSUNDEEP
ms-accessvbams-access-2007ms-access-2010ms-access-2013
Matt Hall
Matt Hall

Reputation: 2412

Deleting a record in form's recordsource without then triggering the form's current event

I am using a form's Current event to pull data from its OpenArgs property in order to pre-populate a few of the empty fields for the current record.

The Current event VBA is illustrated below (it's the pullData sub that brings this new data in to the current record):

Private Sub Form_Current()

    If _
        SplitOpenArg(6) = "NewAssessment" _
    Then        
        pullData
        EditMode
    Else
        ViewMode
    End If

End Sub

Elsewhere on the form I have a command button that branches in to an option for the user to delete/discard the current record displayed on the form:

Dim db As DAO.Database
Dim sql As String

Set db = CurrentDb    
sql = "DELETE * FROM myTable WHERE ID='" & Me.ID & "'"    
db.Execute sql

Set db = Nothing

DoCmd.Close acForm, "myForm"

When a record is deleted this way, it seems the Current event of the form is triggered... I suppose this is by design; Access moves to another record if the current one has been deleted.

Unfortunately for me though this means the pullData sub is run again and so partially re-creates the record the user is trying to delete.

I've tried DoCmd.RunCommand acCmdDeleteRecord, but that causes the same issue.

Is it possible to delete a record from a form's Record Source without then triggering the form's current event?

I could potentially close the form and delete the record in a module separate to the form, but would be interested if this can be achieved from the form module itself.

Upvotes: 0

Views: 1311

Answers (3)

PractLogical
PractLogical

Reputation: 258

[1] Create a private boolean variable for the form.

Option Compare Database
Option Explicit
Dim bUserDeleted As Boolean 'put this in the form's main code

The default value is already set to false, but you can set the var to false in the OnLoad() event if you wish

[2] Set the variable to True before you delete the record in the command button's code.

Dim db As DAO.Database
Dim sql As String

bUserDeleted = True 'set it to true so oncurrent and/or on unload can check it

Set db = CurrentDb    
sql = "DELETE * FROM myTable WHERE ID='" & Me.ID & "'"    
db.Execute sql

Set db = Nothing

DoCmd.Close acForm, "myForm"

[3] Jump out or the Sub(s) if bUserDeleted is True

Private Sub Form_Current()

   if bUserDeleted = True then exit Sub 'add this to jump out cleanly

    If _
        SplitOpenArg(6) = "NewAssessment" _
    Then        
        pullData
        EditMode
    Else
        ViewMode
    End If

End Sub

Upvotes: 0

Gustav
Gustav

Reputation: 55921

You could move the deleting code to the Unload event of the form, leaving just the close command to your button.

Upvotes: 0

jhTuppeny
jhTuppeny

Reputation: 970

I don't think you can get this approach to work - deleting the current record in a bound form will trigger a move to another record and fire the Current event.

Set up your form so that none of the fields are bound. When the form loads, get the data from the database and populate the fields. If the user then decides to delete this record, you then have the control to delete it and load another record as required.

Upvotes: 0

Related Questions