[AccessD] For all forms

[jack drawbridge]

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
>