Programatically Determining What Data is used Where

[Guest]

I must say, programming in MS Access has been a learning experience. The
differences between MS Access VBA and standard VB6 are quite substantial.
Form creation/programming is a completely different animal.

I need to determine where every piece of data from a couple linked tables is
being used in this application.

I’ve already found all the queries that read from or write to these tables
(see ‘How Do I List All Tables used in All Queries?’ from 8/22/05).

Several forms are using one or the other table as a Data Source. With 44
forms to deal with, I figure there has to be an easier way than one textbox
at a time! Plus the random field in other places that obtain their data from
either VBA code or via the queries. What a mess.

Specifically what I need is this:

What forms/reports/other objects (textboxes and the like) use either table
as a data source and or a recipient of data changes?

And …

Within these objects, what field is being used in which textbox/combo box/etc?


Of course, after all this, I will need to document the business logic behind
the use of the data too, but that’s another day’s problem.

Oh, I’ve also learned that documentation has to be written in MS Access as
you go, otherwise you’re hopelessly lost. (like me! )

Thank you. You guys have been great!

Robert
======

 

[David C. Holley]

The following code snoops the FORMS & REPORTS adding records to
_tblControlSnooper listing the FORM, CONTROL NAME and CONTROL SOURCE (if
applicable) (all text fields in the table).

Sub snoopCtl()

Dim sourceRS As DAO.Recordset
Dim targetRS As DAO.Recordset
Dim ctl As Object

Set sourceRS = CurrentDb.OpenRecordset("SELECT * FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768));", dbOpenForwardOnly)
Set targetRS = CurrentDb.OpenRecordset("_tblControlSnooper")

While Not sourceRS.EOF
Debug.Print sourceRS.Fields("Name")
DoCmd.OpenForm sourceRS.Fields("Name"), acDesign, , , ,
acHidden
For Each ctl In Forms(0).Controls
Debug.Print " " & ctl.Name
targetRS.AddNew
targetRS("txtFormName") = Forms(0).Name
targetRS("txtControlName") = ctl.Name
targetRS("txtControlType") = ctl.ControlType
On Error Resume Next
targetRS("txtControlSource") = ctl.ControlSource
On Error GoTo 0
targetRS.Update
Next
DoCmd.Close acForm, sourceRS.Fields("Name"), acSaveNo
sourceRS.MoveNext
Wend

sourceRS.Close
targetRS.Close
Set targetRS = Nothing
Set sourceRS = Nothing

End Sub

 

[David C. Holley]

You can also use the code from that prior post to capture the FORM or
REPORT recordSoruce which might be a formal SQL statement as opposed to
a saved query. And of course you could check to see if the control is a
comboBox or listBox and do the same.

 

[Guest]

That is Incredible, David. Thank you!

The only thing I’m lacking is the data source (table name/query or whatever)
for each of the control’s txtControlSource entries. These appear to be
Fields, but without the data source, I’m only part way there.

Any suggestions for this?

The code works very well! I did add some code to get the actual Control Type
as text, rather than the number.

For anyone following along

Create a new table in your database: _tblControlSnooper

Add these fields to it

Field name Data Type
======= ========
txtFormName Text
txtControlName Text
txtControlType Text
txtControlSource Text

Copy & paste the following code into a VBA Module:

Sub snoopCtl()

Dim sourceRS As DAO.Recordset
Dim targetRS As DAO.Recordset
Dim ctl As Object

