Finding all occurances of a certain type GUI control - grep for Access?

[mdlueck]

I would like to find all Forms that have a Combo Box control on them. Yesterday I came across one that had a SQL query entered directly into the control instead of referring to a DAO.QueryDef object. So I would like to conduct an audit of this application and inspect each Combo Box control. Any suggestions how I could compile a list of Form names which happen to have a Combo Box control on them?

 

[nanscombe]

In Access 2010 there is the Database Documenter under the Database Tools tab. This gives you a report of all sorts of info about the database. They may be something similar for 2007.

It might be worth a look.

 

[spikepl]

Download and install Vtools
Run a Total Deep Search, with only Form objects selected, searching for the word SELECT
Then you get your list

 

[nanscombe]

A very crude alternative ...

Public Sub findCombos()
Dim frm As Object, ctl As Control, obj As Object
For Each frm In CurrentProject.AllForms
    
    DoCmd.OpenForm frm.Name, acDesign
    
    For Each ctl In Forms(frm.Name).Controls
        If ctl.ControlType = 111 Then Debug.Print frm.Name & "\" & ctl.Name, ctl.ControlType
    Next ctl
    
    DoCmd.Close acForm, frm.Name
Next frm
End Sub

Opens each form in turn, looks for ComboBoxes, debug.prints any that it finds then closes the form.

 

[vbaInet]

Little info here:

ctl.ControlType = acComboBox

just for brevity.

 

[nanscombe]

Little info here:

ctl.ControlType = acComboBox

just for brevity.

Ah, that's better. Just rushed that code out between doing something else.

 

[vbaInet]

How did you remember that 111 is a combo box?

 

[mdlueck]

In Access 2010 there is the Database Documenter under the Database Tools tab. This gives you a report of all sorts of info about the database. They may be something similar for 2007.

Indeed there is a documenter. Cranks out a 998 page report for just the Forms in the database as-is! I exported it to a txt file and am making use out of that.

One query is fighting me switching it to a dynamically created DAO.QueryDef object. The SQL that works has multiple lines to it. So I will research how to embed CrLf's into a string arg being passed to a class method.

Otherwise, making good progress, so thanks!

P.S. Note: And about that one query, I was able to concatenate the multiple SQL lines with a & vbCrLf & between each individual line... and it is considered as one string being passed to the class method. Not so bad after all. fffeeewww....

And all Combo Boxes in this app properly configured to use DAO.QueryDef objects once again. Mission accomplished. Thanks!

 

[nanscombe]

How did you remember that 111 is a combo box?

Easy ....





The ctl.name that I printed on the same line had cbo, my prefix for ComboBox, at the front of it.

Aren't naming conventions useful.

 

[vbaInet]

I thought you knew it off-by-heart. I was just about to label you Genius. Now you're not going to get it anymore

 

[nanscombe]

Darn .... should know better than to give away trade secrets.


A bit like Sherlock Holmes and Dr Watson after Sherlock performed a bit of miraculous deductive reasoning.

After you tell people how its done, it seems so mundane.

 

[vbaInet]

I'm still yet to see Sherlock Holmes 2.

Oh, sorry Michael for hijacking your thread!

 

[nanscombe]

"Still off Topic, but just finishing the thought...(Also sorry Michael) "

Years ago I did about nine years on an in-house IT helpdesk. A lot of the time it was not so much knowing how to do something but more a matter of knowing where to look it up before you went to see the client.

I was called by one person "The Guru of the Magic Box" because I always new how to help. Ah ... happy days.

The trouble is that nine years of doing nothing but solving other peoples' problems does tend to sour one's outlook on life. So I switched to development instead.

 

[vbaInet]

So I switched to development instead.

... and I'm sure you've never looked back since then.

 

[nanscombe]

Nope ... not until I was made redundant with skills 9 years out of date (as I thought).

I ... must .. stop .. hijacking .. this .. thread ... sorry.

 

[vbaInet]

I ... must .. stop .. hijacking .. this .. thread ... sorry.

Michael is enjoying this for sure

 

[nanscombe]

If he isn't still wading through his

998 page report for just the Forms in the database as-is!

 

[vbaInet]

We'll catch up with him next week. He should know EVERYTHING about the database by then.

 

[mdlueck]

If he isn't still wading through his

Research project is long over. Ctrl-F looking for "combo" was able to find the handful of combo boxes quickly.

You two may carry on with your chit-chat.

 

[JANR]

You could use the QueryDef object to list alle queries, combo/listboxes, forms/reports that use a SQL-sting in its recordsource.

Function AllQueries()
Dim qdf As DAO.QueryDef

For Each qdf In CurrentDb.QueryDefs
    With qdf
        Debug.Print .Name
        'Debug.Print .SQL
    End With
Next
End Function

and you get somthing like this:

~sq_cfrmMain~sq_ccboCustomer <-ComboBox
~sq_cfrmMain~sq_ccboProduct <-Combobox
~sq_cfrmOrders~sq_csfrmOrders <-RecordSource subform
~sq_cMyForm~sq_clstCustomer <-ListBox
~sq_cMyForm~sq_clstProduct <-ListBox
~sq_csfrmMyForm~sq_clstSubForm <-ListBox on a subform
~sq_ffrmOrders <-Recordsource of mainform
~sq_fsfrmOrders <-Recordsource of subform
~sq_rrptCustomers <-Recordsource of a report
qryMyCustomers <- Normal query

and since I have prefixed my comboboxes with cbo and listbox with lst it's easy to identify which is which

JR