Deleting a row in a list box

[faz848]

Does anyone know how to delete a selected row in a list box. My listbox has a row source type of table/query. At the moment when i try to delete the selected row all the data is removed and shows the query that was used to populate the listbox details instead...

 

[Remou]

You need to delete from the table, or else alter the query to exclude the selected record.

 

[TheAceMan1]

How are ya faz848 . . .

Delete from the table and update the listbox with a requery:

[blue] Me!ListboxName.RowSoure = Me!ListboxName.RowSoure[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[faz848]

Hey, i cant delete the record from the table. I need to do it so when the selected row is clicked and i press the delete button it automatically deletes it from the table as well as the row..

 

[Remou]

In that case use SQL, for example:

strSQL="DELETE FROM tblTable WHERE ID=" & Me.lstList
CurrentDB.Execute strSQL, dbFailOnError
Me.lstList.Requery

 

[MajP]

I do it a little differently. I take the row source and convert to a value list. Then I can use the remove item method.

Option Compare Database

Private Sub Form_Load()
  Call convertToValueList(Me.lstOne)
End Sub

Public Sub convertToValueList(theListBox As Access.ListBox)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim fldField As DAO.Field
  Dim strLstValue As String
  Dim intColCount As Integer
  Dim intColCounter As Integer
  Dim intRowCounter As Integer
  If theListBox.RowSourceType = "Table/Query" Then
    intColCount = theListBox.ColumnCount
    strSql = theListBox.RowSource
    theListBox.RowSource = ""
    Set rs = CurrentDb.OpenRecordset(strSql)
    theListBox.RowSourceType = "Value List"
    Do While Not rs.EOF
       For intColCounter = 0 To intColCount - 1
          strLstValue = strLstValue & """" & CStr(Nz(rs.Fields(intColCounter), " ")) & """;"
       Next intColCounter
       intRowCounter = intRowCounter + 1
       rs.MoveNext
       strLstValue = Left(strLstValue, Len(strLstValue) - 1)
       theListBox.AddItem (strLstValue)
       strLstValue = ""
    Loop
 End If
End Sub

Private Sub lstOne_Click()
  Call deleteSelection(Me.lstOne)
End Sub

Public Sub deleteSelection(theList As Access.ListBox)
  theList.RemoveItem (theList.ListIndex)
End Sub

 

[Remou]

MajP
I cannot see where this method deletes from the table?

 

[faz848]

I used Remou way of doing it as it looked easier and seems to work fine!

Thank You!

 

[MajP]

Sorry, I did not read the whole post carefully.

i cant delete the record from the table...

I interpreted this to mean he did not want to delete from the table only the listbox.

Anyways I find the above very handy for turn a query row source into a value list so you can and delete easily.

 

[TheAceMan1]

faz848 . . .

In the forms [blue]After Del Confirm[/blue] event:

[blue]   If Status = acDeleteOK Then
      Me!ListboxName.RowSoure = Me!ListboxName.RowSoure
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886