Getting Criteria for Query from VB

[Amanda Payton]

I have a parameter query that I would like to "automate" (if you will) by
using VB to pass criteria to a parameter query based on a command button.

Basically - when my user clicks one of several buttons, I want a report
(based on a parameter query) to open, but I don't want the user to be
prompted for criteria, I want VB to supply it at the OnClick event from the
command button.

The report opens fine, and such, but right now I've got a seperate report
and query for each button - and I'm accumulating enough queries and reports
that:
A) it's getting to be obnoxious surfing through the list to find what I want
to edit
B) when I edit the format/layout of 1 report, I have to edit all the others
to match

when I had 3 task buttons, it wasn't so bad... but now I've got 12.... not
so fun...
I know a parameter query/report combo is the way to go - but like I said - I
don't want my users to be able to enter just anything for a parameter...
Especially after clicking a button that (logically) SHOULD have all
parameters defined.

Ideas? thoughts? HELP!

-Amanda

 

[Steve Schapel]

Amanda,

Yes, this is certainly possible, and as you so rightly point out, highly
desirable.

It is not clear from your post what the query criteria are supposed to
be, or where the "Task buttons" are supposed to get the criteria values
from. If you need more specific help, please post back with some more
details, maybe with examples.

 

[Amanda Payton]

Steve -

I have a table containing the following fields: (and others)
Task
CustomerName
IDNo
Associate
DueDate

Depending on the report, I want to find either a specific name, or a
specific task. I then have a switchboard style form (it's not currently a
switchboard, but I intend to convert it to one soon) with several buttons.
All but one are related to a specific task. The remaining one is related to
the associates. I.E. There is a button titled "Bill of Materials Report"
that opens a report displaying all records where "Bill of Materials" is the
task. Same concept with "Unit Orders" and "Body Orders" etc.

What I want to have is:
1 report based on 1 parameter query based on the main data table.
I want the query to "ask" what task I want to display, so that I can use
that query for any task I choose.
I don't want my user to see the parameter prompt box. I want the code in
the command button to contain the correct information to feed the parameter
as it opens the query.

I.E. When I click "Bill of Materials Report" button, the code should
contain something that says ' the task for the parameter = "bill of
materials" '

All I don't understand how to do is use VB to tell the parameter "here's the
information you're asking for". Mostly - I just don't know how to use VB to
refrence a parameter prompt... I think that's what I want to do...
whatever the method - I need some way to get Information A in Slot B as it
were....

Does this give you a better idea of what I'm working with?? (I'm in '97 btw)

Thanks!

Amanda

 

[Steve Schapel]

Amanda,

Ok, thanks for the further explanation.

First of all, forget the Parameter Query. In fact, forget putting
criteria in the query. Put code like this on the Click event of your
"Bill of Materials" command button...
DoCmd.OpenReport "YourReport", , , "[Task]='Bill of Materials'"
.... and the equivalent for each other button.

Another approach is to just have one command button to print the report,
and the task selected in a combobox or an option group. If a combobox,
the command button Click code probably will be like this...
DoCmd.OpenReport "YourReport", , , "[Task]='" & Me.NameOfCombo & "'"

If an option group, like this...
Select Case Me.NameOfOptionGroup
Case 1
DoCmd.OpenReport "YourReport", , , "[Task]='Bill of Materials'"
Case 2
DoCmd.OpenReport "YourReport", , , "[Task]='UnitOrders'"
... etc
End Select