How to Delete Selected Records From A List Box 1

[Accel45]

I have a list box (GroupBox) set to multiselect.
The fields within the list box are (from tblTickets)
TicketID (AutoNumber)
TicketNumber (Number)
TicketAmount (Currency)


I want to use a command button to deleted all records that are selected within GroupBox.

Help please.

accel45

 

[VBslammer]

Private Sub cmdDelete_Click()
On Error GoTo ErrHandler
  Dim strSQL As String
  Dim strValues As String
  Dim vIndex As Variant
  
  strSQL = "DELETE FROM tblTickets WHERE [TicketID] IN ( {%1} )"
  
  For Each vIndex In Me!GroupBox.ItemsSelected
    If Len(strValues) > 0 Then
      strValues = strValues & ", "
    End If
    strValues = strValues & Me!GroupBox.Column(0, vIndex)
    Me!GroupBox.Selected(vIndex) = False
  Next vIndex
  
  If Len(strValues) > 0 Then
    DoCmd.RunSQL Replace(strSQL, "{%1}", strValues)
    Me!GroupBox.Requery
  End If

ExitHere:
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[jbpez]

Try something like this,

Dim strSQL as string
Dim x as long
Dim lngTicketID as long

For x = 0 To Me.GroupBox.ListCount - 1

   If Me.GroupBox.Selected(x) = True Then

	lngTicketID = Me.GroupBox.Itemdata(x)

	strSQL = "DELETE * FROM tblTickets "
	strSQL = strSQL & "WHERE TicketID=" & lngTicketID

	DoCmd.RunSQL strSQL

   ENd If

Next x

John Borges