change subreport source object

  • Thread starter Thread starter JohnLute
  • Start date Start date
J

JohnLute

I've posted this previously but can't locate it! Is something crazy going on
with the forum?

Access 2003/Win 2000

I'm trying to get this to work:
Private Sub Report_Open(Cancel As Integer)
Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.Type &
"PhysicalAttributes"
Me.srptPKMaterialAttributes.SourceObject = "srptPK" & Me.Type &
"MaterialAttributes"
Me.srptPKFinishingAttributes.SourceObject = "srptPK" & Me.Type &
"FinishingAttributes"
Me.srptPKPerformanceAttributes.SourceObject = "srptPK" & Me.Type &
"PerformanceAttributes"

End Sub

The report refuses to open. Does anyone see anything wrong?

The peculiar &Me.Type was suggested by Marshall Barton for use in the
OnChange Event of a tab control in a particular form. It works fine
for the form but perhaps not in the report...?

Thanks!
 
Forms and reports aren't the same. It's possible the "Type" value is not
available in the On Open event. I would suggest including all subreports and
then setting their visible property in the On Format event of the section
containing the subreport(s).

The other issue you might have is "Type" being a reserved word.
 
Hi, Duane.

Thanks for the reply.

I tried this in the main report:
Private Sub Report_Open(Cancel As Integer)
Me.srptPKPhysicalAttributes.SourceObject =
"srptPKCGPhysicalAttributes"
End Sub

Works fine.

I made an alias for "Type" but still this doesn't work:
Private Sub Report_Open(Cancel As Integer)
If Me.PKType = "CG" Then
Me.srptPKPhysicalAttributes.SourceObject =
"srptPKCGPhysicalAttributes"
End If
It's possible the "Type" value is not
available in the On Open event.
Click to expand...

Considering the above I believe you're right.
I would suggest including all subreports and
then setting their visible property in the On Format event of the section
containing the subreport(s).
Click to expand...

The way things are setup I wouldn't have to mess with visible
properties HOWEVER adding all subreports is impossible. There's about
21 of them and the report will never open. I actually tried this a
couple years ago.

I'm trying to eliminate a lot of reports. I was able to eliminate a
lot of forms given the tab control/subform trick mentioned here. I
thought that maybe this would translate into the reports, too!
The other issue you might have is "Type" being a reserved word.
Click to expand...

I know. I wish I would've known about reserved words 7 years ago.
Interestingly, I've never experienced a problem with using Type.
 
I would expect the value of Type might be available some how before the
report is opened based on something happening in a form. If this is the case,
you could try send the value of Type in using the OpenReport OpenArgs. Then
you wouldn't have to depend on the record source of the main report.
 
Duane,
I would expect the value of Type might be available some how before the
report is opened based on something happening in a form. If this is the case,
you could try send the value of Type in using the OpenReport OpenArgs. Then
you wouldn't have to depend on the record source of the main report.
Click to expand...

That's a good idea however I open the report via a selection in a
combobox. I've modified it for OpenArgs but this won't open the
report. I'm not very good with OpenArgs - do you see what I've done
wrong?

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click
If IsNull(Me.cbSelectReport) = True Then
MsgBox "No document is selected!" & vbCrLf & _
"Select a document and click the Preview button."
DoCmd.GoToControl "cbSelectReport"
Exit Sub
End If

Dim stDocName As String
Dim strWhere As String
Dim strPKType As String

strPKType = "[Type] = """ & _
strWhere = "[txtProfileID] = """ & _
Forms![frmPackaging].Form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acPreview _
, , strWhere, OpenArgs:=strPKType

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub
 
I have no idea where you are attempting to pull the Type value from. Your
previous code hinted that the Type value would be the same for every record
in the report. You should be able to use DLookup() or pull something from
your current form that has the proper Type value.
--
Duane Hookom
Microsoft Access MVP


johnlute said:
Duane,
I would expect the value of Type might be available some how before the
report is opened based on something happening in a form. If this is the case,
you could try send the value of Type in using the OpenReport OpenArgs. Then
you wouldn't have to depend on the record source of the main report.
Click to expand...

