BRDemos ShipRequiredDates Question

[epete367]

I finally ALMOST have a Shipping Calendar completed using Duane's
BRDemos as a guide. My current problem is in formatting. My calendar
only boxes around the subreport's data. I have set Duane's 'Text23'
border style to solid which gives me a border around the day of the
week. I have 'txtDay0' set to transparent and the
Report.srptShipRequired set to transparent. What changes do I need to
make to have the calendar box around each date with the subreport data
included within? Thank you!

 

[Duane Hookom]

Check the code in the On Print event of the Detail Section. This code loops
through the seven subreport controls to determine which grows to the largest
height. Then, the code uses the Line method to draw 8 vertical lines outside
and between the subreports using the maximum height.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim lngMaxHeight As Long
Dim i As Integer
lngMaxHeight = Me.txtDay0.Height
'find the maximum height
For i = 0 To 6
If lngMaxHeight < Me.txtDay0.Height + _
Me("srptShipRequired" & i).Height Then
lngMaxHeight = Me.txtDay0.Height + _
Me("srptShipRequired" & i).Height
End If
Next
'draw line on right side
Me.Line (Me.Width, 0)-(Me.Width, lngMaxHeight)
'draw lines on left side of each "day" text box
For i = 0 To 6
Me.Line (Me("txtDay" & i).Left, 0)- _
(Me("txtDay" & i).Left, lngMaxHeight)
Next
End Sub

 

[epete367]

I am closer. I now have all of the vertical lines. However, I don't
have the horizontal lines.
My data (under a date) still shows as a separate box. Thanks again for
the help!

 

[Duane Hookom]

The sample report included the horizontal lines as 0 height Rectangle
Controls from the tool box.

I don't know what you mean by "My data (under a date) still shows as a
separate box".

 

[epete367]

You are indeed a genius. I missed the rectangle controls when I was
trying to replicate. It looks wonderful! Now I am working on an earlier
problem that you suggested corrections for.

 

[epete367]

Duane,
Any idea why the line on the far right of the calendar is farther than
'Saturday'? Text7 and txtDay6 (in the page header and detail section
respectively) are set to the same size and aligned. Also, I am not
getting the line of the rectangle for the bottom box of the calendar.

 

[Duane Hookom]

The "line on the far right" is drawn at the right margin of the report. You
can modify the code to draw it anywhere you want.

I don't know what you mean by "bottom box of the calendar". Can you provide
a position in a section?

 

[epete367]

I will try modifying where the right line is drawn. By the bottom of
the calendar I mean a line across the entire bottom row of the page.
Sorry for all of the trouble. You have been a tremendous help. I am
trying to help a friend who opened a home-based dessert business. I
didn't realize what I had gotten myself into when I told her I would do
a start-up db until she had funds for professional design. Thanks again!

 

[Duane Hookom]

The horizontal line is a rectangle control (Box31) at the bottom of the
detail section.

 

[epete367]

Thank you again for the help and support you offered. The report looks
better than I thought I could have ever done. I appreciate your
patience with a frustrated novice who got in over her head trying to
help a friend. Now if I can figure out my multiple date issue I can
almost put this to an end (after two months of trying)!

 

[Duane Hookom]

What do you mean by multiple date issue? What is the record source of your
main report?

 

[epete367]

