Clearing Comboboxes and Listboxes

[Deleted member 73419]

Hi,

How do you clear Comboboxes and Listboxes?

I have one of each and they get their data via a RecordSource

I have tried combobox.clear and listbox.clear but I get an error saying 'method or data member not found'.

I have also tried iterating through the items and then using removeitem but I cannot do that where the RowSource property is set to 'Table/Query'

Using combobox.recordsource = "" or listbox.recordsource = "" does not work either as I still have the respective combobox and listboxes populated with their previous data.

Could anyone help please?

 

[Beetle]

Combo and List boxes don't have a Record Source, they have a Row Source, so -

Me!YourCombo.RowSource = ""

should clear the list.

 

[Deleted member 73419]

Combo and List boxes don't have a Record Source, they have a Row Source, so -

Me!YourCombo.RowSource = ""

should clear the list.

oops... my bad spelling. They do indeed have a RowSource

Even when I set RowSource = "" they are still populated. Just for good measure I requeried them and that has no effect either.

Thanks for your reply.

 

[Beetle]

If you're attempting to do this in code then post the exact code that you're using. Also post the exact names of the combo or list boxes involved an an example of the row sources.

 

[Deleted member 73419]

If you're attempting to do this in code then post the exact code that you're using. Also post the exact names of the combo or list boxes involved an an example of the row sources.

Here is the code used.

Option Compare Database
Option Explicit

Private Sub cmbBaseMachineGroup_AfterUpdate()
    
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
       
    On Error Resume Next
        
    Set cn = CurrentProject.Connection
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    
    rs.Open "SELECT DISTINCT PART_ID FROM [dbo_ORDER] WHERE ((BASE = '" & cmbBaseMachineGroup & "') AND (TYPE='MC') AND (SLOT='0'));", cn, adOpenForwardOnly
    
    If (Not rs Is Nothing) Then
        rs.MoveFirst
        If (Not (rs.EOF And rs.BOF)) Then
            Set lstBaseGroupNumber.Recordset = rs
            lblBaseGroupNumberCount.Caption = lstBaseGroupNumber.ListCount & " Records Found"
        Else
            lblBaseGroupNumberCount.Caption = "0 Records Found"
        End If
    End If

    rs.Close
    
    If (Not cn Is Nothing) Then
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
    End If
End Sub

Private Sub cmbBaseMachineGroup_KeyDown(KeyCode As Integer, Shift As Integer)
    
    KeyCode = 0
    
End Sub

Private Sub cmdReset_Click()

    Dim intCount As Integer

    txtBaseMachineNumber.Value = ""
    
    cmbBaseMachineGroup.RowSource = ""
    cmbBaseMachineGroup.Requery
    
    lstBaseGroupNumber.RowSource = ""
    lstBaseGroupNumber.Requery
    
End Sub

Private Sub Form_Load()
    
    txtBaseMachineNumber.Value = ""
    cmbBaseMachineGroup.RowSource = ""
    cmbBaseMachineGroup.Value = ""
    lstBaseGroupNumber.RowSource = ""
    lblBaseMachineGroupCount.Caption = "0 Records Found"
    lblBaseGroupNumberCount.Caption = "0 Records Found"
    
End Sub

Private Sub txtBaseMachineNumber_AfterUpdate()

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
       
    On Error Resume Next
                
    Set cn = CurrentProject.Connection
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    
    rs.Open "SELECT DISTINCT BASE_ID FROM [dbo_ORDER] WHERE ((BASE like '" & txtBaseMachineNumber & "%') AND (TYPE='MC') AND (SLOT='0'));", cn, adOpenForwardOnly
    
    If (Not rs Is Nothing) Then
        rs.MoveFirst
        If (Not (rs.EOF And rs.BOF)) Then
            cmbBaseMachineGroup.RowSource = "Table/Query"
            Set cmbBaseMachineGroup.Recordset = rs
            lblBaseMachineGroupCount.Caption = cmbBaseMachineGroup.ListCount & " Records Found"
        Else
            lblBaseMachineGroupCount.Caption = "0 Records Found"
        End If
    End If

    rs.Close
    
    If (Not cn Is Nothing) Then
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
    End If

End Sub

cmbBaseMachineGroup is the combobox and its rowsource is set in Sub txtBaseMachineNumber_AfterUpdate() and lstBaseGroupNumber is the listbox its rowsource is set in Sub cmbBaseMachineGroup_AfterUpdate().

The trouble is in the Sub cmdReset_Click(). As I previously posted, i've tried 'everything' (it seems) to clear the data in the combo and list boxes.

Thanks

 

[missinglinq]

We may be talking at cross purposes, here! What, exactly, do you mean by 'clear?'

Setting the RowSource = "" removes all selections from them; is that what you're trying to do?

Or are you simply trying to de-select the previously chosen item, leaving them still populated by the data from the RowSource? In that case

Me.YourComboboxName = Null

will do.

For a Listbox with Multi-select set to None

Me.YourListboxName = Null

will do. If Multi-Select is set to Simple or Extended, it's a little more complicated, but not too much.

So, let us know exactly what your intent is, here.

Linq ;0)>

 

[Deleted member 73419]

We may be talking at cross purposes, here! What, exactly, do you mean by 'clear?'

Setting the RowSource = "" removes all selections from them; is that what you're trying to do?

Or are you simply trying to de-select the previously chosen item, leaving them still populated by the data from the RowSource? In that case

Me.YourComboboxName = Null

will do.

For a Listbox with Multi-select set to None

Me.YourListboxName = Null

will do. If Multi-Select is set to Simple or Extended, it's a little more complicated, but not too much.

So, let us know exactly what your intent is, here.

Linq ;0)>

My intent is to completely clear the data within the combo and list boxes such that when you click the down arrow on the combobox there is no data it is clear and the listbox is completely empty.

I tried setting RowSource = "" but this didn't have any effect

 

[missinglinq]

Not sure that it matters, but are the cmbBaseMachineGroup and lstBaseGroupNumber Controls Bound or Unbound?

Linq ;0)>

 

[Deleted member 73419]

Not sure that it matters, but are the cmbBaseMachineGroup and lstBaseGroupNumber Controls Bound or Unbound?

Linq ;0)>

They are both unbound