TabPages, Subforms and Filtering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. Ok, so here's what I have. I hope you can help me. :-) (I'm working
in '97 btw)

I have several "teams" of engineers within the department, and I want to be
able to see data data for one team on each file tab. I have all information
stored in a table with fields "name", "team", and several fields for
different job assignments. when I work strictly with the subform(s). I can
filter by team and have just the members of team two, (for example) show up.
When I bind the subform to the main form, and open the main form, the subform
shows team one, even if it was deliberately designed and saved to show team
2. Right now, the parent/child value is set to "Team." If I remove it, I
don't get any filtering at all.

Basically, I want to see a roster of each team on my form, with team names
on the file tabs, or some similar way to view my rosters. I don't have a
problem coding controls to change based on imput from elsewhere, I'm just not
certain where I should start, or how to go about doing this.

Thoughts? Opinions? :-) Thanks
 
Hi Amanda.

There are several issues with this design. One is knowing the number of tabs
that you need. Is there a fixed size for your teams? Could a team ever have
more people? For example, if you have 4 tabs, but a team ends up with 5
people, is it okay to just ignore the 5th person?

I take it that you create a Team from individual engineers, and the rosters
are built by assigning the Team to a Shift (date and time). But what happens
if one of the engineers is unavailable? Surely there will be times when
someone is called away, and another is substituted? In that case the actual
person working the shift will not be a part of that team?

Additionally, over time, the teams will change. A person will be part of
Team A at one stage, and Team Z at another.

It seems to me that the Roster table should *store* the individuals assigned
to a shift, rather than a team assigned to a shift. For the purpose of the
interface to create the rosters, you can still pick a team to assign to a
shift, but under the surface it should execute an append query statement to
store the data as individuals.

So, if you have a data structure like this:

tblStaff - one record for each engineer
StaffID AutoNum primary key (pk)
Surname Text
FirstName Text
...

tblTeam - one record for each team.
TeamName Text

tblTeamDetail - one record for each current member of each team.
TeamName Text foreign key(fk) to tblTeam.TeamName
StaffID Number (Long) f.k. to tblStaff.StaffID

tblShift - one record for each regular shift
ShiftID Number (Long) pk
StartTime Date/Time time this shift begins.
Duration Number (Long) Duration of shift in minutes.

tblRoster - one record for each person in a shift.
RosterID AutoNum pk
RosterDate Date/Time date the shift begins
ShiftID Number (Long) f.k. to tblShift.ShiftID
StaffID Number (Long) f.k. to tblStaff.StaffID

Now you could interface this with a main form bound to tblTeam. In the
Current event of the form, open a recordset that retrieves the names of the
current members of the team, and writes their StaffID into hidden text boxes
above each of the pages of the tab control, and their names onto the Caption
of each page of the tab control. The subform in each page will be linked by
to these text boxes (LinkMasterFields) and the StaffID (LinkChildFields).

That may not be a great interface - esp. if the number of people in a team
can vary - but that's how it could be achieved.
 
:-) I figured it out. (silly me).

I needed to create a set of queries (one for each team) to narrow my
Associates table down to just those on a particular team. Then, base a
subform off of the query and place it on the tab control page. I'm going to
plan for 5 teams, and use VBA to turn any unneeded pages invisible.

(alternatively, I could use code to create a querydef, and use a counter to
cycle through making a querydef, subform and tabcontrol page, and placing
them on the main form at the proper coordinates, but I think that's more
effort than I want to go to (useful if the number of teams is large, or
varies drastically from company division to division))

Thanks for giving me food for thought Allen!

Amanda
 
Back
Top