|
|
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?
--
Jim
|
|
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
"JimS" wrote:
[Quoted Text] > 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?
> --
> Jim
|
|
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 wrote:
[Quoted Text] > 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?
|
|
"JimS" <JimS[ at ]discussions.microsoft.com> wrote in message
news:C4EDDD43-2651-4857-9CE1-E74A61E5A26D[ at ]microsoft.com...
[Quoted Text] > 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?
> --
> Jim
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
"Mike Painter" wrote:
[Quoted Text] > 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 the query.
|
|
On Sat, 20 Dec 2008 12:50:00 -0800, Clifford Bass
<CliffordBass[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >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?
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.
--
John W. Vinson [MVP]
|
|
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
"John W. Vinson" wrote:
[Quoted Text] > On Sat, 20 Dec 2008 12:50:00 -0800, Clifford Bass
> <CliffordBass[ at ]discussions.microsoft.com> wrote:
>
> >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?
>
> 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.
> --
>
> John W. Vinson [MVP]
|
|
Clifford Bass wrote:
[Quoted Text] > 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?
Another way is to use a collection. See Multi Select
Example at
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
--
Marsh
MVP [MS Access]
|
|
Hi Marshall,
I think I like that one also. Thanks for pointing it out!
Clifford Bass
"Marshall Barton" wrote:
[Quoted Text] > Clifford Bass wrote: > > 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? > > > Another way is to use a collection. See Multi Select > Example at > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html> > -- > Marsh > MVP [MS Access] |
|
|