INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Filter field on combo box of other field

Filter field on combo box of other field

(OP)
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.

RE: Filter field on combo box of other field

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

RE: Filter field on combo box of other field

(OP)
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.

RE: Filter field on combo box of other field

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.

CODE --> vba

Public Function FilterCell()
    Dim strSQL As String
    ' assumes BuildingID in tbl_cell is numeric
    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

RE: Filter field on combo box of other field

(OP)
Thanks Duane works a treat

Regards,

Rob

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close