See comments interspersed with original text.
Dan said:
Thanks for the response. I've experimented with the
DoCmd's you mentioned and they will very much be a part of
this process. What I'm ultimately looking for is the
ability to change properties of the objects as in: 1)
within the query 'templates' we now have a hardcoded
selection criteria 'SF0000'. When we create a new
application, one of the steps is we copy the query
SF0000_00_50_SQ(template) as SF1000_00_50_SQ(new
application query) where the '1000' is the number of the
new application(the next would be 1001, etc). Within this
query is an application specific selection criteria
initially entered as 'SF0000' within the template which
needs to be changed to 'SF1000'. As indicated earlier, we
perform this step manually. Would like to automate
changing this 'criteria property'(if it exists and can be
manipulated) with VBA.
There's no "criteria" property, but if I understand you correctly you
can probably do what you want with DAO (assuming you're working with an
..mdb file) by manipulating the .SQL property of the QueryDef object that
represents the new stored query. For example, assuming that dbNewApp is
a Database object variable currently set to the new application
database, and the query named "SF1000_00_50_SQ" has already been created
by copying its template, then *probably* you could use a code snippet
similar to this:
' ... assuming dbNewApp has already been set
Dim qdf As DAO.QueryDef
Set qdf = dbNewApp.QueryDefs("SF1000_00_50_SQ")
qdf.SQL = Replace(qdf.SQL, "SF0000", "SF1000")
Set qdf = Nothing
That could give you problems if the string "SF1000" occurs as part of
something else in the SQL, but you get the idea. If necessary, you
could parse the SQL statement and operate only on carefully chosen
pieces, but that would be more complicated.
2) We also have documentation
within the Description for the query Properties that
requires changing to reflect control number/general
description for the new application. Again, we would like
to automate changing these values as well. There are
numerous other properties we would like to change within
other objects(forms, reports, & macros) in a similar
fashion.
If the Description property already exists for the new query (because it
was copied from a template that had that property), then you can modify
it in a similar manner, as part of the same code:
' ... assuming dbNewApp has already been set
Dim qdf As DAO.QueryDef
Dim strDesc As String
Set qdf = dbNewApp.QueryDefs("SF1000_00_50_SQ")
With qdf
' Fix the SQL of the query.
.SQL = Replace(.SQL, "SF0000", "SF1000")
' Fix the Description property. If the property doesn't exist,
' we'll create it.
On Error GoTo Property_Error
strDesc = .Properties("Description")
strDesc = Replace("<CTLNO>", strControlNumber)
strDesc = Replace("<GENDESC>", strGeneralDescription)
.Properties("Description") = strDesc
PropertyResume:
On Error GoTo Your_Original_Error_Handler
End With
Set qdf = Nothing
Exit_Point:
Exit Sub
Property_Error:
If Err.Number = 3270 Then
strDesc = strControlNumber & " : " & strGeneralDescription '
or whatever
qdf.Properties.Append
qdf.CreateProperty("Description",dbText,"My property")
Resume PropertyResume
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If
Hope this gives you a better idea of what our intentions
are. This process takes approximately 20-30 minutes for
each new application we create. Would be a great time
saver if we could automate these changes we now perform
manually.
I hope that gives you some ideas.
