Best Practice for Passing Parameters from Form to Stored Procedure

Question

Best Practice for Passing Parameters from Form to Stored Procedure

hydera

SSC-Addicted

Points: 410
More actions
July 13, 2005 at 8:59 am

#186593

I have a form which calls a report via DoCmd.OpenReport "rptTestReport", acViewPreview. The report has a stored procedure as its record source which generates the reports contents. I want to pass a start and end date from the form into the SP to specify the date range of the report.
Is anyone able to explain the best way to pass the date parameters from the form into the SP which is referenced by the report?
Thanks,
hydera

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Create two textbox (one per parameter) and set the values there. Then in the report's input parmater property add something like this :

Forms!FormName!TxtDateStart, Forms!FormName!TxtDateEnd.

Then the report will show the correct data range automatically.

You might want to add a check on the report_open() to make sure the form is opened or it'll throw an error.

hydera SSC-Addicted Points: 410 More actions · Answer 2

Remi,

Thanks for your much needed help, how do I keep the form open while the report is executing the SP so it is able to pass the parameters?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

It only needs to be opened at the moment you open the report... once the query is passed on the server you can close the other form.

Dick Schroth Right there with Babe Points: 725 More actions · Answer 4

An alternative in newer versions of Access is to pass either the entire SQL statement or the parameters as opening arguments (OpenArgs) to the report. Then in the report open event you set the record source based on what is passed in. This has the advantage of allowing the report to be called from anywhere, and not just from one form.

Dick Schroth

http://www.schrothsystems.com

Paul E SSC Enthusiast Points: 134 More actions · Answer 5

as always in sw development (if you dare to include working with access in this category 😉 ), the answer is "it depends" - there are 3 methods i might use, depending...

1) Referencing a form textbox is good if it is something like a date-range pop-up to limit report range , then closing it when the values have been excerpted (per Remi). In your case, I would choose this.

2) OpenArgs is a bit cleaner in my opinion, but has the disadvantage of limiting yourself to one parameter, or having to create a parsing routine to separate a string such as "John, 289" into "John" and "289"

3) An approach I use (which I apply both to forms and reports) is to use global variables for items which are used frequently. This is most appropriate when doing extensive work (entry/reporting) on one "entity." For example, if you are editing and reporting on customer #1234, upon selection of that customer (at some initial point), save 1234 (and any additional info like "John Smith") into global variables and refer to them as you traverse forms and reports - it's less of a pain than having to refer to the form you had open three forms ago, or to requery a database to get a name string that you just "knew" about a second ago.