Access report does not work after Upsizing to SQL SERVER

Hi

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 ?
LVL 3
exceterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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.
0
borkiCommented:
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...


0
borkiCommented:
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
      Beep
      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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
0
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"?
0
borkiCommented:
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'.
0
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

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

SELECT * FROM ContIN
 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 ?
0
borkiCommented:
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.

0
exceterAuthor Commented:
tnx. solved it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.