Help with subreport referencing

[Alan]

Hmmm, I'm not too good with the syntax of referencing a subreport.

I have frmInvoice which has the invoice details (e.g. ProductCode,
ProductCost etc) in the subform frmInvoiceDetai ls. I'm trying to get a field
(AccountID) to show in the parent form based on whether a particular product
code (EXT) is present in the list of ProductCodes on the subform. So far
I've tried:

IIf(Reports![frmInvoiceDetai ls]![ProductCode]="EXT",Null,[Reports![frmInvoic
e]![AccountID])

But I get an error. Any ideas would be appreciated,

thanks

Alan

[Larry Linson]

"Alan" <no****@nospam. com> wrote

Hmmm, I'm not too good with the syntax of referencing a subreport.

I have frmInvoice which has the invoice details (e.g. ProductCode,
ProductCost etc) in the subform frmInvoiceDetai ls. I'm trying to get a
field
(AccountID) to show in the parent form based on whether a particular
product
code (EXT) is present in the list of ProductCodes on the subform. So far
I've tried:

IIf(Reports![frmInvoiceDetai ls]![ProductCode]="EXT",Null,[Reports![frmInvoic
e]![AccountID])

But I get an error. Any ideas would be appreciated,

A Form embedded in a Subform Control, even if visible is not "Open" and,
thus, not in the Forms Collection. It exists as the Form property of the
Subform Control. In code in the Form's module of the main form, you'd use:

Me!SubformContr olName.Form!Con trolName

substitute the name of your Subform Control and the name of the Control in
the Form embedded in the Subform Control. I do not name SubformControls with
the "frm" prefix, but instead use "sbf" -- to avoid confusion.

I'm a little puzzled that you'd be using the Reports Collection for Forms.
Yes, it is possible to use a SubForm Control, with an embedded Form, in a
Report. The converse is not true -- you cannot embed a Report in a
Subform/Subreport Control displayed in a Form.

Larry Linson
Microsoft Access MVP

[Lyle Fairfield]

"Larry Linson" <bo*****@localh ost.not> wrote in
news:HEe8g.2007 8$W83.7509@trnd dc07:

A Form embedded in a Subform Control, even if visible is not "Open"
and, thus, not in the Forms Collection.

I believe it is open but I don't know if it's in the forms collection.

Try this.

1. Put some code in the subform's module or in recent Access versions set
the subform's HasModule property to true.
2. Put a textbox on the subform. Set it's default value to "Larry".
3. Open the main form. Move to the subform. Overwrite Larry with "Lyle"
in the textbox.
4. Leave the main form open.
5. Somewhere, anywhere in the application run this line.
MsgBox Form_subformNam e.textboxName.V alue
ie if the subform's (not the control's) name is OrderItems and the
TextBox's name is Contact then
MsgBox Form_OrderItems .Contact.Value

The subform is definitely open now. If the message box says "Larry" then
clearly it just now opened and returns the default value. If it says
"Lyle" then it was open when you typed in "Lyle (prior to your call to
MsgBox).

Look in your VB editor. Make the Project Explorer visible. You will see
Form_OrderItems in Access Class Objects there. You can refer to
Form_OrderItems anywhere, anytime. It points to the default instance of
the form. If the form isn't open, it opens it (invisible unless its
visiblity is expressly set in its open event code). But if is open it
just points to that instance. And that's what happens here in Canada when
the form is being used as a subform.

As an aside, this gives us a simple way of creating multiple instances of
a form.

Dim SomeOrderItemFo rms(8) as Form_OrderItems gives us an array of 8
unitialized OrderItems forms. We can scan through our array, intialize
each form and give its properties unique values, suvh as Captions, Item
Orders (0), Item Orders (1), Item Orders(2) etc.

--
Lyle Fairfield

[DFS]

Lyle Fairfield wrote:

"Larry Linson" <bo*****@localh ost.not> wrote in
news:HEe8g.2007 8$W83.7509@trnd dc07:

A Form embedded in a Subform Control, even if visible is not "Open"
and, thus, not in the Forms Collection.
I believe it is open but I don't know if it's in the forms collection.

It's "open" in the sense that the subform Open and Load events fire when the
main form is opened, but he's right that it's not in the Forms collection.

This old classic is useful for verifying:

Function IsFormLoaded(By Val MyFormName As String) As Integer
Dim i
IsFormLoaded = False
For i = 0 To Forms.Count - 1
If Forms(i).FormNa me = MyFormName Then
IsFormLoaded = True
Exit Function
End If
Next
End Function

Try this.

1. Put some code in the subform's module or in recent Access versions
set the subform's HasModule property to true.
2. Put a textbox on the subform. Set it's default value to "Larry".
3. Open the main form. Move to the subform. Overwrite Larry with
"Lyle" in the textbox.
4. Leave the main form open.
5. Somewhere, anywhere in the application run this line.
MsgBox Form_subformNam e.textboxName.V alue
ie if the subform's (not the control's) name is OrderItems and the
TextBox's name is Contact then
MsgBox Form_OrderItems .Contact.Value

The subform is definitely open now. If the message box says "Larry"
then clearly it just now opened and returns the default value. If it
says "Lyle" then it was open when you typed in "Lyle (prior to your
call to MsgBox).

