Recordsets, parameters and reports

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SAm - 14 Nov 2006 15:02 GMT

Hi

I asked a question last week and i got no repsonses. I will reorganize my
question.

i would like to set a parameter of a query with VBA, so that i can run a
report of that recordset.

i know i can get the recordset by creating a querydef and then setting the
parameters. what i don't know is how to proceed. i need this to be the
recordsource of my report. how do i do that.

thanks,

Sam

Reply to this Message

Douglas J. Steele - 14 Nov 2006 15:09 GMT

Rather than trying to set the parameters with VBA, have the query refer to
text boxes on a form (the form doesn't have to be visible, just open).
Populate the fields in that form via VBA, then open the report.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi
>
[quoted text clipped - 11 lines]
>
> Sam

Reply to this Message

SAm - 14 Nov 2006 15:29 GMT

thanks douglas for your quick reply, but this will not work. basically i need
to loop for through a set of multiple parameters. i have the user choose a
category and then i loop through the whole category each time i run the query
with a different parameter value. then i accumulate all the runs in a
recorset and then display it on the report.
last week i detailed the project, and it seemed too confusing and i got no
answers.

sam

> Rather than trying to set the parameters with VBA, have the query refer to
> text boxes on a form (the form doesn't have to be visible, just open).
[quoted text clipped - 15 lines]
> >
> > Sam

Reply to this Message

Douglas J. Steele - 14 Nov 2006 15:38 GMT

How do you "accumulate all the runs in a recordset"?

If you're using Access 2003, you can set the report's Recordset property
(not its RecordSource property). See
http://msdn.microsoft.com/library/en-us/vbaac11/html/acproRecordset_HV05251093.asp
for details)

Other than that, I think you're out of luck (unless you save the results of
the individual queries to a temporary table, and then base the report on
that temporary table).

Just a piece of advice for future posts: please don't expect us to refer
back to other posts to get details. I read literally hundreds of posts a
week, and there just isn't time to go looking for other ones. At least find
your post at http://groups.google.com and post a link to it.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> thanks douglas for your quick reply, but this will not work. basically i
> need
[quoted text clipped - 31 lines]
>> >
>> > Sam

Reply to this Message

SAm - 14 Nov 2006 16:00 GMT

thanks for the answers. i think i will start by trying the recordset
property. i would like to add that i use the method of writing to a temporary
table constantly. i think this time is different since the initial queries
and very complex. i use two crosstabs and then a union to join the results. i
then sum the results to get my results.

this post has gotten me to think even if i didn't get my direct answer. i
would like to add, if someone is reading this post that i found through
google a relevant post. check out:
http://www.thescripts.com/forum/thread201278.html

thanks a lot,

Sam

> How do you "accumulate all the runs in a recordset"?
>
[quoted text clipped - 47 lines]
> >> >
> >> > Sam

Reply to this Message

SAm - 15 Nov 2006 16:24 GMT

Hi

in the report_open sub, i created the recordset and then i had this line:
Me.Recordset = rstEmployeeType
i got an error, run time error '2593': This feature is not available in an MDB
can you help me with this one? i am running 2003, VB version 6.3 i think i
am current with all updates - SP2.

you other solution of creating a temp table is what i usually use, but this
time i am trying to save time and i am trying to avoid this.

i don't think it makes sense to create an SQL string which would be a long
union query. just don't find them efficient (i only use as last last last
resort).

please help,

thanks,

sam

> How do you "accumulate all the runs in a recordset"?
>
[quoted text clipped - 47 lines]
> >> >
> >> > Sam

Reply to this Message

Klatuu - 14 Nov 2006 15:40 GMT

Depending on the complexity of the criteria, it is possible you could combine
all the criteria into one use And and Or in your filtering. Sometimes, this
is not achievable. If not, it takes some additional work. Can you describe
what a category means, and some examples of the different criteria you would
be applying?

> thanks douglas for your quick reply, but this will not work. basically i need
> to loop for through a set of multiple parameters. i have the user choose a
[quoted text clipped - 25 lines]
> > >
> > > Sam

Reply to this Message

Klatuu - 14 Nov 2006 15:20 GMT

There are more than one way to do this. I don't know how complex your query
is, but the easiest approach is to use the Where argument of the OpenReport
method to filter the data for the report. It has the same net effect as
applying parameters to a query and is actually more flexible. Let's say, for
example, you want to limit your report to people with blue eyes that wear
size 7 shoes.

Dim strWhere As String

strWhere = "[EyeColor] = '" & Me.txtEyes & "' AND [ShoeSize] = " &
Me.txtShoes

Docmd.OpenReport "MyReportName", , , strWhere

You could do the exact same thing by setting criteria on the field in your
query that would point to the controls on the form the report is called from,
provided the form stays open while the report is running.

EyeColor ShoeSize
[Forms]![MyForm]![txtEyes] [Forms]![MyForm]![txtShoes]

Now, the advantage of using the OpenReport Where argument is that you don't
have to filter on anything and you are not required to filter on both fields.
(Yes, there are ways around this. It requires more complex criteria.)

> Hi
>
[quoted text clipped - 11 lines]
>
> Sam

Reply to this Message