I am tasked with converting an old MS Access Application to a MS Access front end and SQL Server back end. I have several reports (all labels) which are giving me an SQL Server ODBC error when I open them in preview mode but which open fine in report mode.
This command fails:
DoCmd.OpenReport Me.cmbReportSelection, acPreview, , whereStr
This command works
DoCmd.OpenReport Me.cmbReportSelection, acViewReport, , whereStr
until I switch to preview mode then it fails with the same error as if I opened it in preview mode.
Error message received:
Microsoft Access
ODBC -- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique (#169)
Sometimes, MS Access crashes completely after clicking ok on the error, sometimes, it allows me to continue.
Environment:
MS Access 2013 (accdb format)
SQL Server 2016
I have put enough displays in to determine its not likely the error message is correct unless Access is doing something after I provide the data source, where condition, filter and order properties. Given that the code works if I open it in acViewReport instead of acPreview, I am not sure its anything I am doing.
The code also seems to work in a version of the database that connects to an MS Access backend and in the original system's .mdb format.
it also doesn't matter if there is a where condition, filter on or order on set. The open report is being called from a report selection menu that include options for a variety of filter, sort, and group criteria that can be used for a number of reports but its only the 3 reports that are "labels" that seem to have this problem.
I have other “label” reports in the system which do not show this problem but are not called through the same process, nor do they use select statements as the record source, they use a query. I tried converting the select statement on the broken reports to a query but it didn’t change the outcome. I have also tried moving the select to a view on the server and it also does not impact the result.
The code flow is:
from the report selection form, select a report from a pull down, fill in any desired filter, sort and group criteria, click on a command button to preview the report.
The command button code processed the filter condition options to assemble a where condition for the report "whereStr" variable
The individual report on-load functions call a common function which handles sort options and a few other things and then the load function takes care of setting the filter and order by properties of the report.
The RecordSource of the reports is a Select Statement. one example is below
I am expecting the label reports to come up in preview with the correct filter and sort applied.
What I am getting is the ODBC error saying I have a duplicate Order by column unless I change the mode the report is opened in. Then it works till I switch to preview mode.
This command fails:
DoCmd.OpenReport Me.cmbReportSelection, acPreview, , whereStr
This command works
DoCmd.OpenReport Me.cmbReportSelection, acViewReport, , whereStr
until I switch to preview mode then it fails with the same error as if I opened it in preview mode.
Error message received:
Microsoft Access
ODBC -- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique (#169)
Sometimes, MS Access crashes completely after clicking ok on the error, sometimes, it allows me to continue.
Environment:
MS Access 2013 (accdb format)
SQL Server 2016
I have put enough displays in to determine its not likely the error message is correct unless Access is doing something after I provide the data source, where condition, filter and order properties. Given that the code works if I open it in acViewReport instead of acPreview, I am not sure its anything I am doing.
The code also seems to work in a version of the database that connects to an MS Access backend and in the original system's .mdb format.
it also doesn't matter if there is a where condition, filter on or order on set. The open report is being called from a report selection menu that include options for a variety of filter, sort, and group criteria that can be used for a number of reports but its only the 3 reports that are "labels" that seem to have this problem.
I have other “label” reports in the system which do not show this problem but are not called through the same process, nor do they use select statements as the record source, they use a query. I tried converting the select statement on the broken reports to a query but it didn’t change the outcome. I have also tried moving the select to a view on the server and it also does not impact the result.
The code flow is:
from the report selection form, select a report from a pull down, fill in any desired filter, sort and group criteria, click on a command button to preview the report.
The command button code processed the filter condition options to assemble a where condition for the report "whereStr" variable
The individual report on-load functions call a common function which handles sort options and a few other things and then the load function takes care of setting the filter and order by properties of the report.
The RecordSource of the reports is a Select Statement. one example is below
Code:
SQL
SELECT tblAllFacilities.FacLicenseNumber, tblAllFacilities.FacMedicareNumber, tblAllFacilities.FacName, tblAllFacilities.FacName2, [tblHHA/Hospice Facilities].[Mail Address 1], [tblHHA/Hospice Facilities].[Mail City], tblAllFacilities.FacCity, [tblHHA/Hospice Facilities].[Mail State], [tblHHA/Hospice Facilities].[Mail Zip], tblAllFacilities.FacNotActiveDate, [tblHHA/Hospice Facilities].PhoneNumber, [tblHHA/Hospice Facilities].[Contact Lst Nme], [tblHHA/Hospice Facilities].[Contact Fst Nme], [tblHHA/Hospice Facilities].[Contact Phone], [tblHHA/Hospice Facilities].[Contact Ext], tblAllFacilities.FacAdminName, tblAllFacilities.FacAdminLast, tblAllFacilities.FacAdminTitle, tblAllFacilities.ToBeDeleted, tblAllFacilities.[Facility Type], tblAllFacilities.FacStatusCode
FROM [tblHHA/Hospice Facilities] RIGHT JOIN tblAllFacilities ON [tblHHA/Hospice Facilities].[670FacIDLink] = tblAllFacilities.FacID
WHERE (((tblAllFacilities.FacNotActiveDate) Is Null) AND ((tblAllFacilities.ToBeDeleted)=False) AND ((tblAllFacilities.FacStatusCode)="Active") AND ((tblAllFacilities.FacClosedDate) Is Null));
I am expecting the label reports to come up in preview with the correct filter and sort applied.
What I am getting is the ODBC error saying I have a duplicate Order by column unless I change the mode the report is opened in. Then it works till I switch to preview mode.