Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-13
5
Medium Priority
?
762 Views
Last Modified: 2013-11-14
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.
0
Comment
Question by:Liberty4all
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 750 total points
ID: 39645808
afaik, that setting is only available in design view...
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 750 total points
ID: 39645833
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
 
LVL 21
ID: 39646058
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
 
LVL 58
ID: 39646085
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
 

Author Closing Comment

by:Liberty4all
ID: 39649496
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question