[AccessD] Turn a recordset into an actual table

Bill Benson bensonforums at gmail.com
Tue Oct 26 13:11:31 CDT 2021


That is exactly what I was implying but someone wrote here that the
controls have to be defined ahead of time to receive the field data from
the recordset.

On Tue, Oct 26, 2021 at 2:04 PM Rocky Smolin <rockysmolin2 at gmail.com> wrote:

> I have something (Ithink is) similar. I created a sub=form on a form, type:
> datasheet, but without a record source.  The user can then select from a
> combo box list of tables containing disparate types of data.  And that then
> is set as the record source for the subform in the After Update event of
> the combo box. The subform is then populated by the data in the selected
> table.  The field widths are all sizeable so you don't have to worry about
> setting thm, although you could do that through code. And the record source
> of the sub form doesn't have to be a table - could be a query, or anything
> that would create a record set.
>
> Would that work?
>
> r
>
> On Tue, Oct 26, 2021 at 10:18 AM Bill Benson <bensonforums at gmail.com>
> wrote:
>
> > Again, my response (and the follow up question) was for Arthur's benefit,
> > not because I have any need for this at the moment.
> >
> > Since trying to create a data sheet that would work for any kind of
> > recordsource was my aim, I started to wonder whether this could be
> > all-purpose, or would be limited to displaying only fields that are added
> > to the Form at design time. Based on what I had read online I was under
> an
> > impression I could simply have the parent form's code determine what
> fields
> > would need to be displayed from the recordset itself, and create those
> > fields dynamically before hooking up the subform's recordsource. Based on
> > what Jim said, this would begin to fail after a certain number of
> controls
> > had been added. I accepted Jim's response as final.
> >
> > That said, in the back of my mind, there is me kinda wondering why the
> > subform itself could not sit there empty of any recordsource on the
> parent
> > form, and then the subform itself gets built at runtime, and one never
> > saves the subform, so there is no risk of exceeding the lifetime control
> > limit.
> >
> > I am not in a position to test whether this is even possible. I suspect I
> > would need to save the subform. I can pretty much guarantee I have
> written
> > code on forms to open other forms in design view and modify them while
> VBA
> > is running. What I can't remember was (1) whether those had to be saved
> > before they could be used (I suspect so - probably saving the form is
> what
> > adds it to the navigation pane); so I think my idea was not necessarily
> > implemable.
> >
> > On Tue, Oct 26, 2021 at 1:10 PM Rocky Smolin <rockysmolin2 at gmail.com>
> > wrote:
> >
> > > > Oh, hypothetical. Ok.  Well, it seems that if the recordset can be
> > > > created, that the datasheet would be the universal solution.  The
> > column
> > > > heading are taken from the field names, whatever they are. Where
> would
> > > the
> > > > datasheet approach fall short in what you're trying to accomplish?
> > > >
> > > > r
> > > >
> > > > On Tue, Oct 26, 2021 at 10:01 AM Bill Benson <bensonforums at gmail.com
> >
> > > > wrote:
> > > >
> > > >> Not sure what you are asking. I wrote with a hypothetical app in
> mind.
> > > If
> > > >> I
> > > >> needed to see the results of a recordset (based on a query) I could
> > > create
> > > >> that with any kind of query access allows except action queries.
> > > >>
> > > >> On Tue, Oct 26, 2021 at 10:59 AM Rocky Smolin <
> rockysmolin2 at gmail.com
> > >
> > > >> wrote:
> > > >>
> > > >> > Bill:
> > > >> >
> > > >> > Where does the data come from that your app is supposed to
> display?
> > > >> >
> > > >> > r
> > > >> >
> > > >> > On Mon, Oct 25, 2021 at 10:25 PM Bill Benson <
> > bensonforums at gmail.com>
> > > >> > wrote:
> > > >> >
> > > >> > > Not so sure Rocky, what about recordsets based on queries with
> > UDFs?
> > > >> > >
> > > >> > > And I think CreateControl method will build the fields on the
> fly
> > on
> > > >> the
> > > >> > > subform.
> > > >> > >
> > > >> > > On Tue, Oct 26, 2021 at 1:08 AM Rocky Smolin <
> > > rockysmolin2 at gmail.com>
> > > >> > > wrote:
> > > >> > >
> > > >> > > > Well, the fields need to be defined somewhere.
> > > >> > > >
> > > >> > > > If you want to dynamically display any recordset, then the
> > fields
> > > >> would
> > > >> > > > have to be defined in the tables from which the recordset is
> > > >> created.
> > > >> > Or
> > > >> > > > perhaps a spreadsheet?
> > > >> > > >
> > > >> > > > I suppose you could set the record source of the datasheet to
> > the
> > > >> > > recordset
> > > >> > > > defined by a table or a query, and it would populate the
> > datasheet
> > > >> just
> > > >> > > > like opening up an Excel spreadsheet.
> > > >> > > >
> > > >> > > > But this ability to display any dataset seems a bit undefined.
> > > Can
> > > >> you
> > > >> > > > tell us more about the task which creates this need? What
> > creates
> > > a
> > > >> > > > requirement to display any recordset that could be presented?
> > > >> > > >
> > > >> > > > Sounds like an interesting problem.
> > > >> > > >
> > > >> > > > r
> > > >> > > > On Mon, Oct 25, 2021 at 5:16 PM Bill Benson <
> > > bensonforums at gmail.com
> > > >> >
> > > >> > > > wrote:
> > > >> > > >
> > > >> > > > > Actually it has been so long since I programmed in Access UI
> > now
> > > >> I am
> > > >> > > > > second guessing my earlier idea - because I can’t remember
> > > whether
> > > >> > the
> > > >> > > > > datasheet subform would or would not need the fields it will
> > be
> > > >> > > > displaying
> > > >> > > > > already defined?
> > > >> > > > >
> > > >> > > > > I was trying to think of a solution that could dynamically
> > > display
> > > >> > any
> > > >> > > > > recordset.
> > > >> > > > >
> > > >> > > > >
> > > >> > > > >
> > > >>
> > > >>
> > > --
> > > 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
>


More information about the AccessD mailing list