Horizontal Report problem

[Mark Reed]

Hi Guru's,
Hopefully I can explain this OK.....What I am trying to do is
create a QC check sheet using the following fields: ASN, PO & Qty. This
information will be at the top of each report with loads of labels
below which users will have as a check list.
1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
I have set it up so far so that a new page is forced for each ASN. On
each page there should be 1 ASN number, however many PO's that belong to
each ASN and the Qty of units for each PO.
The problem I am having is that I can't work out how to get a list of
PO's (horizontal) across the top. If I put the PO in the header, only
the first one will appear. If I put it in the detail section, the list
is vertical and goes down the page rather than across it which wont work for
this report.

Can this be done?

TIA


Mark

[Mark Reed]

Re: Horizontal Report problem

I'm guessing that from the lack of replies that this is not possible? Could
someone please confirm my suspicions.

Cheers,

Mark

"Mark Reed" <mark.reed75@nt lworld.com> wrote in message
news:FG4Jc.4$sk 6.1@newsfe1-gui.ntli.net...[color=blue]
> Hi Guru's,
> Hopefully I can explain this OK.....What I am trying to do is
> create a QC check sheet using the following fields: ASN, PO & Qty. This
> information will be at the top of each report with loads of labels
> below which users will have as a check list.
> 1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
> I have set it up so far so that a new page is forced for each ASN. On
> each page there should be 1 ASN number, however many PO's that belong to
> each ASN and the Qty of units for each PO.
> The problem I am having is that I can't work out how to get a list of
> PO's (horizontal) across the top. If I put the PO in the header, only
> the first one will appear. If I put it in the detail section, the list
> is vertical and goes down the page rather than across it which wont work[/color]
for[color=blue]
> this report.
>
> Can this be done?
>
> TIA
>
>
> Mark
>
>[/color]

[Ray]

Re: Horizontal Report problem

"Mark Reed" <mark.reed75@nt lworld.com> wrote in message news:<FG4Jc.4$s k6.1@newsfe1-gui.ntli.net>.. .[color=blue]
> Hi Guru's,
> Hopefully I can explain this OK.....What I am trying to do is
> create a QC check sheet using the following fields: ASN, PO & Qty. This
> information will be at the top of each report with loads of labels
> below which users will have as a check list.
> 1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
> I have set it up so far so that a new page is forced for each ASN. On
> each page there should be 1 ASN number, however many PO's that belong to
> each ASN and the Qty of units for each PO.
> The problem I am having is that I can't work out how to get a list of
> PO's (horizontal) across the top. If I put the PO in the header, only
> the first one will appear. If I put it in the detail section, the list
> is vertical and goes down the page rather than across it which wont work for
> this report.
>
> Can this be done?
>
> TIA
>
>
> Mark[/color]

Hello Mark,

Perhaps what you could do is set up 2 reports, one is the main report
and the other will be a subreport.

1. Report one: will be the way you have it set up currently.

2. Report two: will be a sub report that is set up like a labels
report with horizontal label sorting, not vertical sorting. Then
add a subreport control to the main report in your detail section.
Set it to can grow: yes and can shrink: yes.

Make sure that the sub report record source has the ASN Number
so that the Child/Parent connection needed for the subreport
control can be linked.

You'll have to play with the subreport control and the subreport
itself to have all data displayed according to your needs.

Hopefully, this will get you started in the right direction. I have
used simular technics like this before and works well.

Regards,

Ray

[James Fortune]

Re: Horizontal Report problem

"Mark Reed" <mark.reed75@nt lworld.com> wrote in message news:<yUpJc.40$ Ag6.23@newsfe1-gui.ntli.net>.. .[color=blue]
> I'm guessing that from the lack of replies that this is not possible? Could
> someone please confirm my suspicions.[/color]

If the subreport method doesn't work you could write a public string
function that when given the ASN opens a recordset to records with
that ASN and builds a string of all the PO's. The function can be
placed in the SQL string like:

GetPOList([ASN]) AS ListOfPOs

and then have a textbox on your Report with a ControlSource:
ListOfPOs. It's slow and not very pretty or even considered good
practice but I have tried this and it seems to work well when the
record count isn't huge.

Here's an example:

tblCustomerPurc hases
CustID Long
theDate Date
Brand Text

Public Function GetBrandList(Pu rchaseDate As Date) As String
Dim MyDB As Database
Dim MyRS As Recordset
Dim strSQL As String
Dim strTemp As String
Dim lngI As Long
Dim lngCount As Long

GetBrandList = ""
strTemp = ""
strSQL = "SELECT Brand FROM tblCustomerPurc hases WHERE theDate = #" &
Format(Purchase Date, "m/d/yy") & "#;"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecord set(strSQL, dbOpenSnapshot)
If MyRS.RecordCoun t > 0 Then
MyRS.MoveLast
lngCount = MyRS.RecordCoun t
MyRS.MoveFirst
For lngI = 1 To lngCount
If lngI <> lngCount Then
strTemp = strTemp & MyRS("Brand") & ";"
MyRS.MoveNext
Else
strTemp = strTemp & MyRS("Brand")
End If
Next lngI
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
GetBrandList = strTemp
End Function


Then putting SELECT DISTINCT CustID, theDate, GetBrandList([theDate])
AS BrandList FROM tblCustomerPurc hases GROUP BY CustID, theDate; as
the RecordSource of a Report allows me to drag BrandList from the
Field List into the Report Details section along with CustID and
theDate.

James A. Fortune

[Mark Reed]

Re: Horizontal Report problem

Hi all,
Thanks very much for your suggestions but I managed to get it working
before I had a change to check for new posts.

I did it by setting the 'vertical' property of all labels and text boxes to
true and building the report on it's side. When it's printed out, it looks
as though it was designed in landscape format. All the titles, etc were
placed in the ASN header with the PO text boxes in the detail section and it
worked a treat.

Hope this info helps other and thanks again,

Mark


"Mark Reed" <mark.reed75@nt lworld.com> wrote in message
news:yUpJc.40$A g6.23@newsfe1-gui.ntli.net...[color=blue]
> I'm guessing that from the lack of replies that this is not possible?[/color]
Could[color=blue]
> someone please confirm my suspicions.
>
> Cheers,
>
> Mark
>
> "Mark Reed" <mark.reed75@nt lworld.com> wrote in message
> news:FG4Jc.4$sk 6.1@newsfe1-gui.ntli.net...[color=green]
> > Hi Guru's,
> > Hopefully I can explain this OK.....What I am trying to do is
> > create a QC check sheet using the following fields: ASN, PO & Qty. This
> > information will be at the top of each report with loads of labels
> > below which users will have as a check list.
> > 1 ASN may have many PO's and the Qty is at PO level so 1 Qty for each PO
> > I have set it up so far so that a new page is forced for each ASN. On
> > each page there should be 1 ASN number, however many PO's that belong to
> > each ASN and the Qty of units for each PO.
> > The problem I am having is that I can't work out how to get a list of
> > PO's (horizontal) across the top. If I put the PO in the header, only
> > the first one will appear. If I put it in the detail section, the list
> > is vertical and goes down the page rather than across it which wont work[/color]
> for[color=green]
> > this report.
> >
> > Can this be done?
> >
> > TIA
> >
> >
> > Mark
> >
> >[/color]
>
>[/color]