Some time ago in a post you pointed out that my data was not normalized
regarding ShipDate information. (As I said I have gotten in over my
head trying to help a friend (it might not help that I am a bit of a
perfectionist)). I have a db with regular orders and special orders.
Special orders can be mailed multiple times during the year (3,6,9,12).
Ultimately I want a Shipping Calendar Report. Which I have now (thanks
to you!) for regular orders. There are 4 main tables that contain all
order info: Orders (OrderID is pk), OrderDetails(OrderID & ProductID
are pk), OrdersSpecialProductDetails (SpecialOrderID is pk) and
ttblSpecialOrderShipping. Originally I had 12 fields in
OrdersSpecialProductDetails named ShipDate1 - ShipDate12. You pointed
out that was not normalized so I broke the table into two tables. The
new ttbl has fields SpecialOrderID, ShipDate, ShipDateID. Every way I
have tried to bring this ShipDate into my form does not work for
Special Orders. I have been struggling for over 3 weeks and just keep
increasing my level of frustration (especially since I would like to
finally finish this). I have redesigned and redesigned my queries,
relationships, forms and subforms. My latest version which has the
Order form with 2 tabs one for regular orders and one for special
orders. In the special order tab I placed a datasheet view subform with
ShipDate. This almost works. I can see dates I have entered in
ttblSpecialOrderShipping but I cannot enter new dates. Keeping track of
dates to ship is a big concern for my friend so this piece of the db is
critical. I have looked at MS Access books and online but can't find
anything similar short of expending large dollars to purchase a
predesigned db (which she really doesn't have funds for yet). My friend
had her first write-up in a local magazine and is online. Orders are
starting to come in on a faster basis and I am still sitting her with
her db (not a good thing). Thanks again for your support and patience.
I am sure to experts people like me seem dense not being able to put it
all together. I do have to admit that this has been a wonderful
learning experience.

 

[Duane Hookom]

Apparently you have dates that you want to display in the report from
multiple tables/recordsets. My first attempt would be to create a union
query to put all the ship dates into a single recordset. This could then be
used as the record source for your report.

BTW: Your posting would be much easier to read and comprehend if you divided
your thoughts into paragraphs.

 

[epete367]

Sorry, early in the morning I am not at my best.

My issue does not lie in combining the ShipDates. As of now I would be
content to create two seperate calendar reports - one for Orders and
one for Special Orders.
My problem lies somewhere in my form & subform for SpecialOrders. I
cannot create it so that I can enter multiple dates for delivery of
each special order. My original design of 12 fields ShipDate1 -
ShipDate12 obviously allowed separate date entries but was not
normalized. My new design breaking the SpecialOrderDetails table into
two tables rectified that issue. I brought the two tables together in a
query and can see delivery dates (ShipDate) for each SpecialOrder.
However, I cannot create a form that allows me to enter new dates.
I tried placing a subform in. This subform (placed in in datasheet
view) does show dates that I have manually entered into the table for
trial. However, I cannot enter new dates.
Again, I appreciate your patience and persistance in working with me.
At least today I have a brief respite from this db. I work with the
Make-A-Wish Foundation, which grants wishes to children with life
threatening illnesses. Today I have the honor of completing a wish that
I have been working on for 10 months. We restored an old car that he
had. At least I can bring something in my life to completion (even if
it isn't this db!).

 

[Duane Hookom]

Is your subform for special dates read-only? What are your table structures
for the two tables? Are the tables related on primary and foreign key
fields? What are the record sources of the main and subform?

 

[epete367]

Fixed my problem but I am not sure why it worked! Instead of using a
query for the subform I switched to using the ttblSpecialOrderShipping.
Now there are no problems.
Perhaps you know the answer to yet one more question - you seem beyond
knowledgeable about Access.

The report [rptShipRequired] prints a Shipping Calendar based on a
starting and end date selected in a form
[frmCalendarSelect]. This report shows orders that need to be mailed on
specified dates. (Thanks to your help )

I would like to print the corresponding orders that fall within the
specified dates.

I already have a report [Invoice With Discount] that could be printed
for each corresponding order OR I could print the form [Orders] that
corresponds with each order.

Is such a thing possible?

Thanks once again & again & again. I would never be this far without
you.

 

[Duane Hookom]

It seems like you are asking if it is possible to filter a report to a
specific order or orders. Yes this is very possible and gets answered quite
often. Check out Martin Green's web site at
http://www.fontstuff.com/access/index.htm. There is also a solution in the
reports section of www.mvps.org/access/