Moving a control to front or back in an Access 2003 report using VBA

I have some rectangles in an Access 2003 report that expand or contract based upon values in certain fields.  The rectangles are essentially "stacked" upon one another so the shorter ones are not covered up by those which are longer.  This process works well but occassionally the smaller ones have values that exceed the next size and thus cover up those that are usually longer.  The rectangles display in color which requires the back style property to be normal.  Thus, setting the back style property to transparent is not an option.

I think the solution is to add code to send the short rectangle control to the front or back depending on its length relative to other fields.  What I don't know is the syntax needed to accomplish this.  I'm guessing it would be similar to the following.

If Me. Box1.value > Me.Box2.value then
Me. Box1.SEND TO BACK
Else
Me.Box1.SEND TO FRONT
End if

I would appreciate any suggestions on how to do this, or if another approach would be better.
Liberty4allAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
afaik, that setting is only available in design view...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and you can flip to design view and do it, but that is problematic on a number of levels, the first of which is it de-compiles the app and makes it run slower.

Second problem is you cannot run as a MDE/ACCDE then.

Third one is that design view changes require exclusive access to the DB.  Not an issue if everyone has a copy of a front end, which is what will have to happen if more then one user uses it (you should be doing this anyway).

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I uses the following code to dynamically resize "Boxes" to fit t

see: http://lebans.com/PrintLines.htm

PrintLinesClass - Create Vertical lines, Borders and boxes with the addition of a simple to use Class to your project.


    How to create a margin for your TextBox.
    How to Draw Vertical lines the entire vertical length of your Report.
    How to Draw a Grid.
    How to force the contents of a Memo field to fit within a fixed sized control.
    Lots of other stuff!


0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, but the problem is not changing the size, but the fact that changing the size ruines the layout they've setup, which is a "stacked" series of rectangles.

They need to change the z layout order.

I wonder though if all the rectangles could be drawn in order in the OnPage event?

A thought...

Jim.
0
Liberty4allAuthor Commented:
For now I will accept the response of capricorn1 and Jim Dettman.  I can live with what I have and recognize the issue is essentially a limitation within Access.  Jim's second comment suggests a possible alternative to consider but time constraints prevent me from exploring it.  I appreciate everyone's quick response.  I will keep the comments from the HiTechCoach for future reference.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.