[AccessD] For all forms

[Arthur Fuller]

Thanks a lot. .uch apprecitd.

On Wed., Aug. 4, 2021, 1:13 p.m. jack drawbridge, <jackandpat.d at gmail.com>
wrote:

> Hi Arthur,
> Here's a working function based on comments from Gustav and Ryan.
> I am limiting the output to those forms that have a combo and/or listbox.
>
>  Procedure Name: ComboAndListboxOnForms
> ' Purpose: For forms in database where exists a combobox and/or listbox,
> '                 print formname, controlname  and rowsource
> ' Procedure Kind: Sub
> ' Procedure Access: Public
> ' Author: Jack
> ' Date: 04-Aug-21
> ' ----------------------------------------------------------------
> Sub ComboAndListboxOnForms()
> 10        On Error GoTo ComboAndListboxOnForms_Error
>           Dim obj As AccessObject, dbs As Object
>           Dim ctrl As Control
> 20        Set dbs = Application.CurrentProject
>
> 30        For Each obj In dbs.AllForms
>
> 40            DoCmd.OpenForm obj.name, acDesign, , , , acHidden
> 50            For Each ctrl In Forms(obj.name).Controls
> 60                Select Case ctrl.ControlType
>                       Case acComboBox, acListBox
> 70                        Debug.Print obj.name & "....." & ctrl.name & "
>   " & ctrl.RowSource
> 80                End Select
> 90            Next ctrl
> 100           DoCmd.Close acForm, obj.name
> 110       Next obj
>
>
> 120       On Error GoTo 0
> ComboAndListboxOnForms_Exit:
> 130       Exit Sub
>
> ComboAndListboxOnForms_Error:
> 140       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
> procedure ComboAndListboxOnForms, line " & Erl & "."
> 150       GoTo ComboAndListboxOnForms_Exit
> End Sub
>
>
> A few examples of output:
>
> frmMumbles10.....Combo4     "<";">";"=";"<>"
> frmProduct.....Combo2     (All);"green";"blue";"black"
> frmTina.....Combo0     SELECT [tblTinaDates].[PUDate] FROM tblTinaDates
> ORDER BY [PUDate];
> frmTina.....Combo2     SELECT [tblHouseholds].[Owner] FROM tblHouseholds
> ORDER BY [Owner];
>
> Hope it's helpful.
> jack
>
> On Wed, Aug 4, 2021 at 11:06 AM Arthur Fuller <fuller.artful at gmail.com>
> wrote:
>
> > How do I get the type of control? As it happens, combos and list boxes
> are
> > my main interest.
> >
> > On Wed., Aug. 4, 2021, 8:33 a.m. Gustav Brock via AccessD, <
> > accessd at databaseadvisors.com> wrote:
> >
> > > Hi Arthur
> > >
> > > That’s because only objects of type ListBox and ComboBox, not the
> generic
> > > Control,  have this property.
> > >
> > > /gustav
> > >
> > > Fra: Arthur Fuller <fuller.artful at gmail.com>
> > > Sendt: 4. august 2021 14:29
> > > Til: Access Developers discussion and problem solving <
> > > accessd at databaseadvisors.com>
> > > Cc: Gustav Brock <gustav at cactus.dk>
> > > Emne: Re: [AccessD] For all forms
> > >
> > > Hmm. The compiler and Intellisense  don't like the reference to
> > RowSource.
> > > I can't see anything  similar  in Intellisense.
> > >
> > > On Wed., Aug. 4, 2021, 7:39 a.m. Arthur Fuller, <
> fuller.artful at gmail.com
> > > <mailto:fuller.artful at gmail.com>> wrote:
> > > You Are the man! I forgot one part. I. am only interested  in combo and
> > > list boxes. How do I specify the control type?
> > >
> > > On Wed., Aug. 4, 2021, 6:15 a.m. Gustav Brock via AccessD, <
> > > accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.com>>
> > wrote:
> > > Hi Arthur
> > >
> > > It could be something like this:
> > >
> > > <code>
> > > Public Function ListControlSources()
> > >
> > >     Dim Form    As Form
> > >     Dim Control As Control
> > >
> > >     On Error Resume Next
> > >
> > >     For Each Form In Forms
> > >         For Each Control In Form.Controls
> > >             Debug.Print Form.Name, Control.Name, Control.RowSource
> > >         Next
> > >     Next
> > >
> > > End Function
> > > </code>
> > >
> > > /gustav
> > >
> > > -----Oprindelig meddelelse-----
> > > Fra: AccessD <accessd-bounces+gustav=cactus.dk at databaseadvisors.com
> > > <mailto:cactus.dk at databaseadvisors.com>> På vegne af Arthur Fuller
> > > Sendt: 4. august 2021 11:50
> > > Til: Access Developers discussion and problem solving <
> > > accessd at databaseadvisors.com<mailto:accessd at databaseadvisors.com>>
> > > Emne: [AccessD] For all forms
> > >
> > > I used to have code to do this but can't find it. Here is the
> algorithm.
> > >
> > > For all forms
> > >     For all controls in f
> > >         ? Ctl.RowSource
> > >     Next
> > > Next
> > >
> > > Do you have such a chunk? Or at least remind me of the syntax?
> > > Thanks
> > > --
> > > 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
>