Need help with function to change BackColor

J

Jim Evans

I want to change the backcolor of each section of each form in the database.
Acc 2003. Here is what I have finally writter. It does nothing but open each
form and then close it.

The line, strDo = "Forms!" & strForm & ".Detail.BackColor = 16119290" does
nothing but set the value of the variable. I kept getting an error that
"Object Needed" with the strDo = portion. But, this line represents the
action I want to take on each form section. I know that I will need a
For...Next loop to apply the change to all sections, which, what will happen
if a section does not exist on that particular form?

Private Function fFormSectionBack()
Dim obj As AccessObject, dbs As Object
Dim Detail As Section
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
lngCount = dbs.AllForms.Count
Debug.Print lngCount

For Each obj In dbs.AllForms
strForm = obj.Name
Debug.Print strForm
DoCmd.OpenForm strForm, acDesign

strDo = "Forms!" & strForm & ".Detail.BackColor = 16119290"
DoCmd.Close acForm, strForm, acSaveYes

'Debug.Print
Next

End Function

Jim
 
J

John Spencer

strDo = ... simply builds a string. It does not perform any action.

Perhaps what you want is
Forms(strForm).Detail.BackColor = 16119290
which should assign a color to the form named strForm

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jim Evans

John,

I have tried EVERYTHING... but Parentheses to enclose the variable for the
form name... Thanks.
Jim

strDo = ... simply builds a string. It does not perform any action.

Perhaps what you want is
Forms(strForm).Detail.BackColor = 16119290
which should assign a color to the form named strForm

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jim Evans

Worked without a hitch. Thanks, again.
Jim

John,

I have tried EVERYTHING... but Parentheses to enclose the variable for the
form name... Thanks.
Jim

strDo = ... simply builds a string. It does not perform any action.

Perhaps what you want is
Forms(strForm).Detail.BackColor = 16119290
which should assign a color to the form named strForm

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jack Leach

I'm not seeing where you are using strDo other than applying a string to the
variable. Also, you cannot use the expression service in a string like that,
you need to refer to the actual collections, so instead of

="Forms!" & strForm & ".Details.BackColor = 16119290"

you can change the value by using this statement:

Forms(strForm).Detail.BackColor = 16119290

or, more commonly for this type of procedure, make an object variable to
hold the form itself:


Public Function fFormSectionBack
Dim frm As Form
For Each frm In CurrentProject.AllForms
DoCmd.OpenForm frm.Name
frm.Detail.BackColor = 16119290
DoCmd.Close acForm, frm.Name, acSaveYes
DoEvents
Next frm
End Function


You may also want to put a check in there to make sure the form is not
already opened before trying to open it.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
J

Jim Evans

Sorry, the line "I kept getting an error that
"Object Needed" with the strDo = portion" Should read: I kept getting an
error that
"Object Needed" without the strDo = portion.
Jim

I want to change the backcolor of each section of each form in the database.
Acc 2003. Here is what I have finally writter. It does nothing but open each
form and then close it.

The line, strDo = "Forms!" & strForm & ".Detail.BackColor = 16119290" does
nothing but set the value of the variable. I kept getting an error that
"Object Needed" with the strDo = portion. But, this line represents the
action I want to take on each form section. I know that I will need a
For...Next loop to apply the change to all sections, which, what will happen
if a section does not exist on that particular form?

Private Function fFormSectionBack()
Dim obj As AccessObject, dbs As Object
Dim Detail As Section
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
lngCount = dbs.AllForms.Count
Debug.Print lngCount

For Each obj In dbs.AllForms
strForm = obj.Name
Debug.Print strForm
DoCmd.OpenForm strForm, acDesign

strDo = "Forms!" & strForm & ".Detail.BackColor = 16119290"
DoCmd.Close acForm, strForm, acSaveYes

'Debug.Print
Next

End Function

Jim
 
J

Jim Evans

Jack,

