Help with customizing report using VBA (an looping through weekdays)

[Lizo Consulting]

Background: I am working on a database to track children who attend a
preschool, which classroom(s) they are assigned to, and which days of
the week they attend. Students can attend any combination of classrooms
or days of the week.

I am currently working on a report that will list the children who
attend on a particular day (it will be used as a sign in sheet by the
parents). The report works great, but I am trying to avoid creating 5
different versions of it (one for each day of the week, where the
"MONDAY report" would show where [mon] = true, etc).

I'm hoping to be more slick and use the one report to programmatically
change the content based on the day of the week (stored in a separate
table).

Key applicable data to this problem are:

Report based on [tblSchedule]
studentID (looks up name & other info in separate table)
classroomID
Mon - y/n
Tue - y/n
Wed - y/n
Thur - y/n
Fri - y/n

There is a separate table [lstWeekdays] that stores the weekdays Monday
- Friday and their associated abbreviations (mon - fri). The "weekdays"
will be displayed on the report header and the abbreviations would be
used to generate the filter for the report (e.g. [mon] = true).

I would appreciate assistance with help with code to loop through my
days of the week table and customize the content shown on the report
based on the day of the week. I am not too proficient with code, but I
do my best. This problem is just too much for me....

Ideally this code will:

1) Loop through the list of weeks & abbreviations from the [lst
weekdays] table
2) Open my report with the filter "where [mon] = true" (I know how to
do this part)
3) Print the day of the week at the top of the report (i assume, by
setting the value of an unbound textbox?)
4) list the children who attend that day of the week (my report already
does this, based on the filter)

Report will be called directly to the printer (though having a preview
might be nice, too)
In the end, I would have printed out 5 copies of the original report,
one for each day of the week....

Any ideas, suggestions? I am pretty experienced with access, but not so
great at heavy duty coding, looping, recordsets etc.

Thanks much in advance for your assistance!

 

[Duane Hookom]

Without looking too far, I would change this:
studentID (looks up name & other info in separate table)
classroomID
Mon - y/n
Tue - y/n
Wed - y/n
Thur - y/n
Fri - y/n
Into a normalized table structure like:
StudentID
Wkday (numbers 1-6 for Mon-Fri)
Each different day of the week will create a new record in the table. For
instance student 72 who attends on M,W, & F will generate these records:
72 2
72 4
72 6

 

[Lizo Consulting]

Duane, I know I should normalize the Table, but using the y/n fields
improves the user's work flow. Not ideal, but makes so many other of
the user's requirement's easier to handle.

I guess my real question is can I use VBA to loop through my report 5
times and put a different day of the week at the top of the report?
Where would the looping code live? In the report? On the form that I
use to select customizing data and call the report?

Liz

Without looking too far, I would change this:
studentID (looks up name & other info in separate table)
classroomID
Mon - y/n
Tue - y/n
Wed - y/n
Thur - y/n
Fri - y/n
Into a normalized table structure like:
StudentID
Wkday (numbers 1-6 for Mon-Fri)
Each different day of the week will create a new record in the table. For
instance student 72 who attends on M,W, & F will generate these records:
72 2
72 4
72 6

--
Duane Hookom
MS Access MVP

Background: I am working on a database to track children who attend a
preschool, which classroom(s) they are assigned to, and which days of
the week they attend. Students can attend any combination of classrooms
or days of the week.

I am currently working on a report that will list the children who
attend on a particular day (it will be used as a sign in sheet by the
parents). The report works great, but I am trying to avoid creating 5
different versions of it (one for each day of the week, where the
"MONDAY report" would show where [mon] = true, etc).

I'm hoping to be more slick and use the one report to programmatically
change the content based on the day of the week (stored in a separate
table).

Key applicable data to this problem are:

Report based on [tblSchedule]
studentID (looks up name & other info in separate table)
classroomID
Mon - y/n
Tue - y/n
Wed - y/n
Thur - y/n
Fri - y/n

