Need help with Dynamic Timeline Report

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

I will try to explain this as clear as possible. Forgive me if I do a
poor job. I am working on a project for a TV studio in Los Angeles. I
can not submit a copy since I signed a legal non-disclosure contract.
The report I need to create appears similar to a TV guide style layout.

Horizontally across the top of the page are 30 minute time block
headings for primetime from 8:00pm-11:00. There are network names as
row headings on the left, like ABC, NBC, etc. Each block of network
rows is in a larger date group for each day of the week. For a given
network's time slot from 8:00pm-11:00pm., I need to put the name of the
program that will air in that slot. If the show is 30 minutes, it takes
one slot, if it is one hour or two, etc. it takes up as many slots as
needed. The program info needs to be inside gridlines so that each box
is drawn with an outline around the program info. The backcolor and
font need to be formatted based on certain flags in the database.

8:00-8:30 8:30-9:00 9:00-9:30 9:30-10:00 10:00-10:30
__________________________________________________________
ABC |show 1 | show 2 |No Show |
----------------------------------------------------------

So far I have used crosstab queries and a timeline style report that
uses twips to position the programs in the right place. That much works
just fine.

Here are the problems caused by my current approach:

Each program box needs to be formatted based on certain conditions but
the report is formatting the whole row. I am using VBA code to set the
formatting. If there is no data for a certain time block the report
does not do anything. No gridlines are printed which I need. A standard
phrase like "No show" needs to be printed in any empty box and that
does not work right.

I believe that the report formatting problems are caused by the fact
that I have turned off the move layout feature for the detail section.
In all of the timeline reports I have seen online, this is how it must
work. I have no idea how to insert the phrase "No Show" in the right
place.

I am considering a different approach now and am wondering which would
be the easiest. I need a crosstab like effect from my data source.
Program names need to be placed physically in time slots for the right
time and network. There will be gaps where there is no program data
entered and then I substitute the "No show" through the query. Should I
create temp tables in Access from Make Table queries? Should I filter
my crosstabs and then create 36 subreports (6 networks times 6 time
slots)?

If anyone can understand my explanation, do you have any suggestions?
 
Hi Duane:

I actually emailed you earlier in the week about this issue. I have
already used the sample ideas from Shawn Gamble's Ship Captain report
in a db that was available on your website. That code helped me layout
the programs in the right time slot.

What I do not know how to solve is printing info in slots that have no
data in the query and formatting the contents of what is in each box
without formatting the whole physical row.
 
Hi Duane:

I actually emailed you earlier in the week about this issue. I have
already used the sample ideas from Shawn Gamble's Ship Captain report
in a db that was available on your website. That code helped me layout
the programs in the right time slot.

What I do not know how to solve is printing info in slots that have no
data in the query and formatting the contents of what is in each box
without formatting the whole physical row.
 
There should be no problem formatting your text boxes individually. They are
each a different record in your report's record source.

I'm not sure how you would print something between your schedule text boxes.
You might create the equivalent of a "Vessel" Header section that is the
same height as the detail section. Fill this section with your "none" text
boxes and set them to send to back. Then add code in this section like:
Me.MoveLayout = False
 
Back
Top