Trying to get fancy

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

So, my users are asking to run their reports, integrity check query displays,
etc. by selection criteria. Say, for example, they want to run an integrity
check query that selects 1 or more "ProjectStatus" values. I've been
interposing a form that presents the project status choices in a list box and
lets them choose. Then I execute the form or report out of that form.

But query displays are different. And I'm tired of reinventing the wheel. I
want to have a generic "choose Project Status" routine that returns a string
-- "(3,5,7,8)" for project statuses 3, 5, 7 and 8. I can then use the "IN"
operator to code the where clause in my SQL statement or domain aggregate
call.

I can't get by the notion that a function isn't a form, so can't present a
list box control and return the selections. I could have the function "call"
the form, but how to return the results to the function? What's your
solution?
 
Hi Jim,

I think the easiest way to deal with that is when the user clicks the
button on the form for the report to create the in clause and pass it as
criteria when opening the report.

Dim strCriteria As String

' Code to construct the in clause

DoCmd.OpenReport "rptMyReport", acViewPreview, , strCriteria

Clifford Bass
 
I have always added a Boolean field to such tables and used a fake listbox
in the form of a continuos form
I maintain the "state" between reports and have a "Select All" and "Clear
All" button under the box.
One nice thing is that they can come back and run it again without having to
go through the selection process again.

They check what they want to see and run the report from the form.
It can work well for two or more choices as in "I want to see tests A,B,C
done by doctors Smith, Jones and Death between this date and that date."

No code to write and you just add a Field = True to teh query.
 
JimS said:
So, my users are asking to run their reports, integrity check query
displays,
etc. by selection criteria. Say, for example, they want to run an
integrity
check query that selects 1 or more "ProjectStatus" values. I've been
interposing a form that presents the project status choices in a list box
and
lets them choose. Then I execute the form or report out of that form.

But query displays are different. And I'm tired of reinventing the wheel.
I
want to have a generic "choose Project Status" routine that returns a
string
-- "(3,5,7,8)" for project statuses 3, 5, 7 and 8. I can then use the "IN"
operator to code the where clause in my SQL statement or domain aggregate
call.

I can't get by the notion that a function isn't a form, so can't present a
list box control and return the selections. I could have the function
"call"
the form, but how to return the results to the function? What's your
solution?
Click to expand...

You could use code similar to below in your function:

Dim rtn As String
Dim item As Variant

With Forms!ProjectStatus!ListboxName
For Each item In .ItemsSelected
rtn = rtn & "," & item
Next
rtn = Mid(rtn,2) 'Strip off 1st comma
End With
rtn = "(" & rtn & ")"

(Untested air code)
 
Hi Mike,

Nice idea, I may adapt it for some circumstances. Question: Do your
users run into issues when more than one user it trying to run the same
report at the same time?

Clifford Bass
 
Hi Mike,

Nice idea, I may adapt it for some circumstances. Question: Do your
users run into issues when more than one user it trying to run the same
report at the same time?
Click to expand...

One way to get around that is to have a local table in the frontend of your
split application, related one-to-one to the table for reporting; you would
use a form based on an outer join, thereby creating a "report me" record only
when needed.
 
Hi John,

That sounds better to me! The idea of modifying records within the
database everytime you wanted to do a report did not seem like the best way
to go. It could cause troubles for someone who is trying to modify actual
data. Or troubles related to date/time records were last modified.

Thanks,

Clifford Bass
 
Hi Marshall,

I think I like that one also. Thanks for pointing it out!

Clifford Bass
 
Back
Top