I was still getting invalid references for the objects in use. So, I've
reworked the code and have made better progress, but am still stymied (along
with now being completely baffled) with two difficulties.
1) Controls don't seem to be actually changed to the values read it. The
code loops through the controls but nothing actually seems to be changed. I
use the acSavePrompt when closing the forms to try & catch a form being
edited & closed, but nothing ever displays.
2) Not all of the forms that are opened close. Only about half of them do,
actually, & I can't for the life of me figure out *why* some forms close &
others don't.
I've moved one of the function calls into the actual code body to help
t-shoot the issues. The other function calls should be pretty
self-explanatory (just DLookups both of them.)
Thanks again to any & all who can help!
Public Sub SetAllControlsToColorScheme()
' This loop will iterate through all the controls on all the forms, check to
see if they have a value in their tag field & then set that _
control's colors according to the schema in the lookup table
' Application.Echo False ' turn off the echo to (vainly) attempt to
increase the speed of the code
Dim obj_CurrentObject As AccessObject
Dim frm_FormToChange As Form
Dim ctr_ControlToChange As Control
Dim bol_ChangeControl As Boolean
' loop through the application, checking each form against the
DatabaseForms table to see if it needs to be changed
' if it does need to be changed (key value <> N/A), then open the form &
move to the next form
' the next loop will actually go through the now open forms & iterate
through their controls
For Each obj_CurrentObject In Application.CurrentProject.AllForms
' look up in the table to see what the primary key is & if it's
listed as N/A then skip over the form entirely
OpenFormKey = DLookup("FormKey_Rqrd", "DatabaseForms",
"[FormName_Rqrd] = '" & obj_CurrentObject.Name & "'")
If OpenFormKey <> "N/A" Then
' open the form
DoCmd.OpenForm obj_CurrentObject.Name, acDesign
End If
Next obj_CurrentObject
Set obj_CurrentObject = Nothing
' now that we have all the forms that are to be modified open, we can
iterate through them for their controls
For Each frm_FormToChange In Application.Forms
' iterate through each control on the form
For Each ctr_ControlToChange In frm_FormToChange.Controls
' check to see if there's a value in the tag; if not, skip it
(still need to validate the tag against values in the table)
If Not IsNull(ctr_ControlToChange.Tag) = True Then
' bol_ChangeControl =
SetControlToColorScheme(ctr_ControlToChange)
' Text color
If CanSetAttribute(ctr_ControlToChange.Tag, "Text") = True
Then
ctr_ControlToChange.ForeColor =
ColorLookup(ctr_ControlToChange.Tag, "Text")
ctr_ControlToChange.FontBold = DLookup("TextBold",
"Lookup_ElementStyles", "[Element] = '" & ctr_ControlToChange.Tag & "'")
End If
' Background color
If CanSetAttribute(ctr_ControlToChange.Tag, "Back") = True
Then
If ctr_ControlToChange.ControlType = acCommandButton Then
' command buttons do not have a background that can
be controlled programmatically; _
however, they *can* be set to be transparent, which
is what we'll do here.
ctr_ControlToChange.Transparent = True
Else
ctr_ControlToChange.BackColor =
ColorLookup(ctr_ControlToChange.Tag, "Back")
ctr_ControlToChange.BackStyle = 1 +
DLookup("BackTransparent", "Lookup_ElementStyles", "[Element] = '" &
ctr_ControlToChange.Tag & "'")
' because the property takes 1 = Normal & 0 =
Transparent, yet the database has TRUE (-1) = Transparent & FALSE (0) = Not
Transparent
End If
End If
' Line (border) color
If CanSetAttribute(ctr_ControlToChange.Tag, "Line") = True
Then
ctr_ControlToChange.BorderColor =
ColorLookup(ctr_ControlToChange.Tag, "Line")
End If
' Label color
If CanSetAttribute(ctr_ControlToChange.Tag, "Label") = True
Then
If DLookup("SetLabelColor", "Lookup_ElementStyles",
"[Element] = '" & ctr_ControlToChange.Tag & "'") = True Then
If ctr_ControlToChange.Controls.Count > 0 Then
' if there is a label, then need to also set its
text color
Dim tmp_Control As Label
Set tmp_Control =
ctr_ControlToChange.Controls.Item(0) ' this is a gimme & might break if
there's more than one item in the group
tmp_Control.ForeColor =
ColorLookup(ctr_ControlToChange.Tag, "Label")
tmp_Control.FontBold = DLookup("LabelBold",
"Lookup_ElementStyles", "[Element] = '" & ctr_ControlToChange.Tag & "'")
tmp_Control.BackStyle = 1 +
(DLookup("LabelTransparent", "Lookup_ElementStyles", "[Element] = '" &
ctr_ControlToChange.Tag & "'"))
' because the property takes 1 = Normal & 0 =
Transparent, yet the database has TRUE (-1) = Transparent & FALSE (0) = Not
Transparent
Set tmp_Control = Nothing ' clear out the
reference
End If
End If
End If
' Conditional Formating for the current row
If DLookup("CondForm", "Lookup_ElementStyles", "[Element] =
'" & ctr_ControlToChange.Tag & "CR'") = True Then ' can set conditional
formating
' Sample Conditional Formatting condition:
Forms!EditDispositions.txt_RowID.Value=[Disposition]
With ctr_ControlToChange.FormatConditions _
.Add(acExpression, acEqual, "Forms!" &
ctr_ControlToChange.Parent.Name & "." & "txt_RowID.Value = [" & OpenFormKey &
"]")
.FontBold = DLookup("TextBold",
"Lookup_ElementStyles", "[Element] = '" & ctr_ControlToChange.Tag & "CR'")
.BackColor = ColorLookup(ctr_ControlToChange.Tag &
"CR", "Back")
.ForeColor = ColorLookup(ctr_ControlToChange.Tag &
"CR", "Text")
End With
End If
End If
Next ctr_ControlToChange
DoCmd.Close acForm, frm_FormToChange.Name, acSavePrompt
Next frm_FormToChange
' Application.Echo True ' turn the echo/refresh back on
End Sub
Arvin Meyer MVP said:
Try adding:
Dim doc As DAO.Document
then after:
Set cnt_Container = dbs_Database.Containers("Forms")
For Each doc In cnt_Container.Documents
'Do your form stuff here
Next doc
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Phoig said:
Ack! Okay, so I'm having some problems expanding the sample code to cover
*all* forms in one fell swoop. I thought I could cobble the code together,
seeing as how it does read all the names of the forms in one routine and
another routine iterates all the controls. However, I'm getting "Run-time
error '3251': peration is not supported for this type of object." My help
files crash everytime I try to reference keywords like Containers and
Documents, which makes this whole endeavor much more difficult.
Additionally,
searching the MS sites haven't yields an online equivalent of the help
files
(although I could have sworn there was such a beastie somewhere.) The line
that errors is marked just above with hash marks.
I'm obviously mixing up object types, but without having accurate help
information, I can't figure out very easily what I *should* be using
instead.
So, if you can see something glaringly obvious in the code, I'd appreciate
the feedback. Similarily, if there's some better way to do this, then I'm
game as well. Thank you!
Public Sub SetAllControlsToColorScheme()
' This loop will iterate through all the controls on all the forms, check
to
see if they have a value in their tag field & then set that _
control's colors according to the schema in the lookup table
' Application.Echo False ' turn off the echo to (vainly) attempt to
increase the speed of the code
Dim dbs_Database As Database
Dim cnt_Container As Container
Dim int_CountForms As Integer, int_CountControls As Integer
Dim FormToChange As Form
Dim ControlOnForm As Control
Dim ChangeControl As Boolean
Set dbs_Database = CurrentDb()
Set cnt_Container = dbs_Database.Containers("Forms")
For int_CountForms = 0 To cnt_Container.Documents.Count - 1 ' loop
through the forms
'### This is where the error occurs ###
For Each FormToChange In cnt_Container.Documents(int_CountForms)
DoCmd.OpenForm cnt_Container.Documents(int_CountForms).Name,
acDesign ' open the form in design mode
' use the DatabaseForms table to lookup what the primary key
should be for each form, for use in the conditional formatting
OpenFormKey = DLookup("FormKey_Rqrd", "DatabaseForms",
"[FormName_Rqrd] = '" & cnt_Container.Documents(int_CountForms).Name &
"'")
If OpenFormKey <> "N/A" Then
DoCmd.OpenForm
cnt_Container.Documents(int_CountForms).Name,
acDesign
For Each ControlOnForm In FormToChange.Controls ' loop
through all the available controls on the form
If Not IsNull(ControlOnForm.Tag) Then
ChangeControl =
SetControlToColorScheme(ControlOnForm)
End If
Next ControlOnForm
End If
DoCmd.Close acForm,
cnt_Container.Documents(int_CountForms).Name, acSaveYes ' save the form
Next FormToChange
Next int_CountForms
' Application.Echo True ' turn the echo/refresh back on
End Sub
Arvin Meyer MVP said:
A sample of how to open a form in Design View and affect a change then
save
is at:
http://www.datastrat.com/Download/FixNames2K.zip
You can also have each user store their preferences in a local table and
use
code to access the table data and affect the change when they open the
form.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Hello! First off, I know how to use VBA to set the values for forms &
controls at run time. What I don't know how to do is to write code that
will
enact changes to values for forms & controls that are *permanent*
(i.e.,
in
design mode.) It may very well be something so obvious that I'm blind
to
it
(professional hazard of mine, very good at seeing trees, not so good as
seeing forests.)
The Scenario:
I have a stable of small Access databases for a number of users. My
default
color "theme" doesn't always appeal to all of my users (needy lot, but
I
love
them.) I have written very extensive code that will read in tagged
controls,
look up the value of the tag in a global table & return what values can
be
updated and if so, what values to use. I further have defined several
"themes" that users can pick from. All of which works great (if quite
slowly)
and is very useful for "trying out" a theme.
What I want to be able to offer the users is the ability to permanently
set
the theme for the forms in a database. That way, once they've found a
theme
they like, they can select the "Make Permanent" option/button/what-ever
&
have the code rip through the objects and set the design values to
their
chosen color. Then they'd not have to wait for the code to run every
time
they open a form.
Does that make sense?
I thought I could try & force the form to open in design view but am
absolutely stymied on that approach. My coding environment is VBA 6.5
with
Access 2000 and due to the structure of the network here, I'm not able
to
reliably access all of the help file contents (links in the help pages
in
particular are often unable to be utilized. Annoying, but so low on the
priority list for the tech-staff that I doubt it'll ever get resolved.)
I'm not sure about the utility or helpfulness in posting sample forms
and
code, but I could certainly do so if it will help out. I really
appreciate
any help with this! Thank you in advance!
Phoig