There is a separate table [lstWeekdays] that stores the weekdays Monday
- Friday and their associated abbreviations (mon - fri). The "weekdays"
will be displayed on the report header and the abbreviations would be
used to generate the filter for the report (e.g. [mon] = true).

I would appreciate assistance with help with code to loop through my
days of the week table and customize the content shown on the report
based on the day of the week. I am not too proficient with code, but I
do my best. This problem is just too much for me....

Ideally this code will:

1) Loop through the list of weeks & abbreviations from the [lst
weekdays] table
2) Open my report with the filter "where [mon] = true" (I know how to
do this part)
3) Print the day of the week at the top of the report (i assume, by
setting the value of an unbound textbox?)
4) list the children who attend that day of the week (my report already
does this, based on the filter)

Report will be called directly to the printer (though having a preview
might be nice, too)
In the end, I would have printed out 5 copies of the original report,
one for each day of the week....

Any ideas, suggestions? I am pretty experienced with access, but not so
great at heavy duty coding, looping, recordsets etc.

Thanks much in advance for your assistance!

 

[Duane Hookom]

Create a normalizing union query and use it as the source for your report. I
try not to let screens affect my table structure. I may need to temporarily
un-normalize for data entry or use code to load an unbound form.]
--
Duane Hookom
MS Access MVP

Duane, I know I should normalize the Table, but using the y/n fields
improves the user's work flow. Not ideal, but makes so many other of
the user's requirement's easier to handle.

I guess my real question is can I use VBA to loop through my report 5
times and put a different day of the week at the top of the report?
Where would the looping code live? In the report? On the form that I
use to select customizing data and call the report?

Liz

Without looking too far, I would change this:
studentID (looks up name & other info in separate table)
classroomID
Mon - y/n
Tue - y/n
Wed - y/n
Thur - y/n
Fri - y/n
Into a normalized table structure like:
StudentID
Wkday (numbers 1-6 for Mon-Fri)
Each different day of the week will create a new record in the table. For
instance student 72 who attends on M,W, & F will generate these records:
72 2
72 4
72 6

--
Duane Hookom
MS Access MVP

Background: I am working on a database to track children who attend a
preschool, which classroom(s) they are assigned to, and which days of
the week they attend. Students can attend any combination of classrooms
or days of the week.

I am currently working on a report that will list the children who
attend on a particular day (it will be used as a sign in sheet by the
parents). The report works great, but I am trying to avoid creating 5
different versions of it (one for each day of the week, where the
"MONDAY report" would show where [mon] = true, etc).

I'm hoping to be more slick and use the one report to programmatically
change the content based on the day of the week (stored in a separate
table).

Key applicable data to this problem are:

Report based on [tblSchedule]
studentID (looks up name & other info in separate table)
classroomID
Mon - y/n
Tue - y/n
Wed - y/n
Thur - y/n
Fri - y/n

There is a separate table [lstWeekdays] that stores the weekdays Monday
- Friday and their associated abbreviations (mon - fri). The "weekdays"
will be displayed on the report header and the abbreviations would be
used to generate the filter for the report (e.g. [mon] = true).

I would appreciate assistance with help with code to loop through my
days of the week table and customize the content shown on the report
based on the day of the week. I am not too proficient with code, but I
do my best. This problem is just too much for me....

Ideally this code will:

1) Loop through the list of weeks & abbreviations from the [lst
weekdays] table
2) Open my report with the filter "where [mon] = true" (I know how to
do this part)
3) Print the day of the week at the top of the report (i assume, by
setting the value of an unbound textbox?)
4) list the children who attend that day of the week (my report already
does this, based on the filter)

Report will be called directly to the printer (though having a preview
might be nice, too)
In the end, I would have printed out 5 copies of the original report,
one for each day of the week....

Any ideas, suggestions? I am pretty experienced with access, but not so
great at heavy duty coding, looping, recordsets etc.

Thanks much in advance for your assistance!