You are correct and I realized that strDo was doing nothing but assigning a
value to itself. The wording in my original post was incorrect. Instead of
saying I kept getting an error that
"Object Needed" with the strDo = portion
It was meant to say:
"Object Needed" without the strDo = portion

Your solution is the way I resolved the problem. Thanks much!
Jim

"Jack Leach" <dymondjack at hot mail dot com> wrote in message
I'm not seeing where you are using strDo other than applying a string to the
variable. Also, you cannot use the expression service in a string like
that,
you need to refer to the actual collections, so instead of

="Forms!" & strForm & ".Details.BackColor = 16119290"

you can change the value by using this statement:

Forms(strForm).Detail.BackColor = 16119290

or, more commonly for this type of procedure, make an object variable to
hold the form itself:


Public Function fFormSectionBack
Dim frm As Form
For Each frm In CurrentProject.AllForms
DoCmd.OpenForm frm.Name
frm.Detail.BackColor = 16119290
DoCmd.Close acForm, frm.Name, acSaveYes
DoEvents
Next frm
End Function


You may also want to put a check in there to make sure the form is not
already opened before trying to open it.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
J

Jim Evans

Jack,

One question. What does the statement DoEvents accomplish?

Jim

"Jack Leach" <dymondjack at hot mail dot com> wrote in message
I'm not seeing where you are using strDo other than applying a string to the
variable. Also, you cannot use the expression service in a string like
that,
you need to refer to the actual collections, so instead of

="Forms!" & strForm & ".Details.BackColor = 16119290"

you can change the value by using this statement:

Forms(strForm).Detail.BackColor = 16119290

or, more commonly for this type of procedure, make an object variable to
hold the form itself:


Public Function fFormSectionBack
Dim frm As Form
For Each frm In CurrentProject.AllForms
DoCmd.OpenForm frm.Name
frm.Detail.BackColor = 16119290
DoCmd.Close acForm, frm.Name, acSaveYes
DoEvents
Next frm
End Function


You may also want to put a check in there to make sure the form is not
already opened before trying to open it.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
J

Jack Leach

It yields code execution to the processor... basically makes sure that any
intensive operations you have going on (inside or outside of the app) are
processed before continuing

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
J

Jim Evans

How can I take this one step further and test for a header and footer on
each form to know if they should have the BackColor set? I have tried
testing for Null on Forms(strForm.FormHeader) and I get an error if the form
does not have a Header.

Jim

"Jack Leach" <dymondjack at hot mail dot com> wrote in message
It yields code execution to the processor... basically makes sure that any
intensive operations you have going on (inside or outside of the app) are
processed before continuing

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
J

Jack Leach

Off the top of my head I don't know, other than trapping the error. The
Section property of the form does not seem to have a Count property which
generally enables a For x = 1 To Count loop.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
J

Jim Evans

This did it:

Private Function fCheckForSections()

Dim obj As AccessObject, dbs As Object
Dim Detail As Section
Set dbs = Application.CurrentProject

' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
strForm = obj.Name
Debug.Print strForm
DoCmd.OpenForm strForm, acDesign

For i = 0 To 2
' If Section(i) doesn't exist We will Resume Next iteration
On Error Resume Next
Forms(strForm).Section(i).BackColor = 16119285
Debug.Print Forms(strForm).Section(i).Name
Debug.Print Forms(strForm).Section(i).BackColor
Next i

DoCmd.Close acForm, strForm, acSaveYes
DoEvents

Next

End Function

Jim

"Jack Leach" <dymondjack at hot mail dot com> wrote in message
Off the top of my head I don't know, other than trapping the error. The
Section property of the form does not seem to have a Count property which
generally enables a For x = 1 To Count loop.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

reference application object of mdb opened with OpenDatabase 16
change all form properties with code 7
How to rename Modules ? 2
Coding conditional formating on focus 2
Help explain to me 3
Return objects 6
Command Not Available Error Using Automation 2
calling a public function correctly 5

Top