Running a report based on SQL source at runtime...help!

[srs09]

Hi,

I am new to access 2003 and have been building a database to track internal projects with the help of books and internet resources.

My current scenario:
I have a report whose source is a query that a user builds on the fly via a form interface that I created. After the user hits submit on the form interface, I have vba code which builds the appropriate SQL, sends it to the stored query to apply the SQL, then closes the query window immediately. Then the report would open based on the stored query, and the user sees the result. So, the user fills out a form, and sees a report at the end with the criterias specified without being bothered by what's going on in the background.

What I would like:
My current scenario is somewhat roundabout and very slow. Is there a way to build the SQL off of the form interface, and set the code as the record source for the report, so that the report would open showing the criteria specified?

Edit: I am looking to make the report's source the SQL statement itself, that is, a way to pass the SQL statement build on the fly into the report's source at runtime.

Thanks for your help!

 

[pbaldy]

It would help to know more about what you're doing. Options include using the wherecondition arguement of OpenReport, similar to this:

http://www.baldyweb.com/wherecondition.htm

The wherecondition is basically an SQL WHERE clause without the word WHERE, so they can be fairly complex. Another option is passing the full SQL to the report in OpenArgs, and setting the source to that string in the open event.

 

[Alisa]

Typically what you would do is use the relevant data from the form in a saved query, and then set the recordsource of the report to the query. Your query could be something like SELECT * FROM WhateverTable WHERE SomeField=" & Forms!YourFormName!YourFormField etc. Is that what you are asking?

 

[srs09]

Typically what you would do is use the relevant data from the form in a saved query, and then set the recordsource of the report to the query. Your query could be something like SELECT * FROM WhateverTable WHERE SomeField=" & Forms!YourFormName!YourFormField etc. Is that what you are asking?

This is my current scenario, where the report's source is the saved query. I am looking to make the report's source the SQL statement itself, that is, a way to pass the SQL statement build on the fly into the report's source at runtime.

 

[srs09]

...Another option is passing the full SQL to the report in OpenArgs, and setting the source to that string in the open event.

Hi, I think this is what I am looking for! Can you help me get started? In the meanwhile, I am going to look into how to do this...what you say here is familiar but I have not been working with reports through vb.

Thanks!

 

[msrichards]

Create a textbox control on your form and set its visible property to not visible. You probably have a 'View' button. On the code behind the click of the button that opens the report, set the sql command string you have built to the text box value. Then open the report which will use the value of the new control as its recordsource and have the report close the form.

(Example: form control is called txtSource, form is called Form1).

In your report, upon clicking the view button
txtSource.Value = strMySQLCmd

.. then open the report. but do NOT close the form.
In your report, in the Report_Open event, include these lines:
Me.RecordSource = Forms!Form1!txtSource
Docmd.Close acForm, "Form1"

This will get the recordsource from the form and then close the form.

 

[srs09]

...This will get the recordsource from the form and then close the form.

The solution you have offered here is within the scope of my understanding so I will have no trouble implementing it when I have access to the database late next week.

However, is there a way where I can simply pass the strSQL that the user build on the fly via the form interface to the report source via vba code when the user hits submit button, without the use of the text-box control?

Will update on how it goes. The idea behind this is exactly what I am looking for, thank you!

 

[msrichards]

There is no method for reports similar to the OpenArgs that you have for forms. I have never figured out why Microsoft has never created an OpenArgs for reports. So, you have to use something like this to pass anything to a report. However, this is an efficient use of resources, so I would not hesitate to use it.

 

[srs09]

There is no method for reports similar to the OpenArgs that you have for forms. I have never figured out why Microsoft has never created an OpenArgs for reports. So, you have to use something like this to pass anything to a report. However, this is an efficient use of resources, so I would not hesitate to use it.

I am sold

 

[pbaldy]

There is no method for reports similar to the OpenArgs that you have for forms.

That is not accurate. OpenArgs was added to reports in either 2002 or 2003.

 

[msrichards]

That is not accurate. OpenArgs was added to reports in either 2002 or 2003.

Well I'll be. You learn something new everyday! I have used every version of Access through 2003 and never picked up on that. Thank you, pbaldy!

For srs09 that means all you need to do is

docmd.OpenReport "Report1",OpenArgs:=strMySQLCmd

My apologies for the wrong info.

 

[pbaldy]

Thank you, pbaldy!

No problemo; there's probably only 10,000 new features that I'm not aware of.

 

[srs09]

...For srs09 that means all you need to do is

docmd.OpenReport "Report1",OpenArgs:=strMySQLCmd

My apologies for the wrong info.

That's no problem at all, I am already thankful for any help!

As for the code, it appears to work, but instead of opening the report, the report seems to get sent to the printer...

Any suggestions?

 

[boblarson]

DoCmd.OpenReport "Report1", acViewPreview,OpenArgs:=strMySQLCmd

 

[srs09]

That's no problem at all, I am already thankful for any help!

As for the code, it appears to work, but instead of opening the report, the report seems to get sent to the printer...

Any suggestions?

I poked around a little bit, and the following works!

'On Submit Click

DoCmd.OpenReport "testing_openargs", acViewPreview, , , OpenArgs:=strSQL

'On Report Open

Me.RecordSource = OpenArgs

There is a huge difference in terms of speed between this and my previous method. Thank you so much for your help, I have successfully gotten it to work!

 

[bohemian9485]

You can design query in the report recordsource by clicking the ellipse button at the right side of the recordsource property. This will bring up the query design window. Then in the click event of the commandbutton that will show the report:

Dim strDocName As String
Dim strLinkCriteria As String
strDocName = "NameOfReport"
strLinkCriteria = "FieldName1=" & Me.Control1 & " AND FieldName2=" & Me.Control2...etc.
DoCmd.OpenReport strDocName, acPreview, , , strLinkCriteria

 

[msrichards]

After some successful use of Openargs with reports I did find one drawback: apparently Docmd.OutputTo does not have an Openargs argument. So, if you want to use the same report for both printing and exporting, the report will ignore the Openargs value when it is used with OutputTo, which means the report will not be correct. Same is true for Docmd.SendTo.

 

[srs09]

After some successful use of Openargs with reports I did find one drawback: apparently Docmd.OutputTo does not have an Openargs argument. So, if you want to use the same report for both printing and exporting, the report will ignore the Openargs value when it is used with OutputTo, which means the report will not be correct. Same is true for Docmd.SendTo.

Thanks for info, will watch out for that!