[AccessD] Multi-Select List Box problems

[Arthur Fuller]

Thanks, guys. I think that I have solved most of my problem, snd thin Iccan
finnish it today. I settled on designing a query to grab the data I need
data I need from several tables involved, then grabbed the SQL ,from the
query and pasted it into the code, so I just have to grab the list of
Workstations, which I store in a static function that returns an IN()
clause which I call as the criterion for the important field, so the result
looks (simplifying) like this:


SELECT LightCurtainData_tbl.AssessID, LightCurtainData_tbl.CompanyID,
LightCurtainData_tbl.ProjectID

FROM LightCurtainData_tbl

WHERE (((LightCurtainData_tbl.AssessID) In
(1724,1681,1524,999,974,973,1633)));


That's a concrete example. I'll have to rewrite the query each time the
process is run, which begs the, How can I access the Criterion part of a
named query? this would really simplify matters. I could just refer to the
Criteria part and substitute the current selection.


I suppose I have to research the QueryDef collection and learn how to walk
its attributes. I suppose there's an argument walkig the QueryDef but I
don't know how to grab one and examine it in detail. Too much is hidden
from my examination, so far, so let's reverse the question and ask instead,
given the name of a recordset of whatever type, how do I walk its
attributes and discover the tables and their fields? I think I've gathered
some code from the net; I just how to figure out where I stored it. I'll
try Googling MS-Access QueryDef to see if that helps.


Thanks again for your input. For now, barring unforeseen problems, I
am going to pursue this avenue: grab the Projects, grab their Workstations,
and finally the Issues related to each WorkStation. that will entail
modifying a few downstream outputs (to Excel and standard PDFl not a big
problem, just introduce a few Project headers and footers and it ought to
work.

This took more than a few hours to figure out, and being a semi-retired
writer on tech subjects, I may choose to make all this public.

On Sun, Sep 5, 2021 at 2:18 PM Daniel Waters <df.waters at outlook.com> wrote:

> Hey Arthur,
>
> I've done this same thing too - it's simple and works well!
>
> Dan
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces+df.waters=
> outlook.com at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: 5 September, 2021 10:06
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Multi-Select List Box problems
>
> When I had this problem, I iterated through the listbox (you can do this
> with just the selected items) and add create a WHERE clause MyField1 =
> value OR MyField2 = value, etc.  append that to the SQL of the query which
> opens the recordset.
>
> Crude but it works.
>
> r
>
>
>
> On Sun, Sep 5, 2021 at 6:59 AM Arthur Fuller <fuller.artful at gmail.com>
> wrote:
>
> > Actually that description is slight inaccurate. Here is a brief overview
> of
> > the situation:
> >
> > 1. A Customer  has multiple Projects
> > 2. A Project has multiple Workstations.
> > 3. A workstation may have multiple issues
> >
> > Currently the code presents the user with a combo box to select the
> > customer of interest. A multi-select list box presents the user with the
> > Projects for the selected Customer. On clicking a Project the list bo
> > x containing the Workstations for that Project. From there the user can
> > print a report that lists the issues for one selected Workstation. It is
> > written using a recordset that finds all the issues for one Worksation.
> >
> > I have been asked ti change the code so that multiple projects can be
> > selected, and within those, multiple workstations can be selected. I
> > changed the list boxes to multi-select, and a button on the form
> > accumulates the Workstation IDs and turns them into a comma-separated
> list
> > wrapped in a SQL IN() clause.
> >  and the multi-select code
> > This all works fine. But I'm not sure how  to bridge the gap between the
> > recordset code, which was written originally to deal with only one
> > Workstation at a time.
> > I'm not tied to the SQL IN() approach. I've also considering using an
> array
> > or a collection of Workstation IDs. One thing that occurred to me is to
> > expand the recordset to include all the selected workstations rather than
> > dealing with them one by one. One downstaream advantage is that the final
> > report could do the whole thing in a single pass, and generate a single
> > report.
> >
> > Any thoughts on this subject?
> >
> >
> > Currently the code
> >
> > --
> > Arthur
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
Arthur