Data Report, how to filter data from Access at runtime

Question

Data Report, how to filter data from Access at runtime

JLWade (Computer)

(OP)

16 Nov 02 16:39

Ok, I have gotten to the point in my VB programming where I want to start wasting paper to kill more trees, and the only way I know how to do this is by printing reports.

In visual basic there seems to be only 2 ways (without buying add ons).  Either use the printer object, (in which case you have total control and can do whatever) or use Data Reports (which are easy to make nice and pretty but, how do you let the user specify selection parameters at runtime?)

Ok so I am using data reports.  The problem is, how am i supposed to allow the user to specify selection parameters at runtime? ( Such as a date range to report). The only thing (short of copying the database and deleting the data i want to filter out) I have been able to figure out is to use a form or inputbox to get parameter values from the user and then modify the contents of
DataEnvironment1.rsCommand1.Source which has a shape definition.  (This does work)

I am thinking there must be an easier way.   If you know of an easier way or of cheap active X component I could get for doing reports please let me know.

Tarwn (Computer) · Answer 1 · 2002-11-19 10:32:48

How are you receiving the data from your database? If you are using a recordset to open a table (ADO objects) weith either a connection or command object, you can use SQL to place limitations on the data received (qualifiers and matches on specified fields in the data) so that you will only receive data that matches your query. This has the added bonus of creating less communications if you force the user to hit an "update" button before requesting the data because only the data that matches there requirements is returned to you, rather than the whole table.

If you are using VB directly built into access (I'm not entirely familiar with reporting straight from VBA, most of my clients want excel generated reports) you should look into setting the filter attribute which should allow you to filter the results to the users specifications.

Let me know if I am way off in left field here, and I'll look into it :)

-Tarwn

JLWade (Computer) · Answer 2 · 2002-11-20 12:22:11

A Data Report uses a data environment object, as the source of its recordset(s). The data envirnment can use a SQL statment(s) or more database objects each object can be a table, view (stored query), synonym (i dont know what that is), or stored procedure ( i have not messed with those yet)

I tried setting the value of
DataEnvironment1.rsCommand1.Filter
But i get a message saying "1 or more required parameters
is not set." when i do that.

The SQL text does not seem to be a property i can get to at runtime.

Anyway, If I was to do it the way you normally do, (using Excell), would I populate each cell in Excell with the data from Access? Or is there a way to tell excell to use an Access database as the source for its columns somehow?

I dont know how to modify an Access Query at runtime, or a stored procedure. Obviously I could populate a table with only the data i want at runtime, but of course the overhead would be to much.

Come Join Us!

Posting Guidelines

Data Report, how to filter data from Access at runtime