[AccessD] Turn a recordset into an actual table

Tue Oct 26 11:01:29 CDT 2021

<< And finally, in case I die before completing this mission, I need a date with Halle Berry. Anyone know her?>>

Can’t help you there, LOL.

<< I have been asked to expand this to allow the user to select multiple Projects *by Customer)>>

As for the rest, sounds to me like a Treeview would be something to consider.   

As an alternative, keep the current form(s), but lay on another parent form to let them select the customer / project in a list box.   When they click on one, pop-up the current form to let them to select the machines.

Then it’s either a loop for the reporting, giving one report for each customer/project selected, or some code to build up a temp table for the report(s) based on all selected customers/projects, then run the reports off that so they can handle the multiple customers/projects and do it all in one report execution.

Jim.

From: Arthur Fuller 
Sent: Tuesday, October 26, 2021 11:23 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

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 <mailto: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 <mailto: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 <mailto:accessd at databaseadvisors.com> >
> Cc: Jim Dettman <jimdettman at verizon.net <mailto: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 <mailto: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 <mailto:AccessD at databaseadvisors.com> 
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com <mailto:AccessD at databaseadvisors.com> 
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 

Arthur