[AccessD] For all forms

Arthur Fuller fuller.artful at gmail.com
Thu Aug 5 17:49:04 CDT 2021


I copied your code into a new .module but it won't  compile. I don't  know
what's going wrong.

On Wednesday, August 4, 2021, Arthur Fuller <fuller.artful at gmail.com> wrote:

> 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
>>
>

-- 
Arthur


More information about the AccessD mailing list