Trying to decide on a form design approach

[EonsTimE]

Hi,
I'm trying to design a planner/weekly calendar form that shows items assigned to several people for each day on one screen.

Here's a schema of how I want to arrange this:

i63.tinypic.com/2reigzs.png

So far I've created a working concept up to PersonA, but can't figure out how to display PersonB/PersonC... below and make the whole thing scrollable.

I'm working with the following design assumptions:
1) The number of people will wary, and I will want to sort the order they appear on list dynamically (using SELECT queries with WHERE conditions)
2) Tasks (Task1, Task2) will be inserted into a 'Day container' as TextBox clones, the number will wary. Tasks will be populated and sorted using SELECT queries that pull data for (DATE & PERSON) and populate each 'Day container accordingly'.


The problem I have is, I can't nest a Continuous subform in another Continuous subform, according to Access limitations. Ideally 'Day containers' would be Continuous sub_forms listing Tasks for the Person. And ideally, PersonA/PersonB/PersonC... would be a continuous form as well, so that you can scroll up and don and view people's tasks by their names.

I think I can give up the Task1/Task2/Task3 container being a continuous form and instead generate the required amount of TextBoxes for Tasks programatically in VBA. Then I will make the main form continuous and be able to scroll and sort the list of people.

Now the questions:
1) Would anyone have a better suggestion on how to plan this arrangement?
2) I couldn't find much code for dynamically creating and destroying text boxes on a sub form, populating them with results of query against each person and date etc - has anyone encountered a code that achieves this?
3) It would be much easier if I could make the main form (People) continuous and nest in it 7 subforms with continuous list of bound TextBoxes - is there maybe a way to do it?

Thank you.

 

[Uncle Gizmo]

>>> I couldn't find much code for dynamically creating and destroying text boxes on a sub form<<<

What about hiding, un-hiding?

 

[EonsTimE]

>>> I couldn't find much code for dynamically creating and destroying text boxes on a sub form<<<

What about hiding, un-hiding?

Hmm, this might work, but I'm still devastated about the continuous form/subform limitations :/

The problems I would face are (my brain is dead atm, so these might be trivial):
1) Preventing the main form from displaying scrollbars if the extra controls that are hidden and not hold data are present. Let's say my form can show 4 People-frames: I'll need to figure out how to programatically count the used People-frames and enable/disable scrollbars if People shown is >4 or <=4

2) Subform-control has a "Link Master Fields" and "Link Child Fields" property, and so far I'm passing only one parameter to my subform-control: it's assigned date. How would it be best to pass two (or more) parameters to a subform-control, ie PersonName and DayDate. Can it be done with only Link Master/Link Child? Maybe Tags? Or need pure and precise VBA approach?

3) If there are a lot of People-frames shown, enabling scrolling the main form contents with MouseWheel. There doesn't seem to be much code to scroll the contents of a form with mouse wheel, unless it's a continuous form and utilizing MoveNext/MovePrevious command. Would I need to empoy a .dll or ActiveX?

4) I still need to decide if I'd want to put in like 10 People-frames and hide the unused ones, or put only 4 People-frames and recycle them by scrolling People's details in- and out- of them. For now I'd prefer to show all filtered People on the form and let the user to scroll through all of them.

 

[Minty]

2) You can use more than one field as a link. Use a semi-colon to separate the fields you want to use.

Your picture link isn't working for me, so struggling a little to visualise your layout / design issues.

 

[CJ_London]

rather than one subform, have several, one for each person.

The main form would be a single form, with unbound control(s) for the link master value(s) down the side. Reason for scrolling is because the number of people wont fit on the screen - but you can mimic this with a scrollbar control moving the values assigned to each of the unbound controls up and down.

Not sure of your access version, but you can also use form mousewheel event to scroll to as well

 

[CJ_London]

another option is to use listboxes for your day containers