472,353 Members | 1,149 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

Help with subreport referencing

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 frmInvoiceDetails. 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![frmInvoiceDetails]![ProductCode]="EXT",Null,[Reports![frmInvoic
e]![AccountID])

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

thanks

Alan
May 10 '06 #1
9 7137
"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 frmInvoiceDetails. 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![frmInvoiceDetails]![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!SubformControlName.Form!ControlName

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
May 10 '06 #2
"Larry Linson" <bo*****@localhost.not> wrote in
news:HEe8g.20078$W83.7509@trnddc07:

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_subformName.textboxName.Value
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 SomeOrderItemForms(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
May 10 '06 #3
DFS
Lyle Fairfield wrote:
"Larry Linson" <bo*****@localhost.not> wrote in
news:HEe8g.20078$W83.7509@trnddc07:

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(ByVal MyFormName As String) As Integer
Dim i
IsFormLoaded = False
For i = 0 To Forms.Count - 1
If Forms(i).FormName = 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_subformName.textboxName.Value
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 SomeOrderItemForms(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.


May 10 '06 #4
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 rptInvoiceDetails. 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![rptInvoiceDetails]![ProductCode]="EXT",Null,[Reports![rptInvoic
e]![AccountID])

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

Alan
May 10 '06 #5
I have rptInvoice which has the invoice details (e.g. ProductCode,
ProductCost etc) in the subform rptInvoiceDetails. 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![rptInvoiceDetails]![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
May 10 '06 #6
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
May 10 '06 #7

"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 rptInvoiceDetails. 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![rptInvoiceDetails]![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 SubreportControl 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
May 10 '06 #8
> You refer to it the same as a form... just substitute SubreportControl 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 "ProductCode" 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
May 10 '06 #9
"Alan" <no****@nospam.com> wrote
Just having problems referencing the output of
"ProductCode" 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

May 11 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Michael Charney | last post by:
I have a main report that has a subreport to it. On the sub report I have a running total. If there are no entries in the sub-report source table...
1
by: Manal/report designer | last post by:
Thank you in advance for any suggestions... I'm using crystal reports version 8 & SQL server. I've created a report that is composed of 2 parts:...
2
by: Nothing | last post by:
I have a main report with several sub-reports on it. Some of the sub-reports are hidden some are visible. A few of the sub-reports hve totals...
2
by: Keith Wilby | last post by:
A97 I have a report/sub-report setup and for some records in the main report, the sub-report is blank. I want to set the height of the sub-report...
7
by: ChadDiesel | last post by:
Hello everyone, I'm having a problem with Access that I need some help with. The short version is, I want to print a list of parts and part...
2
by: Jimmy | last post by:
On the subreport, records are grouped by WorkDate. In the WorkDate header there is a textbox named DateCounter with the control source =1 and...
1
by: Susan-MN | last post by:
Iíve just recently started working with Access at my new job. Iíve been able to figure out quite a bit on my own, but Iím stuck with a problem I...
4
by: Wook | last post by:
Ok I got a set of Forms Reports etc it goes like this Reports Form Passes a Filter to the report for the needed results The filter it passes along...
12
D Giles
by: D Giles | last post by:
Access 2003: A subreport control (sum total calculated textbox located in the subreport report footer) does not show total of all records when...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.