Continuous Form - Delete Selected Records

[soco3594]

Hi guys,

I thought this would be easier to find but haven't had any luck. I have a continuous form based on a simple table. One of the fields in the table is called "Select" with Yes/No data type, which allows the user to select 1 or more records on the form.

I want to add a "Delete" button to the form using which deletes only those records which are selected via VBA (i.e. "Select" checkbox ticked). So the button will find the records for which Select = True, prompt the user for confirmation, then delete those records.

Cheers

[CJ_London]

code behind the button click event would be something like

if msgbox("OK to Delete?",vbyesnocancel")=vbyes then
    currentdb.execute("DELETE * FROM myTable WHERE Selected=True")
end if

note that Select (which you have called your checkbox) is a reserved word, so change it to something else (e.g. selected)

[soco3594]

The code doesn't work every time for some reason... If I select a record and click delete, nothing happens. If I exit and re-enter the form and click delete (with the "selected" field still checked) the record deletes.

Should I be adding a refresh or requery command in there somewhere?

[alansidman]

try adding a

me.requery

to your code and see if that fixes your issue.

[soco3594]

I ended up adding a delete statement to the "close" button which actually achieves what I was trying to achieve more efficiently. Basically I wanted blank records to be deleted when I close the form (i.e. records which have been assigned an auto-number but for which no details were entered).

Similarly, if you delete the description against a record prior to closing the form, that record will also be deleted:

Private Sub btnClose_Click()

Dim sql As String
sql = "DELETE * FROM tblMilestonesTasks WHERE MilestoneTaskDescription Is Null"
CurrentDb.Execute sql

DoCmd.Save
DoCmd.Close

End Sub

[ssanfu]

Just an FYI.....

DoCmd.Save

This line is not needed. It is for saving changes to an object, not the data in a form.

Closing the form automatically saves changes to data. If you want to explicitly save data, use

Me.Dirty = FALSE

or

If Me.Dirty Then
   Me.Dirty = FALSE
End If

[soco3594]

Thanks. I actually had that in there from a previous iteration of the code, but you're right it's of no use in the current context.

Cheers

[Missinglinq]

...If I select a record and click delete, nothing happens. If I exit and re-enter the form and click delete (with the "selected" field still checked) the record deletes...

If your 'delete' button is present on each Record, the problem is likely that the change to the Record, i.e. ticking the Checkbox, hasn't been saved, yet. When you exit the Form and return, it has been saved, and hence works!

Linq ;0)>

[soco3594]

Of course! Perhaps adding a save command before the delete command would do the trick?

[June7]

Use ssanfu's suggestion or:

DoCmd.RunCommand acCmdSaveRecord

The Requery should accomplish the same. Except Requery resets focus to the first record.