locked
Filter Form Dynamically In Access RRS feed

  • Question

  • Hi,

       I'm trying to build a form that can filter which data is loaded or displayed in the form based on a user selection when the form opens. In example: "Which city do you wish to update?" with a list to choose from . When the user selects a city all the other cities are not listed in the form. I'm awful at VBA, so if there's a way to do this in the expression builder it would be best. Failing that, perhaps have a form for each of the cities (there are only 6) but only display records pertaining to one city in each form?

    Thanks,

    Dave

    Friday, November 20, 2015 12:24 AM

Answers

  • Hi Dave,

    Do you mean you want to filter one form with selection? I am not sure where you selected the list, and I assume you use a combobox.

    To achieve your requirement, you could do as below:
    1. Click On Change event of the combobox
    2. Choose Code Builder, and then enter the code to filter the Form, you need to modify the code below according your own form and control.

    Private Sub Combo14_Change()
    Me.Filter = "Name = '" & Me.Combo14.Value & "'"  ‘Name is the field you want to filter
    Me.FilterOn = True
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 20, 2015 3:33 AM
  • Bear in mind that city names can legitimately duplicated, so you should also provide a means of distinguishing them in the combo box's list, e.g. by showing the state or other regional unit in which the city is located.

    For the same reason a referencing table, e.g. one of addresses, should include a numeric CityID or similar foreign key referencing a numeric primary key of a Cities table, which in turn should include a foreign referencing the primary key of a States or similar table.  A form based on the referencing table should therefore be filtered on the numeric key.

    The unbound combo box in the form would be set up as follows:

    Name:             cboFindCity

    RowSource:     SELECT CityID, City & ", " & State
                           FROM Cities INNER JOIN States
                           ON Cities.StateID = States.StateID
                           ORDER BY City, State;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

    You cannot implement the filter by using the Expression builder as you need to execute an action, not build an expression.  In the combo box's AfterUpdate event procedure put the following code:

    If Not IsNull(Me.cboFindCity) Then
        Me.Filter = "CityID = " & Me.cboFindCity
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If

    If a city is selected the form will be filtered to the selected city; if the combo box is cleared to Null all records will be shown.

    Ken Sheridan, Stafford, England

    Friday, November 20, 2015 12:46 PM

All replies

  • Hi Dave,

    Do you mean you want to filter one form with selection? I am not sure where you selected the list, and I assume you use a combobox.

    To achieve your requirement, you could do as below:
    1. Click On Change event of the combobox
    2. Choose Code Builder, and then enter the code to filter the Form, you need to modify the code below according your own form and control.

    Private Sub Combo14_Change()
    Me.Filter = "Name = '" & Me.Combo14.Value & "'"  ‘Name is the field you want to filter
    Me.FilterOn = True
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 20, 2015 3:33 AM
  • Bear in mind that city names can legitimately duplicated, so you should also provide a means of distinguishing them in the combo box's list, e.g. by showing the state or other regional unit in which the city is located.

    For the same reason a referencing table, e.g. one of addresses, should include a numeric CityID or similar foreign key referencing a numeric primary key of a Cities table, which in turn should include a foreign referencing the primary key of a States or similar table.  A form based on the referencing table should therefore be filtered on the numeric key.

    The unbound combo box in the form would be set up as follows:

    Name:             cboFindCity

    RowSource:     SELECT CityID, City & ", " & State
                           FROM Cities INNER JOIN States
                           ON Cities.StateID = States.StateID
                           ORDER BY City, State;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

    You cannot implement the filter by using the Expression builder as you need to execute an action, not build an expression.  In the combo box's AfterUpdate event procedure put the following code:

    If Not IsNull(Me.cboFindCity) Then
        Me.Filter = "CityID = " & Me.cboFindCity
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If

    If a city is selected the form will be filtered to the selected city; if the combo box is cleared to Null all records will be shown.

    Ken Sheridan, Stafford, England

    Friday, November 20, 2015 12:46 PM
  • Woozol, are you saying you want a dependency list or a cascading combo box?

    http://www.fontstuff.com/access/acctut10.htm

    https://www.youtube.com/watch?v=SpMyGlEInGs


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, November 24, 2015 12:45 PM