Link to home
Start Free TrialLog in
Avatar of mburk1968
mburk1968Flag for United States of America

asked on

MS Access 2010 ADP Input Parameter string not functioning correctly for a subreport

We just converted from MS Access 2007 ADP (Windows XP) to MS Access 2010  ADP (Windows 7).  We use the Input Paramenters string off of the data tab in MS Access to pass parms to our backend SQL query in SQL Server 2005.  This works fine for MS Access 2007, but not MS Access 2010.  I have a Main report with a subreport, and this subreport has a subreport.  The 2nd subreport is the one that is malfunctioning.  It pulls it's parms from the Main Report, whereas the second subreport pulls its parms from the Access forms leading up to the report (it has totally different parms).  
Does anyone know what changed between the 2 version of MS Access to cause this to happen?  If not, does anyone know of an alternative way to pass parms to the 2nd subreport?
Parameter-passing-screen-shots.docx
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Access announced the discontinuation of ADP support in 2010, and has long advised to use linked tables instead of ODBC. Older versions may function, or they may not.

Is your subreport unbound?

In some cases you'd have to write the data to a local table, and base your report off those local tables. In other cases, you can sometimes directly modify the reports underlying recordsource to show the right records - base your subreport on a query, and then BEFORE opening the main report, modify the query SQL:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Set dbs = CurrentDB
Set qdf = dbs.QueryDefs("Your report querydef")

qdf.SQL = "Your SQL Statement"

Set qdf = Nothing
Set dbs = Nothing

Now open your report, and the subreport (which is based on the querydef above) should show the correct records.
Avatar of mburk1968

ASKER

Yes...the subreport is unbound.  The subreport is based on a query (the Record Source on the Data tab is populated), but the parms for that query originate in the Main report.  So, I won't know the parms for the 2nd subreport until the query for the Main report runs.  Also, for further clarification, each report (Main, and both subreports) is based on a different query, and each query is specified in the Record Source on the Data tab in Access.
Sorry...I misspoke.  The parms for the 2nd subreport are coming from the 1st subreport, not from the Main report.
I don't know of any specific changes between those versions that would cause this, but as stated before MSFT has stopped insuring that ADP format databases will function correctly (in fact, there has been no specified support for ADPs for quite some time).

Short of building your datasources locally, I don't know what you can do to fix this.
ASKER CERTIFIED SOLUTION
Avatar of mburk1968
mburk1968
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Master/Child linking was the only solution I could get to work...