Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-26-2018, 07:21 AM   #1
BJF
Newly Registered User
 
Join Date: Feb 2010
Posts: 72
Thanks: 20
Thanked 0 Times in 0 Posts
BJF is on a distinguished road
Filter a combobox

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

BJF is offline   Reply With Quote
Old 04-26-2018, 07:28 AM   #2
shafiq-yasin
Newly Registered User
 
Join Date: Mar 2018
Posts: 6
Thanks: 2
Thanked 1 Time in 1 Post
shafiq-yasin is on a distinguished road
Re: Filter a combobox

Quote:
Originally Posted by BJF View Post
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
shafiq-yasin is offline   Reply With Quote
Old 04-26-2018, 07:46 AM   #3
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,989
Thanks: 54
Thanked 1,923 Times in 1,837 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Filter a combobox

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?

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-26-2018, 07:50 AM   #4
BJF
Newly Registered User
 
Join Date: Feb 2010
Posts: 72
Thanks: 20
Thanked 0 Times in 0 Posts
BJF is on a distinguished road
Re: Filter a combobox

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;
BJF is offline   Reply With Quote
Old 04-26-2018, 07:55 AM   #5
shafiq-yasin
Newly Registered User
 
Join Date: Mar 2018
Posts: 6
Thanks: 2
Thanked 1 Time in 1 Post
shafiq-yasin is on a distinguished road
Re: Filter a combobox

Quote:
Originally Posted by arnelgp View Post
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
shafiq-yasin is offline   Reply With Quote
Old 04-26-2018, 10:04 AM   #6
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,989
Thanks: 54
Thanked 1,923 Times in 1,837 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Filter a combobox

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
combobox , filter , form

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter a combobox in a subform with another combobox from main form jjake Forms 3 12-18-2015 05:44 AM
filter combobox based on another combobox vb.net spider2vb VB.NET 0 10-06-2013 11:49 PM
Filter One Combobox based on selection in another combobox rachelkm2 Modules & VBA 4 12-13-2010 09:48 PM
Filter Subform ComboBox based on Master Form ComboBox selection azammalick Forms 1 12-16-2009 12:27 PM
How can I filter one combobox based on another combobox selection? aeleech Forms 2 04-26-2005 08:45 AM




All times are GMT -8. The time now is 11:58 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World