InputParameters worked in Access 2003 but no longer work in Access 2010

[Ryan Moody]

Hello,

I would be very grateful if someone could assist me with the following problem. We are in the process of migrating an Access 2003 Data Project (.ADP) to Access 2010. In the Access 2003 project (which works correctly), we have a report that is configured with InputParameters (found in the Design View under the Data tab) set to the following:

@intYear int = [Reports]![rptShippedWeightsPosition].pYear, @strCompanyList varchar(1000) = [Reports]![rptShippedWeightsPosition].pCompanyList, @strProductList varchar(1000) = [Reports]![rptShippedWeightsPosition].pProductList, @strContractList varchar(1000) = [Reports]![rptShippedWeightsPosition].pContractList

In the above declaration, pYear, pCompanyList, pProductList and pContractList all refer to properties on the report whose implementations are as follows:

Public Property Get pYear() As Variant
pYear = m_year


End Property

Public Property Get pCompanyList() As Variant
pCompanyList = m_CompanyList
End Property

Public Property Get pProductList() As Variant
pProductList = m_ProductList
End Property

Public Property Get pContractList() As Variant
pContractList = m_ContractList
End Property

In the Access 2003 project, the InputParameters declaration is working correctly - we have confirmed by using breakpoints that the four properties above are executed when the report is created, and the report gives us the results that we are expecting.

However, when we migrated this ADP to Access 2010, we discovered that the InputParameters were no longer working. When the report is run in Access 2010, the end-user is prompted to enter values for the parameters instead. We changed the InputParameters declaration in Access 2010 to the following (effectively replacing the occurrences of '.' with '!'):

@intYear int = [Reports]![rptShippedWeightsPosition]!pYear, @strCompanyList varchar(1000) = [Reports]![rptShippedWeightsPosition]!pCompanyList, @strProductList varchar(1000) = [Reports]![rptShippedWeightsPosition]!pProductList, @strContractList varchar(1000) = [Reports]![rptShippedWeightsPosition]!pContractList

This resolved the problem of the end-user being prompted to enter values for the parameters, however the four Get Properties (implementations shown above) were no longer being executed. As a consequence, the results in the generated report were incorrect.

I have tried a number of permutations to get the InputParameters working in Access 2010, namely:

• putting braces around pYear, pCompanyList etc. - i.e. @intYear int = [Reports]![rptShippedWeightsPosition]![pYear] etc.
• putting brackets after pYear, pCompanyList etc. - i.e. @intYear int = [Reports]![rptShippedWeightsPosition]![pYear()] etc.
• Removing the datatype from pYear, pCompanyList etc. - i.e. @intYear = [Reports]![rptShippedWeightsPosition]![pYear] etc.
• Following recommendations on setting the RecordSource property as posted in a similar issue: http://stackoverflow.com/questions/8...s-on-form-load

Unfortunately, none of these attempts have succeeded - in Access 2010, the Get Properties are still not being executed and the report (which worked in Access 2003) is now giving us incorrect results.

I would be very grateful if anyone could provide any suggestions as to what the resolution may be - please let me know if anything is unclear, or if you require any further information in order to help diagnose the issue.

Many thanks,

Ryan

[ranman256]

are you using the query designer, and entering these in the Parameters button?

[Ryan Moody]

Hi ranman,

The parameters have been specified in the InputParameters field on the report's Design View, under the Data tab on the report's Property Sheet.

Regards,

Ryan

[ItsMe]

Is your Access 2010 app using SQL as a backend?

I am not an expert with SQL but it seems the report within the adp was using stored procedures on the SQL server. I do not know if this is supported with 2010. Access projects have been deprecated.

Perhaps you can look on the server for the Stored Procedure to rebuild the input parameters within 2010. Each field should have its own. For instance @strCompanyList is the name of the Stored Procedure for the field named pCompanyList.

[Ryan Moody]

Hello,

Apologies for not replying sooner - I believe we have now resolved the original problem - however, we now have two new problems relating to our Access .ADP project migration from 2003 to 2010 - I would be very grateful if anyone could provide me with any assistance with the following:

Problem 1: #Name? errors occurring.

We have a number of reports containing calculated values such as =nz([Open_Count],0). These calculations worked fine in 2003, but when migrated across to Access 2010, these calculations returned the error #Name. In searching for a solution to this problem, I found the following thread:

