Filter field on combo box of other field

[bobjackson]

Hi, Thanks for any help I receive in advance.

I am trying to filter a combo box list based on the selection of a previous field.

I have a main table called "tbl_equipment", two of the fields within this table are "building" and "cell". Each building has a number of Cells, each building has different cells.

I have two other tables called "tbl_building" and "tbl_cell"

The tbl_building has the following fields :-

building id (primary key)
building name

The tbl_cell has the following fields :-

cell id (primary key)
cell name
building name

In the main table "tbl_equipment" I pick the building from a combobox linked to "tbl_building", i then go to pick the cell from the next combobox linked to "tbl_cell" however want it to filter on the cell name that are specific the the building selected.

If anyone can help it would be appreciated.

Thanks

Rob


Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[dhookom]

tbl_cell should be storing the building ID values from tbl_building, not the name. I wouldn't go much further until this relationship was fixed.

Duane
Hook'D on Access
MS Access MVP

 

[bobjackson]

Thanks, Duane. Changed tbl_cell to store Building ID value.

Rob

Never smile at work, they may think you're enjoying yourself too much and cut your pay.

 

[dhookom]

I would create a small function in the form's code/vba that changes the Row Source of the "next combobox" based on the current selection of the first combo box. Let's assume combo box names of cboBldgID and cboCellID.

Public Function FilterCell()
    Dim strSQL As String
    [COLOR=#4E9A06]' assumes BuildingID in tbl_cell is numeric[/color]
    strSQL = "SELECT [cell id], [cell name] from tbl_cell " & _
        " WHERE [BuildingID] = " & Me.cboBldgID & _
        " ORDER BY [Cell name]"
    Me.cboCellID.RowSource = strSQL
End Function

You can set the After Update property of the first combo box to:
After Update: =FilterCell()

You would want to set the On Current event of the form to call the same function.

Duane
Hook'D on Access
MS Access MVP

 

[bobjackson]

Thanks Duane works a treat

Regards,

Rob

Never smile at work, they may think you're enjoying yourself too much and cut your pay.