ADP Report Using Recordset Property 1

[rlieving]

I am currently programming an application using Access 2003 ADP/SQL2K. I want to use a simple stored procedure to return records to a report. The only way I found to do this is to create an ADO (v2.7) recordset in the Open event of the report, then set the recordset property to the recordset I want.

This works great for reports that do not have any grouped headers or footers. However, grouped reports are another story. When adding a grouped header or footer to the report, none of the data is displayed and the header field, which could be previously displayed, cannot be found. In other words, you get nothing.

I tested this method by printing out the recordset to the immediate window, and the data is present in the recordset. I have tried changing the locks, the cursor type, etc...but cannot get this to work. This is not a problem that has a lot of coverage on the web.

In case anyone suggests using views, I am trying to use stored procedures for security purposes. What I am finding, much to my suprise and horror, is that Access reports just don't play well with SQL Stored Procedures. Anyone have any ideas?

Thanks.

 

[cmmrfrds]

Did you try basing the report on the stored procedure instead of the recordset. i.e. RecordSource = exec dbo.sp

 

[rlieving]

I have a form opening in the Report 'open' event to gather parameters (a date range, if desired and a status or group of statuses) and have not figured out a way to pass the values as parameters back to the sp. Filters do not work with stored procedures - only views and tables. I do not want to expose the entire view or table in the ADP. So, the answer is 'yes', I've tried but it is not getting me anywhere.

Right now, the solution I am following is to create a 'Reporting' adp, with rights to the view apart from the general user pool, and using server-filter to generate the report subset I want. It's not the best - but ADP security isn't either and I am trying to move on as fast as possible.

If you have ideas for passing parameters dynamically, I'm all ears.

 

[cmmrfrds]

I am not understanding. Why doesn't this work?

Under the Data Tab.

Record Source dbo.sp_OutputToInvoiceReport

Input Parameters @InvoiceID=forms![frm_OpenInvoices]![invoiceHeaderID]

 

[rlieving]

I gave you a star for having me try harder. I am getting it to work (by passing defaults to the other parameters), although now I have another problem. The problem is I use the stored procedure in question in several procedures. In one procedure in particular, I load only particular fields in a temp table for display on the screen. The problem is, Access (and/or SQL) 'remembers' the previous field selection and only displays the select fields from the previous run.

If I run the query in SQL Query Analyzer, it displays all of the fields. Any ideas on how to get Access to 'see' all of the fields again?

 

[rlieving]

I solved the fields problem (my error) and the default problem (pass defaults to the database). I CANNOT get Access to see the form with the ID parameter, though. I used the string format provided above plus any additional combination I could think of.

Access keeps prompting me for the ID, no matter if the form is open or closed - which is completely unacceptable. This brings me back to my original point - the InputParameters property does not always work and that's why I didn't use it in the first place.

 

[cmmrfrds]

Access keeps prompting me for the ID, no matter if the form is open or closed - which is completely unacceptable."

Can you paste in what you have in the data tab.

Also, how and where any variables that are used are defined.

Also, The parameter part of your stored procedure.

 

[rlieving]

I found the problem. I was declaring the form as a variable, then setting it's property visible = true. It turns out that the bang method (Forms!frmX!txtId) cannot be used to reference a field UNLESS you open a form via the DoCmd.OpenForms method.

Once I changed the form format, everything worked fine. That brings it's own set of problems, but is the way I am going to move. Do you have any idea why a bang works with a form opened via docmd vs. not working with a form opened as a variable? Very strange....

 

[cmmrfrds]

Generally, the dot operator can/is used with objects, method, etc... that can be identified at compile time. Whereas, the bang operator is used for things that are identified at run time, such as, the fields that make up a recordset. Access must be waiting until run time to identify the fields in the OpenForm. Probably once the Form is available as a variable the field names are known. Those are my thoughts on it.

 

[LuckyLuciano]

If I remmember properly i had the same problem a while ago.

Try to rebind all controls (setting the controlsource for each control from code).

It is possible that you have to unbind first and then rebind.

Hope this helps you

greetzzzzz