How do you clear a listbox?

[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.

 

[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

 

[moke123]

@MajP - Is there any advantage to using a dao recordset with a listbox?

 

[MajP]

@moke123, there are times where this makes coding easier. Imagine you have a form where you use the built in access features in data sheet view to filter and sort the records. You can then simply pass the reocordset to a listbox without trying to rebuild a Sql string based on the FilterBy and OrderBy properties. If you are working with an external db like SQL Server and you are not using linked tables, you can build an ADO recordset and bind to the listbox.
I use this a lot in my FAYT listboxes and comboboxes. It provides me a lot of flexibility since I do not to know antying about the rowsource or have to mess with a rowsource and try some complex string manipulation. You can apply a Filter to a DAO recordset as easy as you can a form. This is not as efficient, but makes the code very flexible to work with any rowsource.

 

[moke123]

Interesting. Thanks.

 

[MajP]

In the FAYT. I store the listbox's original recordset based on the original rowsource when I create the FAYT. You can then apply the filter to the original recordset and then use the recordset to create a new recordset. You are use to seeing SomeDb.OpenRecordset but you can also do SomeRecordset.OpenRecordset.

Recordset.OpenRecordset method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Set rsTemp = mRsOriginalList.OpenRecordset(dbOpenDynaset)
rsTemp.Filter = StrFilter
Set rsTemp = rsTemp.OpenRecordset

MRsOriginalList is the recordset the listbox/combobox has to start with when you open the form
RsTemp is a new local recordset = the mRsOriginalList
before opening RSTemp I set the filter
Then use the Original recordset to open the new recordset with a filter.

This just makes it very simple to apply the filter without worrying about how to parse the new string and handling things where the sql might have an Order By clause or worse built on a crosstab.

However, this is the problem that was seen. You can get a conflict doing this. Your recordsource and recordset are out of synch. That is why you solution to first set the recordsource "" or mine to requery ensures they are then synched. If you set the recordset to nothing and do not modify the rowsource to "" the old rowsource will win out and reassign the "nothing" to the old recordsource.