• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Access report does not work after Upsizing to SQL SERVER


I have:
form  - myForm
control - myInputBox
report - myReport

the report is build according to value of myInputBox

it is called from in ButtonClick

Private Sub Button_Click()
    DoCmd.OpenReport "Report", acPreview
End Sub

The RecordSource of Report is :
Select * from Table(@Forms___myForm___myInputBox)

The above does not work of course (The result of Upsizing to SQL SERVER)

How to redo that ?
2 Solutions
I don't know what that recordsource is supposed to do.  I can't see how it ever worked, so I don't see that it doesn't work after upsizing to be remarkable.
Do you use an Access data Project or ODBC?
Can you clarify what the user would enter into the Textbox field? It appears that you pass the name of a table?

Typically I would call the report with the contents of the textBox as the OpenArgs parameter.

DoCmd.OpenReport "Report", acPreview,,,, Me.myInputBox

Then trap the Open event of the report and create the SQL statement there on the fly and adjust the report's recordsource.

I'll try and find a code example...

Here is some sample code:

Private Sub Report_Open(Cancel As Integer)
   Dim sSql As String

   If IsNull(Me.OpenArgs) Then
      'Openargs must specify parameter
      Cancel = True
      Exit Sub
   End If
   sSql = Me.RecordSource & vbCrLf & "WHERE (Lastname LIKE '%" & Me.OpenArgs & "%')"
   Me.RecordSource = sSql
End Sub

'Call the report:
DoCmd.OpenReport "Reportname", acPreview, , , , Me.LastNameFilter
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

exceterAuthor Commented:
Sorry, I use .adb

after Upsizing the adp does not recognize (@Forms___myForm___myInputBox), and instead of reading its value, it always pops up with an Inputbox and requires user to enter data.
That data is passed to query as a parameter.
And the query result will depent on that parameter.

SELECT * FROM TABLE WHERE col1 = Parameter
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The you need to rework that form's Recordsource to show the correct values. Where are you getting the value of "Parameter"?
Have you studied my sample code? It is what I use in a .adp and it works great.

You can't trust the upsizing wizard to have everything working. I generally create the reports record source, then remove all WHERE criteria and add it at runtime as in my sample code.

If you can't work it out, you could post the.adp, and the SQL Create code from the table. This is usually obtained from SQL Management Studio, right click on the table and select 'CREATE to clipboard'.
exceterAuthor Commented:
>If you can't work it out, you could post the.adp, and the SQL Create code from the table. This is usually obtained >from SQL Management Studio, right click on the table and select 'CREATE to clipboard'.

I would post it with a pleasure, but it is in Russian, I am afraid you can't open it.

the query I need is:

SELECT * FROM ContIN WHERE PostDATE BETWEEN datain1 and datein2

exceterAuthor Commented:
I I found it. but look at the following:

 WHERE (datePOST BETWEEN CONVERT(varchar,'01.01.2009',112) AND CONVERT(varchar,'31.12.2009',112))

I got it from SQL Profiler it is the query from Access Report. In Access it pops-up error:

Run-time error '242'.
The conversion of varchar data type to datetime data type resulted in an out-of-range value.

If I run the above code in Management Studio, it works fine.

Where to dig ?
This error is cause by the date conversion.

To start with hardcoding the report's recordsource to this:

SELECT * FROM ContIN WHERE (datePOST BETWEEN '2009-01-01' AND '2009-12-31')

The date format (yyyy-mm-dd) is called ISO date and you can always get Access to format an inputted (user) date like that.

exceterAuthor Commented:
tnx. solved it
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now