Look in your VB editor. Make the Project Explorer visible. You will
see Form_OrderItems in Access Class Objects there. You can refer to
Form_OrderItems anywhere, anytime. It points to the default instance
of the form. If the form isn't open, it opens it (invisible unless its
visiblity is expressly set in its open event code). But if is open it
just points to that instance. And that's what happens here in Canada
when the form is being used as a subform.

As an aside, this gives us a simple way of creating multiple
instances of a form.

Dim SomeOrderItemFo rms(8) as Form_OrderItems gives us an array of 8
unitialized OrderItems forms. We can scan through our array, intialize
each form and give its properties unique values, suvh as Captions,
Item Orders (0), Item Orders (1), Item Orders(2) etc.

[Alan]

Sorry guys I feel such an idiot, this is reports we're working with, I was
looking at the wrong list (forms) and working from memory when I quickly
typed out the message. My message should have been:

I have rptInvoice which has the invoice details (e.g. ProductCode,
ProductCost etc) in the subform rptInvoiceDetai ls. I'm trying to get a field
(AccountID) to show in the parent report based on whether a particular
product code (EXT) is present in the list of ProductCodes on the subreport.
So far I've tried:

IIf(Reports![rptInvoiceDetai ls]![ProductCode]="EXT",Null,[Reports![rptInvoic
e]![AccountID])

Sorry about the confusion!! maybe I should drink more coffee....

Alan

[Alan]

I have rptInvoice which has the invoice details (e.g. ProductCode,
ProductCost etc) in the subform rptInvoiceDetai ls. I'm trying to get a field (AccountID) to show in the parent report based on whether a particular
product code (EXT) is present in the list of ProductCodes on the subreport. So far I've tried:

IIf(Reports![rptInvoiceDetai ls]![ProductCode]="EXT",Null,[Reports![rptInvoic e]![AccountID])

Actually, I'm guessing this is a timing/loading issue as I can reference
fields on the parent report in the subreport but not vice versa.

Alan

[Alan]

OK, to make things easier I'm only dealing with the subreport now. The brief
is the same though, I would like to display a label if Product code "EXT" is
present in the product codes listed on the invoice. I can't use syntax like:

IIf([ProductCode]="EXT" etc

Because ProductCode is listed as many times as there are items on the
invoice i.e. it's in the Details section of the report. What's the best way
of going about this?

Alan

[Larry Linson]

"Alan" <no****@nospam. com> wrote in message
news:U6******** ************@bt .com...

I have rptInvoice which has the invoice details (e.g. ProductCode,
ProductCost etc) in the subform rptInvoiceDetai ls. I'm trying to get a

field

(AccountID) to show in the parent report based on whether a particular
product code (EXT) is present in the list of ProductCodes on the

subreport.

So far I've tried:

IIf(Reports![rptInvoiceDetai ls]![ProductCode]="EXT",Null,[Reports![rptInvoic

e]![AccountID])

Actually, I'm guessing this is a timing/loading issue as I can reference
fields on the parent report in the subreport but not vice versa.

You refer to it the same as a form... just substitute SubreportContro l for
SubformControl in my previous post.

The Reports Collection is, by definition, a Collection of Open Reports. The
Report displayed in a Subreport Control can be used, in many ways, as though
it were Open, but isn't in and therefore can't be referenced using the
Reports collection. It exists as the Report property of the Subreport
Control.

<It's not productive to argue with Lyle over his definition of "Open".>

Assuming the code you describe is in the Format or Print event of the Detail
Section, a reference to a Field will be to that Field in the current record,
not to all occurrences of that Field in the Report. Sometimes, it is worth
trying something that may "seem obvious it will not work" to you.

Larry Linson
Microsoft Access MVP

[Alan]

> You refer to it the same as a form... just substitute SubreportContro l for

SubformControl in my previous post.

The Reports Collection is, by definition, a Collection of Open Reports. The Report displayed in a Subreport Control can be used, in many ways, as though it were Open, but isn't in and therefore can't be referenced using the
Reports collection. It exists as the Report property of the Subreport
Control.

<It's not productive to argue with Lyle over his definition of "Open".>

Assuming the code you describe is in the Format or Print event of the Detail Section, a reference to a Field will be to that Field in the current record, not to all occurrences of that Field in the Report. Sometimes, it is worth
trying something that may "seem obvious it will not work" to you.

Larry Linson
Microsoft Access MVP

Thanks Larry, I'll give that a go, although I had just resigned myself to
just working within the subreport only to keep things simple! Just having
problems referencing the output of "ProductCod e" as it gets printed many
times within the Details section of the invoice report (according to how
many products have been purchased) so I'm not able to use syntax like:

IIf([ProductCode]="EXT" etc

Alan

[Larry Linson]

"Alan" <no****@nospam. com> wrote

Just having problems referencing the output of
"ProductCod e" as it gets printed many
times within the Details section of the invoice report
(according to how many products have been
purchased) so I'm not able to use syntax like:

IIf([ProductCode]="EXT" etc

I don't understand where you have put the code that is not working. You
should be working on a separate detail line of the report, if it is in the
Format or Print event... there's just no place in a report where multiple
instances of a single Control are available to VBA. If it _appears_ that is
the problem, you need to dig deeper to discover the real problem.

Do you have both a Control and the underlying Field (ControlSource property
of that Control) with the same identical names? That can cause "confusion, "
despite the beliefs of some here that it is perfectly OK all the time.

Larry Linson
Microsoft Access MVP