Set sourceRS = CurrentDb.OpenRecordset("SELECT * FROM MSysObjects WHERE
(((MSysObjects.Type)=-32768));", dbOpenForwardOnly)
Set targetRS = CurrentDb.OpenRecordset("_tblControlSnooper")

While Not sourceRS.EOF
Debug.Print sourceRS.Fields("Name")
DoCmd.OpenForm sourceRS.Fields("Name"), acDesign, , , , acHidden
For Each ctl In Forms(0).Controls
Debug.Print " " & ctl.name
targetRS.AddNew
targetRS("txtFormName") = Forms(0).name
targetRS("txtControlName") = ctl.name


'Use ControlType to determine the Type of Control
Select Case ctl.ControlType
Case acLabel: targetRS("txtControlType") = "Label"
Case acRectangle: targetRS("txtControlType") = "Rectangle"
Case acLine: targetRS("txtControlType") = "Line"
Case acImage: targetRS("txtControlType") = "Image"
Case acCommandButton: targetRS("txtControlType") = "Command
Button"
Case acOptionButton: targetRS("txtControlType") = "Option
button"
Case acCheckBox: targetRS("txtControlType") = "Check box"
Case acOptionGroup: targetRS("txtControlType") = "Option
group"
Case acBoundObjectFrame: targetRS("txtControlType") = "Bound
object frame"
Case acTextBox: targetRS("txtControlType") = "Text Box"
Case acListBox: targetRS("txtControlType") = "List box"
Case acComboBox: targetRS("txtControlType") = "Combo box"
Case acSubform: targetRS("txtControlType") = "SubForm"
Case acObjectFrame: targetRS("txtControlType") = "Unbound
object frame or chart"
Case acPageBreak: targetRS("txtControlType") = "Page break"
Case acPage: targetRS("txtControlType") = "Page"
Case acCustomControl: targetRS("txtControlType") = "ActiveX
(custom) control"
Case acToggleButton: targetRS("txtControlType") = "Toggle
Button"
Case acTabCtl: targetRS("txtControlType") = "Tab Control"
End Select

On Error Resume Next
targetRS("txtControlSource") = ctl.ControlSource
On Error GoTo 0
targetRS.Update
Next
DoCmd.close acForm, sourceRS.Fields("Name"), acSaveNo
sourceRS.MoveNext
Wend

sourceRS.close
targetRS.close
Set targetRS = Nothing
Set sourceRS = Nothing

End Sub

 

[Guest]

I've worked out that Form(0).RecordSource.Name will give me the SQL text for
that form. Here's where my inexperience with MS Access comes into play: Can
each individual control have its own - separate- Record Source? I imagine a
form that gets its data from somewhere one source, but a control on that form
that gets its data from another. If this can happen, it probably does (this
is an _interesting_ database).

Suggestions?

 

[David C. Holley]

You would have to grab the FORM/REPORTS recordSource and then match the
values in the .ControlSource back to them to figure out which tables the
fields are coming from. Unfortunately, I don't know of any way around that.

 

[David C. Holley]

It depends. The .ControlSource property HAS to tie back to a field
returned by the FORM's or REPORT's RecordSource. If the field (eg.
txtLastName) is NOT selected by the RecordSource's SQL statement, then
#Name? will appear in the control when the form/report is run. This
applies to wether or not the F/R RecordSource is a formal SQL statement
or a saved query. The terms BOUND and UNBOUND refer to wether or not a
control is BOUND/UNBOUND to an underlying field. If you have a listbox
or controlbox, there can be a separate recordSource for those which
again can be a formal SQL statement or a save query. The RS for those
controls is how the controls are populated if they're being populated by
a table/query as opposed to a value list.

 

[Guest]

Ok,

So what I need to do then is determine if each control is Bound or Unbound.

The Bound ones will have the same source as the form. I can include that
with each control’s record, including only the table/query name by adding a
field to our table:

Field Data Type
====== ======
txtFormRecordSource Text

and including the following function in the module:

--------------------------------------------------------------

Function GetFormRecordSourceTable(RecordSourceString) As String

If InStr(UCase(RecordSourceString), "FROM") > 0 Then

Dim FromLoc As Long
Dim WhereLoc As Long
Dim SemiColonLoc As Long
Dim OrderByLoc As Long
Dim SourceName As String
Dim SourceStart As Long
Dim SourceEnd As Long

FromLoc = InStr(UCase(RecordSourceString), "FROM")
WhereLoc = InStr(UCase(RecordSourceString), "WHERE")
SemiColonLoc = InStr(UCase(RecordSourceString), ";")
OrderByLoc = InStr(UCase(RecordSourceString), "ORDER BY")

' pick apart SQL statement
SourceStart = FromLoc + 5

If WhereLoc > 0 Then
SourceEnd = WhereLoc - 1
ElseIf OrderByLoc > 0 Then
SourceEnd = OrderByLoc - 1
ElseIf SemiColonLoc > 0 Then
SourceEnd = SemiColonLoc - 1
Else
SourceEnd = Len(RecordSourceString)
End If

GetFormRecordSourceTable = Trim(Mid(RecordSourceString, SourceStart,
SourceEnd - SourceStart + 1))
Else

GetFormRecordSourceTable = RecordSourceString

End If

End Function
--------------------------------------------------------------

This is called from code inserted after: On Error GoTo 0

If Len(targetRS("txtControlSource")) > 0 Then
' if RecordSource is a SQL statement then only include
FROM clause
targetRS("txtFormRecordSource") =
GetFormRecordSourceTable(Forms(0).RecordSource)
End If

That was easy. The Unbound ones are the ones I’ll have to concern myself
with. I know there are several list boxes that pull data after a command
button click event. One of these definitely accesses a table I’m concerned
with. I’ve no doubt there are others.

Can I find out if a control is Bound or not?

 

[Guest]

The Unbound ones are the ones I’ll have to concern myself
with. I know there are several list boxes that pull data after a command
button click event. One of these definitely accesses a table I’m concerned
with. I’ve no doubt there are others.

Can I find out if a control is Bound or not?

 

[Douglas J. Steele]

Bound controls have their ControlSource property set.

 

[Guest]

Gee, THAT should've been a no-brainer, huh?

Now, (even) more inexperience shines though...

Many list boxes in this DB are populated based on data within the forms. A
good example is a search form that gets data from tables or views based on
criteria filled in by the user. Assuming the original programmer didn't use
VBA calls via SQL to open & extract data (which would be easy to locate, and
is how a 'classic' VB program would work), how is it done within the
internals of Access?

I guess I'm attempting to envision all the potential ways that data can be
extracted or written to tables within an Access database program, as I need
document them and they aren't at all obvious.

Robert
=====

 

[David C. Holley]

Not certain if you've gotten a response, but... A listBox or comboBox
can obtain their values from a SQL statement - either a fully qualified
statement or a saved query. Check the .RowSourceType property to see if
its TABLE/QUERY, if it is the .RowSource property will contain the SQL
statement or the name of the query providing the records.

 

[Guest]

Wow,

This is getting interesting!

The RowSource values I get for controls with a RowSourceType are:

Field List
FillResultList
fListFill (<- huh?)
Table/Query
Value List

Most return a SQL statement ,query name or string list, but many do not.
Should I be looking in the VBA code for these? If so, why would the control
be allowed to say it has a recordSourceType when it doesn’t (per se)?

If not, where is the control getting its data? If you say ‘from a macro’,
I’ve got a whole bunch more learning to do.

Thanks for your help, BTW!

 

[David C. Holley]

The only ones to ignore are the 'VALUE LISTS'. All others require some
snooping. The Table/Query and Field List are pretty obvious.
FillResultList and fListFill are most likely functions designed to load
the controls programically. The Typical values for RowSourceType are
FieldList; Table/Query; Value List.