change subreport source object

[JohnLute]

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!

 

[Tom Wickerath]

Hi John,

The Me.Type part bothers me, because Type is considered a reserved word in
Access. Is this the name of a field in the recordsource for this report?

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

If you do have a field or control named Type, I recommend changing the name.
If you don't want to do that right now, then use square brackets, like this:

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

Also, make sure that this code module--and eventually all code
modules--include the two very important words Option Explicit near the top of
the module, usually like this:

Option Compare Database
Option Explicit

If you only see the line that reads "Option Compare Database", then add
Option Explicit to all modules. Then VBA editor so that you will get these
two very important words automatically added to all newly created modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Make sure that your code compiles without any compile errors. When viewing
any code module, click on:

Debug | Compile {ProjectName}

where {ProjectName} is the name of your VBA project (likely the same name as
the database, but it can be different).

The report refuses to open.

Do you get any compile-time or run-time errors? Actually, you should get a
compile-time error with the Me.Type.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________