http://answers.microsoft.com/en-us/o...52b4fa3?page=1

I have tried a number of the suggestions offered, including:

> Ensuring that the fields in the calculations were not prefixed by [table]
> Compacting and repairing the database
> Ensuring that the report's code started with Options Explicit
> Exporting the problematic reports and then re-importing them into the database

One other suggestion was to recreate these reports from scratch - recreating the controls and copying across the code - however I'm not convinced that this is a satisfactory solution / is prone to errors and will only attempt that as a last resort.

Can anyone suggest why these calculations may be returning #Name? errors in 2010 but not in 2003? Anything I can check / verify to diagnose the problem?

Problem 2: Requeries no longer working.

I have a combo box that runs a SQL query in order to populate its options. This used to work fine in Access 2003, but no longer works in Access 2010. The original code in Access 2003 was as follows:

myComboBox.RowSource = MY_SQL_QUERY
myComboBox.Requery

In Access 2010 I have tried changing it to the following:

Me!myComboBox.RowSourceType = "Table/Query/StoredProc"
Me!myComboBox.RowSource = MY_SQL_QUERY
Me!myComboBox.Requery

I have also tried adding in a Form.Requery and requerying the combobox in the Form_AfterUpdate event, but still the combobox is not being populated as it was in 2003.

I have confirmed that the data tables have been successfully migrated across from 2003 to 2010, ruling out the possibility that the query is working but that there is no data to query, hence the empty combobox.

Can anyone suggest any reasons why Requery may no longer be working for me in Access 2010? Does Requery return any error codes or throw any exceptions that I can catch in order to help diagnose the issue?

Any assistance with either of the two problems above is greatly appreciated.

Best Regards,

Ryan

[ItsMe]

The thing with the report may be an issue with sorting and grouping that did not carry over. the thing with the combo is likely a corrupt combo control. you can try deleting the VBA code that is specific to the combo control, Tools>Compile DB, and a compact and repair. Then, recreate the sub procedure using the ellipses in the property sheet or the pull downs from the VBA editor.

[Ryan Moody]

Hello,

Thank you for your suggestions so far - unfortunately I am still encountering difficulties with migrating our Access Data Project from 2003 to 2010. Specifically:

Problem 1 - I have a report containing a sub report. The main report has a hidden textbox with Name "txtPCID" and Control Source "PC". The subreport also has a hidden textbox with Name "txtPCID" and Control Source "intPCID". If the Link Master Field and the Link Child Field on the subreport are left blank, then the generated report contains two records. The first record has matching IDs for the hidden controls, however in the second record, the txtPCID value in the main report is different, but the txtPCID value in the subreport is the same as for the first record. If I then try specifying the Link Master Field and the Link Child Field to be "txtPCID", then the subreport is not displayed at all. We have tried various permutations for the Link Master Field and Link Child Field values (involving "txtPCID", "intPCID" and "PC"), however none of these combinations have worked. It also seems that the subreport's onOpen method is only executed once - shouldn't it be called twice given that two records are displayed in the generated report?

Problem 2 - Requeries no longer working, as previously described in an earlier post. We have tried deleting and recreating the combo control and its associated code (as per ItsMe's suggestion), but unfortunately the control that we are attempting to populate is still ending up empty as the SQL query is not returning any records.

Both of these problems only occur in 2010 - they do not occur in the original 2003 version of the ADP.

If anyone could provide me with any assistance with either of the problems above, it would be greatly appreciated. Please let me know if you require any additional information.

Best Regards,

Ryan

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Ryan Moody]

Hi June7,

Thank you for the offer - however this access data project is commercial in confidence, and so I will not be able to transmit it for others to investigate.

Can you, or anyone else, think of any other things that I can try with regards to the two problems described in my previous post based on the information I have provided so far?

Best Regards,

Ryan

[June7]

I never used ADP and didn't use 2003 very long before moving on to 2007.

What is the @ symbol for? I have never set up parameters for queries. I don't use dynamic parameterized queries. I use VBA to build filter criteria string and pass to form or report with OpenForm and OpenReport methods or set Filter property.

If you can't provide sanitized db for analysis, won't be able to offer any other ideas.