CODEWITHSUNDEEP
vbams-access
Batterdburrito
Batterdburrito

Reputation: 35

ListBox Retaining Selection Data after Requery

I am currently working with a list box that deletes values containing the selected text from a table. I have the following code:

Private Sub DeleteEntry_Click()

Dim DeleteTbl As String
Dim Msg As String



If IsNull(Me.lstSolution) = True Then
    MsgBox "Please Select a Entry", vbOKOnly, "No Entry Selected"
Else
    DeleteTbl = MsgBox(Msg, vbYesNo, "Delete Entries?")
    Msg = "Are you sure you wish to delete all entrys containing: "
    Msg = Msg & Me.lstSolution
    DeleteTbl = MsgBox(Msg, vbYesNo, "Delete Entries?")
End If

If DeleteTbl = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * FROM [Solutions] WHERE [Solutions].SolutionText = '" & Me.lstSolution & "'"
    DoCmd.SetWarnings True
Else
     'Do Nothing
End If

    Me.lstSolution.Requery

End Sub

The problem i have is that after the entry clears from the table, and i click the button again (without selecting anything) the previously selected value appears in the MsgBox still. I need to clear this value so that an error message box appears, saying that no entry has been selected

Upvotes: 1

Views: 395

Answers (2)

Combinatix
Combinatix

Reputation: 1216

ListBox.Requery does not refresh the ListBox.Value, nor Listbox.Column(index)

You must call lstSolution.SetFocus after lstSolution.Requery. That propagates new values from current recordset to listbox columns.

I'm not sure but I think it is a bug in MS Access.

Upvotes: 0

Batterdburrito
Batterdburrito

Reputation: 35

I have managed to solve the issue by adding in a NotSelected variable as follows:

Private Sub DeleteEntry_Click()

Dim DeleteTbl As String
Dim Msg As String
Dim NotSelected As String

If IsNull(Me.lstSolution) = True Then
    MsgBox "Please Select a Entry", vbOKOnly, "No Entry Selected"
    NotSelected = True
Else
    Msg = "Are you sure you wish to delete all entrys containing: "
    Msg = Msg & Me.lstSolution
    DeleteTbl = MsgBox(Msg, vbYesNo, "Delete Entries?")
    NotSelected = False
End If

If NotSelected = True Then
    End
ElseIf DeleteTbl = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * FROM [Solutions] WHERE [Solutions].SolutionText = '" & Me.lstSolution & "'"
    DoCmd.SetWarnings True
End If

    Me.lstSolution.Requery


End Sub

Upvotes: 0

Related Questions