Filter a combobox (1 Viewer)

BJF

Registered User.
Local time
Today, 15:01
Joined
Feb 19, 2010
Messages
133
Hello,

I have a form with a combobox that selects records.

Is there a way to filter the combobox based on the form filter?

For example:

My form holds a recordset of 100 records, and without any filter applied, my combobox also holds 100 choices......

I want my combobox to reflect the curent recordset even when filtered, so if i use the filter by selection on a field which might narrow the recordset down to 10 records, i then want my combobox to only have those 10 records instead of the non-filtered full amount of 100 records.

Sorry if this is confusing! Hopefully someone out there understands what i am trying to do and can help!

Thanks,
Brian
 

shafiq-yasin

New member
Local time
Today, 19:01
Joined
Mar 30, 2018
Messages
6
Hello,



I have a form with a combobox that selects records.



Is there a way to filter the combobox based on the form filter?



For example:



My form holds a recordset of 100 records, and without any filter applied, my combobox also holds 100 choices......



I want my combobox to reflect the curent recordset even when filtered, so if i use the filter by selection on a field which might narrow the recordset down to 10 records, i then want my combobox to only have those 10 records instead of the non-filtered full amount of 100 records.



Sorry if this is confusing! Hopefully someone out there understands what i am trying to do and can help!



Thanks,

Brian



You need to go in rowsource in which combo is retrive and then select two fields from table one which is your combo data and second to which you filter tha record. And in critera field put your required criteria
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:01
Joined
May 7, 2009
Messages
19,169
This is a follow up to mr.shafiq answer:

What is the rowsource of the combo? If the first column is like ID, can it be found on the form?
 

BJF

Registered User.
Local time
Today, 15:01
Joined
Feb 19, 2010
Messages
133
Hi-

The rowsource of my combo is the following: (i used the wizard to create it to select records on my form)


SELECT tblStandardCost.ProductNum AS [Product#], tblStandardCost.Category, tblStandardCost.Description, tblStandardCost.CustNum AS [Customer#] FROM tblStandardCost ORDER BY tblStandardCost.ProductNum;
 

shafiq-yasin

New member
Local time
Today, 19:01
Joined
Mar 30, 2018
Messages
6
This is a follow up to mr.shafiq answer:

What is the rowsource of the combo? If the first column is like ID, can it be found on the form?



When you creat a field combo box it ask from where you need select the records here you select your record.
After that on form you need to go that combo proprty and on data options there is rowsurce open that source and select 2 fields but one should be not shown and also filtered by recquied criteria. Remember don’t select primary key
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:01
Joined
May 7, 2009
Messages
19,169
you add code the the Form's ApplyFilter Event:
replace the names of table, comboname in the code.
Code:
Const COMBO_SOURCE As String = "SELECT ProductNum As [Product#], Category, " & _
    "Description, CustNum As [Customer#] " & _
    "FROM tblStandardCost"
    
Const ORDER_BY As String = "ORDER BY ProductNum"

'! REPLACE YourTableName with correct table in the FORM
Const TABLE_NAME As String = "YourTableName"

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    
    Dim rs As String
    
    If ApplyType = 1 Then
        
        rs = "SELECT ProductNum FROM " & TABLE_NAME & " Where " & Replace(Me.Filter, "_", "")
        
        '! REPLACE YourComboName with correct combo name
        Me.YourComboName.RowSource = COMBO_SOURCE & " WHERE ProductNum IN (" & _
            rs & ") " & ORDER_BY
    Else
    
        '! REPLACE YourComboName with correct combo name
        Me.YourComboName.RowSource = COMBO_SOURCE & " " & ORDER_BY
        
    End If
    
    Me.YourComboName.Requery

End Sub
 

Users who are viewing this thread

Top Bottom