Displaying Certain Records in a Form Based on a Table Value

[brezja]

I've been searching high and low over the internet for a solution to this. I'm by no means an advanced user of Access and have figured out things through trial and error. I have a database that will serve as a member directory for our various Safety Teams. I have the main table done that will hold all of the member data as well as an input form to add things to that table. They are the All Campus With Safety Teams table, query, and form.

What I'm looking to do is to make additional forms for each of our safety teams (CEAT Team Roster, ERT Ream Roster, WARN Team Roster, and CERT Team Roster) so that if the check box is checked on the main input form...and the value for a particular field is "yes" on the main table.....only those records that have the "yes" values would populate on the individual team form would come up so we could view records individually by team.

IE....Someone who is a member of the CEAT team would have the combo box for "CEAT" checked giving them a "Yes" value for that column. All members that have that yes value would then populate on the CEAT Team Roster Form so we could view them individually.



I know that I could just do a query and a report, but I was looking to possibly link pictures so that we could see their faces as well with the profiles. I know it's a Macro or VBA code that I would need to put in to make this happen...

The columns on the main table that I would be working with are titled "ERT", "CEAT", "CERT", "WARN" They are all Yes/No and show up as check boxes on the main form, but text boxes on the table. I would upload a copy of the database...but it's already 11 MB and it won't upload.

[June7]

Did you run Compact & Repair then zip the db? Make copy and remove most of the data.

Can member be only one roster type? If yes then instead of 4 yes/no fields should be one field with 4 possible values.

Regardless, only one form should be required, just apply filter as needed.

[brezja]

Actually...I created a new one with just the essentials of what I'm currently working on (no switchboard, images, or anything)....so this should help. Sorry, new to most of this and I'm open to any suggestions.

I can't just have one roster type, as many of our employees serve on multiple teams. This need to be able to reflect all of the cross overs for these teams.

[orange]

I think you're going to have to Normalize your tables. One table can not satisfy your set up.
I see these at the outset.

Employee
Team
TeamType
TeamMember (junction) [could be called TeamRoster]

[June7]

I suggest one form, [All Campus Safety Personnel] and filter the RecordSource. Options:

1. build in dynamic filtering on the form itself so users can choose filter criteria after the form opens

2. open the form from another form with filter criteria applied


BTW, recommend no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be EmpID or Emp_ID.

[brezja]

Thanks for the advice, but I found another work around. I made a query on the main table with the criteria that the value for one of the teams must = True. Then I re bound the form to that query and now every time I open it, it only displays the results for that team. I love that feeling of overcoming something that's been in your way for a few days. Thanks guys for the kind help, I'll definitely keep your suggestions in mind for future reference, I'm sure they'll come in handy sooner than later!