[AccessD] Turn a recordset into an actual table

[Arthur Fuller]

Perhaps I am asking all the wrong questions. Interesting and educational as
this thread is, I don't seem to be getting closer to the solution of my
current problem. A little background:
The  app concerns a discipline called Safety Assessment Engineering. The
top of the hierarchy is Customers, then Projects, then Workstations
(machines), then Risks and finally Solutions (maximum six per Risk).
The existing code works well. Depending on the number of Machines as
revealed by a DCount() call, we choose among several report layouts, and in
addition also do an Export to Excel/ The original code, which a colleague
and I wrote, is fired from a button on a form, which assumes that exactly
one Project has been selected. Having selected the Project of interest, the
user is presented with a list of its Machines.
The code depends on the existence of a named RecordSet. The current
implementation anticipates a RecordSet whose scope is a single Project, and
may or may not include all Machines associated with that Project.
I have been asked to expland this to allow the user to select multiple
Projects *by Customer) and then multiple Machines within each project. I
have created a dialog which contains a pair of Extended Select  ListBoxes,
and also a button that builds a SQL In() clause from the selected Machine
IDs. I plug the resultant In() clause into a queryDef. But I want to be
able to browse the result as if it were an actual table. Downstreaam there
will be consequences in terms of report formatting and so on, and I'm ok
with that. I just wish that after creating the RecordSet with multiple
Projects and Machines, that I could browse it and check for (in)accuracy.
To begin with, I need a tool that, given the name of a QueryDef, shows me
the SQL. From that I can build a table and browse it and ensure that
everything is working correctly.
And finally, in case I die before completing this mission, I need a date
with Halle Berry. Anyone know her?

On Tue, Oct 26, 2021 at 9:43 AM Bill Benson <bensonforums at gmail.com> wrote:

> Well explained thanks Jim.
>
> On Tue, Oct 26, 2021 at 9:14 AM Jim Dettman via AccessD <
> accessd at databaseadvisors.com> wrote:
>
> > Bill,
> >
> > No because there are several problems with creating controls at run-time.
> >
> > Forms have a lifetime limit on the number of controls you can create.
> >  Once you hit that limit, you can’t create any more controls.   You can
> > avoid that if you create the form from scratch each time though.    Note
> > also that the form needs to be in design view to create controls, so you
> > must be using a .accdb (no .accde allowed).   Typically, you’ll want to
> > have code associated with the form/controls as well.   While you can add
> > the code, it will put your project into an un-compiled state and run
> slower
> > overall.    You can work around that by calling code from the properties,
> > but that’s more work.
> >
> >  Given all that, I never found it worth the effort.   It’s far easier to
> > simply put a bunch of hidden controls on a form, then unhide and
> > move/resize and set properties as needed.
> >
> >  In general, you would only want to use CreateForm and CreateControl if
> > you were writing some type of Wizard.
> >
> > Jim.
> >
> > -----Original Message-----
> > From: AccessD On Behalf Of Bill Benson
> > Sent: Tuesday, October 26, 2021 8:22 AM
> > To: Access Developers discussion and problem solving <
> > accessd at databaseadvisors.com>
> > Cc: Jim Dettman <jimdettman at verizon.net>
> > Subject: Re: [AccessD] Turn a recordset into an actual table
> >
> > Jim have you tried the method indicated here?
> >
> >
> https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb237827(v=office.12)?redirectedfrom=MSDN
> >
> > On Tue, Oct 26, 2021 at 7:49 AM Jim Dettman via AccessD <
> > accessd at databaseadvisors.com> wrote:
> >
> > >
> > >
> > >  There is nothing like this anywhere in Access.
> > >
> > >  When it comes to forms, for a dynamic record set, you can:
> > >
> > > 1. Have a series of un-bound controls, check the record set fields
> > > collection at the forms OnLoad, and then populate the controls as
> needed
> > > hiding the ones that you don't want to use.
> > > 2. Fill a temp table and point it to that.
> > >
> > >  For combo's and list boxes, you can
> > >
> > > 1. fill them manually by row and column with a call back function
> > > 2. fill the recordsource with a value list
> > > 3. Use a temp table and point it to that.
> > >
> > >    Unfortunately, Access doesn’t have a "put record set" command unlike
> > > other products (i.e. VFP).    It has a GetRows() function to bring
> stuff
> > > in, but not the corresponding Put.
> > >
> > > Jim.
> > >
> > <<snip>>
> >
> > --
> > 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