That's a good idea however I open the report via a selection in a
combobox. I've modified it for OpenArgs but this won't open the
report. I'm not very good with OpenArgs - do you see what I've done
wrong?

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click
If IsNull(Me.cbSelectReport) = True Then
MsgBox "No document is selected!" & vbCrLf & _
"Select a document and click the Preview button."
DoCmd.GoToControl "cbSelectReport"
Exit Sub
End If

Dim stDocName As String
Dim strWhere As String
Dim strPKType As String

strPKType = "[Type] = """ & _
strWhere = "[txtProfileID] = """ & _
Forms![frmPackaging].Form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acPreview _
, , strWhere, OpenArgs:=strPKType

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub
.
Click to expand...
 
Hi, Duane.

Thanks for the support. I think I've properly revised the button click
event. The following will open the report and populate the OpenArgs
conrol I placed on the report with the appropriate Type value:

Private Sub Preview_Click()
On Error GoTo Err_Preview_Click
If IsNull(Me.cbSelectReport) = True Then
MsgBox "No document is selected!" & vbCrLf & _
"Select a document and click the Preview button."
DoCmd.GoToControl "cbSelectReport"
Exit Sub
End If

Dim strType As String
Dim strWhere As String

strType = "[Type] = """ & _
Forms![frmPackaging].Form![Type] & """"
strWhere = "[txtProfileID] = """ & _
Forms![frmPackaging].Form![txtProfileID] & """"
DoCmd.OpenReport Me!cbSelectReport, acViewPreview, _
WhereCondition:=strWhere, OpenArgs:=strType

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
MsgBox Err.Description
Resume Exit_Preview_Click

End Sub

Unfortunately, when I plug in the SourceObject change code in the
report's Open event it fails to open. I've tried these:
Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.OpenArgs &
"PhysicalAttributes"
Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.Type &
"PhysicalAttributes"

I've even tried them in the report's detail OnFormat event. Like I
said, I'm not too clear on the whole OpenArgs concept but it seems to
me my problem is whether or not the SourceObject change code is
correct and exactly where to put it. Otherwise, the report opens
properly.

To be clear, the subreports are indeed in the main report's detail
section. Their Master and Child link properties are properly set.

Any further insight you might have is greatly appreciated!

Thanks!
 
The OpenArgs are not like a where condition in that you don't need to use the
"[Type]="
I would change the code to
strType = Forms![frmPackaging].Form![Type]
If the code is running in frmPackaging, then use:
strType = Me.[Type]

Then in the On Open event try this assuming the Type value is part of the
name of your saved subreport.

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.Type &
"PhysicalAttributes"
 
The OpenArgs are not like a where condition in that you don't need to use the
"[Type]="
I would change the code to
strType = Forms![frmPackaging].Form![Type]
If the code is running in frmPackaging, then use:
strType = Me.[Type]
Click to expand...

Gotcha. I was wondering why the OpenArgs control on the report was
displaying "[Type] = CG".
Then in the On Open event try this assuming the Type value is part of the
name of your saved subreport.

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.Type &
"PhysicalAttributes"
Click to expand...

Well, the OpenArgs now works properly as I tested it without an Open
event in the report. When I place this in the report's Open event then
it fails to open:
Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.Type &
"PhysicalAttributes"

The above has no naming flaws. I've checked and double checked. It's
basically the same approach I use with related forms:
Me.sfrmCtl.SourceObject = "sfrmPK" & Me.Type & "PhysicalAttributes"

Given the corrected OpenArgs I've tried the following three codes in
the report's Open event:
If Me.Type = "CG" Then
Me.srptPKPhysicalAttributes.SourceObject =
"srptPKCGPhysicalAttributes"
End If

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & "" & Me.Type &
"" & "PhysicalAttributes"

[Report]![srptPKPhysicalAttributes].SourceObject = "srptPK" & "" & Me!
Type & "" & "PhysicalAttributes"

The debugger is happy with all of the above however the report fails
to open.

Only this properly opens the report and subreport:
Me.srptPKPhysicalAttributes.SourceObject =
"srptPKCGPhysicalAttributes"

This is of no use however.

Considering all of the above it seems like there's something wrong
with:
Me.sfrmCtl.SourceObject = "sfrmPK" & Me.Type & "PhysicalAttributes"

or

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & "" & Me.Type &
"" & "PhysicalAttributes"

However I can't see what else I can do with it!

I greatly appreciate your help. If we've run the gamut of
possibilities then please let me know and I'll try posting again with
where it's at now.

Thanks a bunch!
 
The Me.Type is a continuation of your earlier error. You need to reference
the openargs. Try:

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.OpenArgs &
"PhysicalAttributes"

Duane Hookom
MS Access MVP

johnlute said:
The OpenArgs are not like a where condition in that you don't need to use
the
"[Type]="
I would change the code to
strType = Forms![frmPackaging].Form![Type]
If the code is running in frmPackaging, then use:
strType = Me.[Type]
Click to expand...

Gotcha. I was wondering why the OpenArgs control on the report was
displaying "[Type] = CG".
Then in the On Open event try this assuming the Type value is part of the
name of your saved subreport.

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.Type &
"PhysicalAttributes"
Click to expand...

Well, the OpenArgs now works properly as I tested it without an Open
event in the report. When I place this in the report's Open event then
it fails to open:
Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.Type &
"PhysicalAttributes"

The above has no naming flaws. I've checked and double checked. It's
basically the same approach I use with related forms:
Me.sfrmCtl.SourceObject = "sfrmPK" & Me.Type & "PhysicalAttributes"

Given the corrected OpenArgs I've tried the following three codes in
the report's Open event:
If Me.Type = "CG" Then
Me.srptPKPhysicalAttributes.SourceObject =
"srptPKCGPhysicalAttributes"
End If

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & "" & Me.Type &
"" & "PhysicalAttributes"

[Report]![srptPKPhysicalAttributes].SourceObject = "srptPK" & "" & Me!
Type & "" & "PhysicalAttributes"

The debugger is happy with all of the above however the report fails
to open.

Only this properly opens the report and subreport:
Me.srptPKPhysicalAttributes.SourceObject =
"srptPKCGPhysicalAttributes"

This is of no use however.

Considering all of the above it seems like there's something wrong
with:
Me.sfrmCtl.SourceObject = "sfrmPK" & Me.Type & "PhysicalAttributes"

or

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & "" & Me.Type &
"" & "PhysicalAttributes"

However I can't see what else I can do with it!

I greatly appreciate your help. If we've run the gamut of
possibilities then please let me know and I'll try posting again with
where it's at now.

Thanks a bunch!
Click to expand...
 
Hi, Duane.
The Me.Type is a continuation of your earlier error. You need to reference
the openargs. Try:

Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.OpenArgs &
"PhysicalAttributes"
Click to expand...

Duh. I should've caught that. I caught it earlier. Thanks for pointing
out the error of my ways!

I plugged in the complete code:
Private Sub Report_Open(Cancel As Integer)
Me.srptPKPhysicalAttributes.SourceObject = "srptPK" & Me.OpenArgs &
"PhysicalAttributes"
Me.srptPKMaterialAttributes.SourceObject = "srptPK" & Me.OpenArgs &
"MaterialAttributes"
Me.srptPKFinishingAttributes.SourceObject = "srptPK" & Me.OpenArgs &
"FinishingAttributes"
Me.srptPKPerformanceAttributes.SourceObject = "srptPK" & Me.OpenArgs &
"PerformanceAttributes"

End Sub

The report failed to open. I went back and commented out everything
but the first line and the report opened properly. I un-commented each
line and tested. When I un-commented the last line then the report
failed to open. I tinkered more and it kept acting buggy so I
compacted/repaired. It seems to work fine now.

Well, this is going to enable me to delete LOTS of reports. I'm
looking at deleting about 126 right now! It looks like about another
50+ could be eliminated when all is said and done. This will make a
huge difference in the frontend's file size.

Thanks, Duane!!!
 
Back
Top