Script forms to disk

  • Thread starter Jonathan Wallace
  • Start date
J

Jonathan Wallace

Hello,
I have an adp project that I've inherited with numerous forms. I'm
responsible for extending and maintaining this project.

Many of these forms have record sources that point to stored procedures
on an MS SQL server. Documentation for this project is very sparse.
I'd like to be able to programmatically generate a list of all the
record sources for all forms so that when it is necessary to make a
change to a stored procedure, I can determine which forms are dependent
upon it.

Is this even possible? If so, can anyone point me in the proper
direction? I assume that there is probably a object model for ms
access?

Thank you in advance for your time and consideration,
Jonathan
 
B

Brendan Reynolds

Here's an example that will print the name of each form and the value of its
RecordSource property to a text file located in the same folder ...

Public Sub PrintFormRecordSource()

Dim intFile As Integer
Dim aob As AccessObject

intFile = FreeFile
Open CurrentProject.Path & "\FormRecordSource.txt" For Output As intFile
For Each aob In CurrentProject.AllForms
DoCmd.OpenForm aob.Name, acDesign, , , , acHidden
Print #intFile, aob.Name, Forms(aob.Name).RecordSource
DoCmd.Close acForm, aob.Name
Next aob
Close intFile

End Sub

You could also try using the built-in Documenter (from the Tools menu,
select Analyze, then Documenter). Using the documenter is easier, but it is
a little hard on the trees! :-/
 
K

Klatuu

Here is a function that will print a list of all forms with recordsources to
the immediate window. You can modify it to put it out whereever you want:

Public Function GetRecordSources()
Dim frms As AllForms
Dim frm As Object
Dim strFormName As String
Dim varSource As Variant

Set frms = CurrentProject.AllForms
For Each frm In frms
strFormName = frm.Name
DoCmd.OpenForm strFormName, acDesign, , , acFormPropertySettings,
acHidden
varSource = Forms(strFormName).RecordSource
If Len(Trim(Nz(varSource, ""))) > 0 Then
Debug.Print strFormName & " - " & varSource
End If
DoCmd.Close acForm, strFormName, acSaveNo
Next frm
Set frms = Nothing
End Function
 
B

Brendan Reynolds

I hope you'll forgive me for being a bit picky here, but the RecordSource
property returns a String, not a Variant, so you don't really need to
declare varSource as a Variant and then use NZ to convert it to an empty
string. If the form is unbound, the RecordSource property will return an
empty string.
 
K

Klatuu

You are correct. The reason it is that way is that when I first put it
together, I was not sure whether I was going to get a Null or zero length
string, so I started with a Variant. I should have cleaned up the code
before I posted it.
 
K

Klatuu

Your code is cleaner than mine; however, yours will include forms with no
recordsource. I felt it was a good idea to exclude those.
 
B

Brendan Reynolds

It depends! :)

We might or might not want to include those forms. So we could enhance the
solution by adding an optional argument ...

Public Sub PrintFormRecordSource(Optional IncludeUnbound As Boolean)

Dim intFile As Integer
Dim aob As AccessObject

intFile = FreeFile
Open CurrentProject.Path & "\FormRecordSource.txt" For Output As intFile
For Each aob In CurrentProject.AllForms
DoCmd.OpenForm aob.Name, acDesign, , , , acHidden
If (Forms(aob.Name).RecordSource <> vbNullString) Or IncludeUnbound
Then
Print #intFile, aob.Name, Forms(aob.Name).RecordSource
End If
DoCmd.Close acForm, aob.Name
Next aob
Close intFile

End Sub
 
R

RoyVidar

Brendan Reynolds said:
It depends! :)

We might or might not want to include those forms. So we could
enhance the solution by adding an optional argument ...

Public Sub PrintFormRecordSource(Optional IncludeUnbound As Boolean)

Dim intFile As Integer
Dim aob As AccessObject

intFile = FreeFile
Open CurrentProject.Path & "\FormRecordSource.txt" For Output As
intFile
For Each aob In CurrentProject.AllForms
DoCmd.OpenForm aob.Name, acDesign, , , , acHidden
If (Forms(aob.Name).RecordSource <> vbNullString) Or
IncludeUnbound Then
Print #intFile, aob.Name, Forms(aob.Name).RecordSource
End If
DoCmd.Close acForm, aob.Name
Next aob
Close intFile

End Sub
Click to expand...

He, he - and the next step, would probably involve looping the
controls collection to check out the .RowSource of combos and lists,
then - what if there are rowsource or recordsource assignements either
in the form class module, or elsewhere in code which uses SP's... ;-)
 
B

Brendan Reynolds

Yes, even the commercial apps like Total Access Analyzer eventually run up
against that sort of limitation. For example, suppose the app includes code
such as this ...

strProcName = "GetCustomers"
If SomeCondition Then
strProcName = strProcName & "ByCountry"
Else
strProcName = strProcName & "ByPostCode"
End If

Programmatically detecting the fact that this code is dependent on the two
stored procedures 'GetCustomersByCountry' and 'GetCustomersByPostCode' is,
shall we say, a non-trivial task! :)
 
Top