Alter Report Captions from a Subform

[bobdydd]

Hi

I have a Main Form with Subform
I have a Report that opens on a query that restricts it to a single
record

On the report there are Labels whose captions I need to alter
depending what records are showing on the subform. But I am only
getting it to work on the first Label. Grateful for any help

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 1
Then
Me.lbl_1.Caption = 9
End If

If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 2
Then
Me.lbl_2.Caption = 9
End If

If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 3
Then
Me.lbl_3.Caption = 9
End If

If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 4
Then
Me.lbl_4.Caption = 9
End Sub

 

[John W. Vinson]

Hi

I have a Main Form with Subform
I have a Report that opens on a query that restricts it to a single
record

On the report there are Labels whose captions I need to alter
depending what records are showing on the subform. But I am only
getting it to work on the first Label. Grateful for any help

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 1
Then
Me.lbl_1.Caption = 9
End If

If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 2
Then
Me.lbl_2.Caption = 9
End If

If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 3
Then
Me.lbl_3.Caption = 9
End If

If Forms![frmSite]![zfrmSiteProductLeft].Form![ComboProductID] = 4
Then
Me.lbl_4.Caption = 9
End Sub

I'm confused: what do you want to see on the mainform if there are 10 records
in the subform's recordsource, with four with 1 in the combo, three with 2,
two with 3 and one with 4?

A combo box can only have one value (well, unless it's a multiselect) but a
subform can have many records. What exactly are you trying to accomplish by
displaying this data redundantly (since the user can just presumably look at
the subform and see what products are there)?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[bobdydd]

Hi

Thanks for the reply
The Database is for a Vending Machine operator
who has 12 different Products available to
leave at each of the vending machine sites

Each site can only take 9 choices of Product
And each choice of product = 9 items left on
sitehence the caption = 9

This Report is for the vending roundsman to
print out and take on his rounds and manually
write on the paper how many have been sold.

The data showing which Products have been
left on the previous visit, are on the
underlying subform

The result on the report should look something
like this:

Site: Jones the Butchers
Address: 1 High Street, Romford, Essex RM1 1FT
Tel # 01111 111222

Products Left at site last visit

Chocolate Bars 9
Peanut Bars 9
Peppermint Bars 9
Honeycombe Bars 0
Raisin Bars 9
Chocolate wraps 0
Candy Fizz 9
Whirlygigs 9
CHocolate Balls 0
Candy Balls 9
Apricot Bars 9
Maltesers 9

 

[John Spencer]

You would have to execute the code in the format or print event of the section
containing the label caption you wanted to change.

I wonder why you are using a sub-form in a report. As far as I can tell you
should be able to use the report header or group header for the company info
and the detail lines for the information you are currently printing using the
sub-form.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County