Reference SubReport in code

[Mike Revis]

Hi Group,
Access 2000, win xppro

I have never tried a subreport before although I can sort of find my way
around access and vba.

I am trying to get a subreport to show in a limited space on the main report
page header without pushing everything else down the page. I can't get the
placement right so I thought perhaps I might be able to do it through code.

The only field on the sub report is SalesOrder.

I am trying to accomplish something like this.

txtSomeCode = SomeCode & vbCrLf & subreport goes here

I have tried
Dim SO As SubReport
SO = Reports!rptMain!rptSub.Report!SalesOrder
txtSomeCode = SomeCode & vbCrLf & SO

This returns an error of object variable not defined.

Then I tried
txtSomeCode = SomeCode & vbCrLf & Reports!rptMain!rptSub.Report!SalesOrder

This returns the sales order number for the first record but not the second
or third etc. There can be several sales order numbers on one report.
This one would work fine if it returned all of the sales order numbers.

Then I tried
txtSomeCode = SomeCode & vbCrLf & Reports!rptMain!rptSub.Report

This returns a type mismatch error


As always any thoughts or suggestions are welcome.

Mike

 

[tina]

comments inline.

Hi Group,
Access 2000, win xppro

I have never tried a subreport before although I can sort of find my way
around access and vba.

I am trying to get a subreport to show in a limited space on the main report
page header without pushing everything else down the page. I can't get the
placement right so I thought perhaps I might be able to do it through code.

The only field on the sub report is SalesOrder.

I am trying to accomplish something like this.

txtSomeCode = SomeCode & vbCrLf & subreport goes here

I have tried
Dim SO As SubReport
SO = Reports!rptMain!rptSub.Report!SalesOrder
txtSomeCode = SomeCode & vbCrLf & SO

This returns an error of object variable not defined.

SubReport is an object variable, so you need to "set" its' value, as

Set SO = Reports!rptMain!rptSub.Report

you cannot include a control reference, because you've specified SO as a
subreport object variable, not a control object variable.

Then I tried
txtSomeCode = SomeCode & vbCrLf & Reports!rptMain!rptSub.Report!SalesOrder

This returns the sales order number for the first record but not the second
or third etc. There can be several sales order numbers on one report.
This one would work fine if it returned all of the sales order numbers.

yes, the reference is correct to return the value in the "current" record of
the subreport.

Then I tried
txtSomeCode = SomeCode & vbCrLf & Reports!rptMain!rptSub.Report

This returns a type mismatch error

here, you're trying to concatenate a report object, rather than a value, to
a string - which is why you're getting the type mismatch error.

As always any thoughts or suggestions are welcome.

not sure what "finished product" you're looking for. since you don't want to
push down the rest of the report, i assume you want to concatenate multiple
SalesOrder values into one string and display them in the txtSomeCode
textbox? if so, you can probably open a RecordsetClone of the subreport, and
loop through it to concatenate the SalesOrder value of each record to your
string. i'm thinking you'd have to run the code on the report section's
OnFormat event, to retrieve the values for each of multiple records in the
main report. you could probably dispense with the subreport altogether, and
simply open a recordset based on a SQL statement that returns the SalesOrder
records you need for each record in the main report.

hth

 

[Mike Revis]

Hi tina,
Thanks for your response.
You are correct. I didn't need the subreport at all. The RecordsetClone idea
works great.

Mike

 

[tina]

you're welcome, glad you got it working.

Hi tina,
Thanks for your response.
You are correct. I didn't need the subreport at all. The RecordsetClone idea
works great.

Mike

record want