How do you clear a listbox?

[Deleted member 73419]

Hi,
For clarity, by clear I am meaning that I want to empty the listbox of any previous results - I want a blank listbox.

Set lstResults.Recordset = Nothing

Also doesn't do anything. Bit strange really as all the previous results are still in the listbox even though the recordset is nothing you may have expected it to have cleared out its contents...

 

[Gasman]

Not recordset, rowsource !

 

[Deleted member 73419]

Also, for reference, this is not a multiselect listbox (not sure whether this matters though as I'm not looking to clear the selected entries but clear the entire listbox of everything!!)

 

[Deleted member 73419]

Not recordset, rowsource !

oops

using RowSource I get a "invalid use of property" error.

 

[theDBguy]

Also, for reference, this is not a multiselect listbox (not sure whether this matters though as I'm not looking to clear the selected entries but clear the entire listbox of everything!!)

Can you please tell us what's in the following properties for your Listbox? Thank you.

Name
Row Source
Row Source Type
Bound Column
Column Count
Column Widths
Allow Value List Edits
List Items Edit Form
Inherit Value List
Multi Select

 

[Deleted member 73419]

Can you please tell us what's in the following properties for your Listbox? Thank you.

Name - lstResults
Row Source - (blank)
Row Source Type - Table/Query
Bound Column - 1
Column Count - 3
Column Widths - 2cm;;3cm
Allow Value List Edits - yes
List Items Edit Form - (blank)
Inherit Value List - yes
Multi Select - none

 

[theDBguy]

Thanks. For starters, try changing the Inherit Value List to No.

With the Row Source being blank, can you also post a screenshot of the Listbox showing the items in it?

 

[moke123]

The rowsource is blank yet there are items in the listbox?

Have you tried re-creating the listbox?

 

[Deleted member 73419]

Thanks. For starters, try changing the Inherit Value List to No.

With the Row Source being blank, can you also post a screenshot of the Listbox showing the items in it?

Setting Inherit Value list to no didn't make any difference.

 

[Gasman]

Time to upload the db now I think?

 

[moke123]

I would re-create the listbox to rule out corruption.

 

[Pat Hartman]

There is no reason to empty the RowSource of a listbox. You can always control the value list by modifying the RowSource query. If there are no records selected, the listbox will be empty. Something you are doing makes no sense.

 

[Deleted member 73419]

I'll upload a DB but it will have to be tomorrow now as it is large and i'll need to remove the intellectual data...

 

[Gasman]

All you need is what supplies that listbox, and perhaps the rest of the form.

 

[cheekybuddha]

Are you using code to populate the to populate the listbox in the first place? If so, please post it

 

[Deleted member 73419]

Ok, attached is the offending DB.

Entering "wd-1" in to the textbox and hitting enter will provide some entries in the listbox.

 

[moke123]

Thats a first for me and I use a lot of listboxes. I have never seen a list set to a recordset like that.

All you really need is

Private Sub cmdOk_Click()

    Dim strSQL As String
      
    If (Not IsNull(txtPartNumber.Value)) Then
      
        strSQL = "SELECT PN as [Part No], DE as [Description], MC as [Manufacturers Code] FROM tblPT WHERE ((PN='" & txtPartNumber & "') OR (MC Like '*" & txtPartNumber & "*'));"
        
        lstResults.RowSource = strSQL
        
    End If
    
End Sub

 

[Gasman]

Not sure why I cannot see form view on that form?

ANyway, a quick button and

Private Sub cmdClear_Click()
Me.lstResults.RowSource = ""
Set lstResults.Recordset = Nothing
End Sub

does clear it. Tried commenting out each in turn and it did not.

 

[moke123]

Not sure why I cannot see form view on that form?

their views were set to no in format properties

 

[MajP]

I added a requery after setting the recordset to nothing.

Private Sub cmdOk_Click()

    On Error GoTo Err_cmdOk_Click
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
     
    If (Not IsNull(txtPartNumber.Value)) Then
        Set db = CurrentDb
        strSQL = "SELECT PN as [Part No], DE as [Description], MC as [Manufacturers Code] FROM tblPT WHERE ((PN='" & txtPartNumber & "') OR (MC Like '*" & txtPartNumber & "*'));"
 
        Set rs = db.OpenRecordset(strSQL)
     
        If (Not rs.EOF = True) Then
            rs.MoveLast 'NECESSARY WHEN THERE IS MORE THAN ONE RECORD RETURNED BUT NOT SURE OF IMPLEMENTATION...
          If (rs.RecordCount = 1) Then
            Debug.Print rs.RecordCount & " records found"
            'move on - open form, process etc...
            Set lstResults.Recordset = rs
          ElseIf (rs.RecordCount > 1) Then
            Set lstResults.Recordset = rs
            Debug.Print rs.RecordCount & " records found"
            txtPartNumber.SetFocus
          End If
        Else
          MsgBox "No Records Found"
          Set Me.lstResults.Recordset = Nothing
          Me.lstResults.Requery
        End If
    End If
 
Exit_cmdOk_Click:
    Exit Sub

Err_cmdOk_Click:
    MsgBox err.Description
    Resume Exit_cmdOk_Click
 
End Sub