Delete the selected row from a listbox

[faz848]

On my form i have several listboxes. I would like to delete the selected row. I have a table/query rowsource for each listbox. How do i go about doing this?

 

[TheAceMan1]

How are ya faz848 . . .

The easiest way I can think of:
[ol][li]Add a [blue]Checkbox[/blue] to the underlying table of the listbox SQL (name it [blue]Hide[/blue]). Set the [blue]DefaultValue[/blue] property of the textbox to [blue]False[/blue].[/li]
[li]Include the Checkbox field in the SQL of the listbox and sets its criteria to false (don't forget to set its [blue]column width[/blue] property to zero in the form.[/li]
[li]Now the listbox is setup to show only those records where [blue]Hide[/blue] is false.[/li]
[li]Now its a simple matter of an [blue]Update SQL[/blue] to check the right checkbox in the table. Criteria in the [blue]Update SQL[/blue] usually pings on the primarykey of the table ... comparing table PK with listbox PK.[/li]
[li]A final requery of the listbox updates its recordsource.[/li]
[li][blue]Thats It![/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Here is another approach. You can run this method at form open to convert the query to a value list.

Private 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

Then use the ".removeItem" method of a listbox to remove the selected item.