One to many subform design advice request

[Richard]

I have a DB with a 1 to many relationship. On the "many" side, there's
a unique "day number" field with possible values of 1 to 7.

For each record on the "one" side, there's always a record for day 1 on
the many side, but all the others may be missing or not.

The main form is designed to always show one line for each of the 7
days, even is there's no data for it. This way, a given day will
always be displayed at the same place on the form.

Since all days have the same layout, I designed a non-continuous
"sfmDay" subform which I'd like to use for all days, each one placed in
it's own main form's subform control.

I'd like to get advice on the best way to setup the record sources,
source objects, linked fields, filters, etc.. of the form and
subforms, either on design mode, in VBA or both to achieve this.

Thanks !

 

[KML]

I have a DB with a 1 to many relationship. On the "many" side, there's
a unique "day number" field with possible values of 1 to 7.

For each record on the "one" side, there's always a record for day 1 on
the many side, but all the others may be missing or not.

The main form is designed to always show one line for each of the 7
days, even is there's no data for it. This way, a given day will
always be displayed at the same place on the form.

Since all days have the same layout, I designed a non-continuous
"sfmDay" subform which I'd like to use for all days, each one placed in
it's own main form's subform control.

I'd like to get advice on the best way to setup the record sources,
source objects, linked fields, filters, etc.. of the form and
subforms, either on design mode, in VBA or both to achieve this.

Thanks !

Richard,

Let me check to make sure I understand what you're saying.

You have a table with 7 records, one for each day of the week. For
arguments sake, let's say your other side of the table is for Classes
(like school classes).

From your description, it sounds like what you are saying is that a

Class will always have at least one day but could have more than one
day. If this is the case, what you have here is a Many To Many
relationship, not a One to Many. For many to many relationships, you
must have 3 tables to represent the relationship appropriately.

If I'm right in my Assessment, let me know.

 

[Richard]

Thanks for your reply.

Unfortunately, it seems I was a bit unclear because it is actually a
one-to-many relationship.

To keep things simple, let's say it's a weekly timesheet summary.

For each weekly timesheet there may be up to 7 summary records (no more
than one per day). At least one record exists for each existing
timesheet.

Each summary record contains fields containing the total time the spent
in different categories (work on projects, education, administration,
vacation, etc..).

This can be summarized in the following tables definitions:

tblSummary
EmpNo
WeekNo
...

tblSummaryDaily
EmpNo
WeekNo
DayNo
Hours_Project
Hours_Education
Hours_Administration
Hours_Vacation
...


Since all lines have the same layout, I created a subform aimed to
contain one day's layout - or one record in tblSummaryDaily.

What I want to do is to design a form where all 7 days are displayed in
their own place, whether they have a corresponding tblSummaryDaily
record or not. In that latter case, the fields would simply remain
empty - and not editable.

What I'm thinking is to put 7 times the same subform on the form and to
use the different availiable control/subsorm properties to put the
right data on the right place.

Is it clearer now ?


KML said:

 

[KML]

Richard,

I apologize for the delay in getting back to you, I just got back from
vacation. I do understand your scenario better now and see where you
are coming from. I believe you are on the right track with your seven
different subform approach. Your form probably needs to calculate what
the current week is OnLoad by using the "Date()" function and then load
the data into the seven different subforms one at a time. Someone else
may be able to think of a more "efficient" way to do this, but off the
top of my head this is the easiest I can think of. How about one
Parameter-Based query for the summary records where the missing
parameter is the date of the summary. In the form, load this query as
many times as necessary into the subforms, passing the appropriate date
in to the parameter query one at a time. Hopefully this makes sense to
you, if you need more assistance please let me know!

Kris

 

[Michel S.]

Thanks for your reply.

Better late than never ! Hope you had pleasant vacations.

I finally solved my problem by using a variation on the approach you
are suggesting.

I assigned to each subform forms Record source it's own query selecting
all records where day number = line number and in the main form current
event, I set each subform's form filter to the current "employee/week"
pair of the main form (actually, I "find first" in the subform form
recordset, which is much faster). If NoMatch, just set all fields to
Visible = No.

I don't see a performance hit at all -- actually, each timesheet main
form and all its subforms load in less than .07 sec.

Thanks again !


KML a présenté l'énoncé suivant :

 

[KML]

Excellent!

I'm glad you were able to work it out!