#Deleted issue with listbox - requery not working :(

[BeardedSith]

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:

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]

Did you try requerying the listbox?

 

[BeardedSith]

Did you try requerying the listbox?

Yep, tried that, too. Here's what I tried:

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]

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]

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

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]

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..

 

[BeardedSith]

I have a copy on my Google Drive here: https://drive.google.com/open?id=1hU14aG_SdoHt_jEAGck1Xvzfng9Svsf7
It's two files, one is the front-end, other is the back-end. Open, attach back-end via prompts and it's frmRewards. If you have any quesitons, let me know. Thanks in advance for looking into this for me, it's very much appreciated!

 

[MajP]

No access for google drive from here. Can you zip and post here instead?

 

[BeardedSith]

It's not letting me, says the file is too large. The ZIP is only 1.9mb.

 

[MajP]

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.

    Me.lstCustomers.requery
    Me.lstCustomers.Value = ""

 

[BeardedSith]

I moved it up right after the Delete and I'm getting the same problem.

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