OpenArgs vs. Tags

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm working my way out of a very bad situation believing that somehow there
must be a better way to accomplish the task than the solution I've got right
now.

I have appr. 10 reports that users would like to have filtered by topic, and
have ended up with 10 different copies of the same parameter form so that I
can reference the name of the form in the query for the report.

Each parameter form is launched from a different command button in the
database, and I would think first to use OpenArgs to pass the filter criteria
to the report, but I am already using OpenArgs to pass values to the (single)
combo box on the form. I'm unfortunately too unskilled to know how to pass
more than one parameter with OpenArgs....

I've seen instances where the Tag property can also come in handy in these
situations, but don't know where to start. Any suggestions?

Many thanks,

Scott A
 
I would use OpenArgs. Just build a string that contains all the individual
parts separated by a delimiter (often, ; or | is used).

Then, in the report, create a module-level global variant variable and then
use Split function in the report's Open event to parse the OpenArgs string:

Private varOpenArgsArray As Variant

Private Sub Report_Open(Cancel As Integer)
varOpenArgsArray = Split(Me.OpenArgs, "|")
' then have code that uses the different values in the array
End Sub
 
I as a rule NEVER use parameters in queries, and attach that parameter to a
form.

The problem with the above is that while it is easy to do, you wind up with
a new form for each query. You also tend to wind up with a different report
for just changing a few things.

So, what is the solution? The solution here is to use a TINY bit of code.

So, strip out all of the parameters, and use what is called the "where"
clause in the open report. This feature was designed to solve the above
exact problem.

Also, the same concept also works for opening forms..and again, you
can/should be using the "where" clause of the openform.

So, lets assume we have a "prompt" form for a sales report, and we want to
restrict the report to salesman, and also by city. So, we make a nice
un-bound form with two text boxes on it. (txtCity, and txtSalesMan).

Now, for the button that launches the sales report you go:

dim strWhere as string

if isnull(txtCity) = false then

strWhere = "City = '" & txtCity & "'"

end if

if isnull(txtSalesMan) = false then

if strWhere <> "" then
strWhere = strWhere & " and "
endif

strWhere = strWhere & "SalesMan = '" & txtSalesMan & "'"
end if

docmd.OpenReport "salesReprot",acViewPreview,,strWhere

Note that you can continue (add more conditions) as above for as MANY
parameters you want. And, if the user leaves the parameter blank, then you
simply get all. So, for he above, you can enter a particular sales man name,
but not the city. Or, you can just enter the city for the sales. Or, your
can enter SalesMan, and city for the repot. You can rapidly see that we did
NOT have to make a zillion queries, a zillion reports etc.

So, just remember, the 'where' clase is a VALID sql where clause, but
without the word "where". If the values were not a string, then you go

"SalesManID = " & cboSalesMan

Here is some screen shots that uses the above exact idea in code:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html
 
Back
Top