#Deleted issue with listbox - requery not working :( (1 Viewer)

BeardedSith

Member
Local time
Today, 07:02
Joined
Feb 5, 2020
Messages
73
I have a form (frmRewards) that has a listbox (lstCustomers) which is used to select customers from a list. When you double-click the customer's name, the rest of the form is populated (no subforms).

When I delete the record, I get the infamous #Deleted issue in the listbox. I've tried a couple different methods I've found around the internet (and these forums) but none seem to work.

cmdDelete_Click() control:
Code:
Private Sub cmdDelete_Click()
Dim Response As Integer
On Error GoTo cmdDelete_Click_Err

  DoCmd.SetWarnings False
  If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acNewRec
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
    Me.Requery
    Me.lstCustomers.Value = ""
    Me.txtFilter.Value = ""
  End If

cmdDelete_Click_Exit:
    Exit Sub

cmdDelete_Click_Err:
    MsgBox Error$
    Resume cmdDelete_Click_Exit
End Sub

Right now the code is a mish-mash of different ideas to tackle this problem, but none seem to work (Me.Dirty & Me.Requery specifically). Outside of closing the form and re-opening it, what else could I try here? I know this is a safety mechanism within Access to let the user know they deleted a record, but I'd rather accomplish that with Message Boxes and just make the record disappear completely!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:02
Joined
May 21, 2018
Messages
3,622
Did you try requerying the listbox?
 

BeardedSith

Member
Local time
Today, 07:02
Joined
Feb 5, 2020
Messages
73
Did you try requerying the listbox?
Yep, tried that, too. Here's what I tried:
Code:
Private Sub cmdDelete_Click()
Dim Response As Integer
On Error GoTo cmdDelete_Click_Err

  DoCmd.SetWarnings False
  If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acNewRec
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
    Me.lstCustomers.Requery
    Me.lstCustomers.Value = ""
    Me.txtFilter.Value = ""
  End If

cmdDelete_Click_Exit:
    Exit Sub

cmdDelete_Click_Err:
    MsgBox Error$
    Resume cmdDelete_Click_Exit
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:02
Joined
May 21, 2018
Messages
3,622
Maybe try adding a me.repaint after the requery? However, you do need a requery. Not that it should make a difference, but you could also try
Me.lstCustomers.rowsource = me.lstcustomers.rowsource
 

BeardedSith

Member
Local time
Today, 07:02
Joined
Feb 5, 2020
Messages
73
Maybe try adding a me.repaint after the requery? However, you do need a requery. Not that it should make a difference, but you could also try
Me.lstCustomers.rowsource = me.lstcustomers.rowsource
Same problem, unfortunately :(
Code:
Private Sub cmdDelete_Click()
Dim Response As Integer
On Error GoTo cmdDelete_Click_Err

  DoCmd.SetWarnings False
  If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.GoToRecord , , acNewRec
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
    Me.lstCustomers.RowSource = Me.lstCustomers.RowSource
    Me.lstCustomers.Value = ""
    Me.txtFilter.Value = ""
  End If

cmdDelete_Click_Exit:
    Exit Sub

cmdDelete_Click_Err:
    MsgBox Error$
    Resume cmdDelete_Click_Exit
End Sub

Should I try to requery at a different point in this process?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:02
Joined
May 21, 2018
Messages
3,622
Can you post the db? I would think a requery would fix this. Usually you will se a delete when you delete records in the rowsource but have not done the requery..
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:02
Joined
May 21, 2018
Messages
3,622
No access for google drive from here. Can you zip and post here instead?
 

BeardedSith

Member
Local time
Today, 07:02
Joined
Feb 5, 2020
Messages
73
It's not letting me, says the file is too large. The ZIP is only 1.9mb.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:02
Joined
May 21, 2018
Messages
3,622
I demoed this and the requery worked for me, so you have something more going on. Maybe move this up to right after the delete record.
Code:
    Me.lstCustomers.requery
    Me.lstCustomers.Value = ""
 

BeardedSith

Member
Local time
Today, 07:02
Joined
Feb 5, 2020
Messages
73
I moved it up right after the Delete and I'm getting the same problem.

Code:
Private Sub cmdDelete_Click()
Dim Response As Integer
On Error GoTo cmdDelete_Click_Err

  DoCmd.SetWarnings False
  If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    Me.lstCustomers.Requery
    Me.lstCustomers.Value = ""
    DoCmd.GoToRecord , , acNewRec
    If Me.Dirty = True Then
        Me.Dirty = False
    End If

    Me.txtFilter.Value = ""
  End If

cmdDelete_Click_Exit:
    Exit Sub

cmdDelete_Click_Err:
    MsgBox Error$
    Resume cmdDelete_Click_Exit
End Sub
1583168672929.png
 

Users who are viewing this thread

Top Bottom