another shrink grow form problem

[ares13]

I have a Mainform and a subform. The subform is a ‘summary’ form which contains about 30 textboxes with a label for each. Textboxes are arranged on the form as
Label1
Textbox1
Label2 Textbox2 label 3 textbox3
Label4
Textbox4
etc
Each textbox has a function as the controlsource, which looks in other tables and builds a string to display in the textbox.

In a user maintenace area of the app I have note definition form and table which has checkboxes corresponding to each textbox on my subform. The user can ‘define’ notetypes by checking what they want to appear on the subform. When a record is created elsewhere this notetype is stored in the record. Depending on which type of note they open, I want to reference the note definition table and determine what they want to see on the form.

I can set the textfields to visibe=false on my subform, call a function to check the definition table when the form opens, and turn on what I want to see.

I need both the textfields and subform to shrink and grow accordingly. Any visible textbox must grow to size, and all visible textboxes need to show with no gaps above or below. I have code to resize the textfields when they are all visible, however if they are to stay invisible, they do not shrink even if the function used as their control source returns nothing. Setting height=0 on a txtbox before using the resize code I have just opens a blank form.

Is there any way to remove the textbox and label from the form dynamically if they are not supposed to show?

I may be approaching this from the wrong angle and welcome any suggestions.

 

[Eupher]

Hi, ares13,

Yes, you can dynamically add and remove controls through code (only in design view), but I think that's probably way more trouble than it's worth.

Take a look at the controls' Top and Left properties in addition to Height. With experimentation, you should be able to come up with a matrix that positions the controls as you wish.

HTH,

Ken S.

 

[ares13]

Thanks for the reply Eupher. I fear I may be in over my head here. This is the code I am using (it was provided here by CautionMP) to shrink currently...it may be close but every mod I have tried has just resulted in a blank form.

Private Sub RepositionControlsRelative()
Dim objControl As Control
Dim intOldTop As Integer, intRelativeAdjust As Integer

Set objControl = Me.ActiveControl
With objControl
intOldTop = .Top
intRelativeAdjust = .Height
.Height = ((Len(Nz(.Value, "")) \ cCharactersPerLine) + 1) * (cRowHeight * 1440)
intRelativeAdjust = .Height - intRelativeAdjust
End With
Me.Detail.Height = Me.Detail.Height + intRelativeAdjust

For Each objControl In Me.Controls
If objControl.Top > intOldTop Then
objControl.Top = objControl.Top + intRelativeAdjust
End If
Next objControl
End Sub

I set focus on each control and call the function , one at a time when the form opens.
I think i need something like this but cant seem to figure it out:

if txt1.visible=false then
(something here to resize to 0?)
(I cant set focus here because visible=false)
(I dont care if visible=true if I could get it to shrink to 0)
else
txt1.setfocus
RepositionControls
end if

Thanks to anyone for suggestions

 

[Eupher]

Nah, you're not in over your head. You're on the right track, we just need to apply a bit of logic to the situation.

First, there's no need to set focus to each individual control to re-position or re-size it. Anyway, you *can't* set focus to an invisible control.

Second, according to your scenario, we don't really care where the invisible controls are, we just want the visible controls to be aligned and sized properly. Have I got that right? At the moment, your code positions the controls regardless of their .visible state.

So I think the way to approach it is this:

1) When the form opens (maybe OnCurrent event), loop through all the controls on the form
2) While looping, first determine what type of control it is (don't want to mess with labels and lines and checkboxes, etc., right?)
3) Then test whether the control meets the criteria to be visible, i.e. Not IsNull or whatever
4) If it meets the visibility criteria, make it visible, reposition and resize

Since you're pretty much building the form dynamically, it probably makes sense to save the form in design mode with all controls of variable size/position stacked on top of each other.

Tinker with that, I will play with it also.

HTH,

Ken S.

 

[ares13]

First, thanks for your time on this.

several things here;
1. using the function CautionMP provided, if the control doesnt have focus, it fails at
Set objControl = Me.ActiveControl
so that is why I first setfocus, then call the function.
2. The form is laid out in a specific order, section A, B, C, D etc. If B and C are to be invisible (determined elsewhere by looking in a table), then A and D need to appear in order with no gap.
3. if section B is to be invisible, both it and its label need to go away. Currently this involves 2 or more lines
4. Only dealing with labels and txtboxes.
5. I think I have it worked out to determine what needs to be visible or not before the form opens, and its easy enough to loop through and turn things off. Its the layout issue that doesnt want to cooperate! Up until now, I have just displayed all fields on the form, empty or not, and the function above works great to expand the text boxes. However, now I need them to shrink below 1 line, and the labels and the space they take up need to shrink as well.

 

[Eupher]

Hi, ares13,

1) You don't need to use the .ActiveControl object if you are looping through the controls
2) Yup, got that.
3) By "lines" I presume you mean lines of text, not line controls, right?
4) Good, that makes it easier.
5) My idea basically involves a form with all the textboxes stacked on top of each other. When the form opens, and as you scroll through the records, the code loops through the controls, validates visible status, resizes the detail section, and resizes and repositions the textboxes and associated labels. It involves a bit of code and some trial and error, but... is definitely achievable.

I'll tinker and see what I come up with...

Ken S.

 

[ares13]

Thanks Eupher,
yes, by lines I mean lines of text...
your idea sounds interesting, I look forward to seeing what you figure out.

 

[SuryaF]

All right, try this.
You said

The form is laid out in a specific order, section A, B, C, D etc. If B and C are to be invisible (determined elsewhere by looking in a table), then A and D need to appear in order with no gap.

I assume you need only shrinking on vertical not horizontal. So:
1. For all controls in a section, put in the Tag property the section number (starting with 1)
2. Paste the following code into your form:

Option Compare Database
Option Base 1
Dim STops As Variant 'the top margins of the sections if all are visible
Dim STopsCrt As Variant 'the current top margins of the sections in inches

Private Sub Form_Load()
STops = Array(0.125, 0.375, 0.7917, 1.0417) 'in inches; the top margins of the sections if all are visible
STopsCrt = STops
End Sub

Function RefrSections()
Dim SMoveUp(10) As Double 'how much the section needs to move up
Dim SVis(10) As Boolean 'section visibility array

'taking the visibilities from some controls
SVis(1) = Me.S1Vis
SVis(2) = Me.S2Vis
SVis(3) = Me.S3Vis
SVis(4) = Me.S4Vis

Dim SCount As Integer 'number of sections
SCount = UBound(STops)

Dim Shrink As Double 'cumulative shrinking - in inches
Shrink = 0

Dim SCrt As Integer 'current section
SMoveUp(1) = 0
For SCrt = 2 To SCount
    If Not SVis(SCrt - 1) Then
        Shrink = Shrink + STops(SCrt) - STops(SCrt - 1)
    End If
    SMoveUp(SCrt) = Shrink - (STops(SCrt) - STopsCrt(SCrt)) 'how much the section needs to move up (relative to current position)
    If SVis(SCrt) Then STopsCrt(SCrt) = STops(SCrt) - Shrink 'resetting the current top margin
Next SCrt

Dim C As Control
For Each C In Me.Controls
    If IsNumeric(Nz(C.Tag, "")) Then
        SCrt = Val(C.Tag)
        C.Visible = SVis(SCrt)
        If SVis(SCrt) Then C.Top = C.Top - 1440 * SMoveUp(SCrt)
    End If
Next C

End Function

3. change the code (the Form_Load) and put in that array the Top value of the first control in the section

4. change the code (the RefrSections) to take the section visibilities from your own controls or fields

Give it a try, see if it works for you.
Cheers.

 

[Eupher]

ares13,

SuryaF's solution looks promising. I was thinking in terms of individual controls, but after re-reading your initial post it seems you have sections comprised of groups of controls. I think SuryaF takes this into account better than the idea I had envisioned.

Let us know how it goes.

Ken S.

 

[ares13]

SuryaF thank you for taking the time to try and help me.
I followed your instructions, and try and call the function when the form opens. I am get a type mismatch error on
SCount = UBound(STops)

I currently only have 4 items in the array.

Looking at your code, maybe need to clarify some more. When I say 'section' I am referring to a single label and single textbox, arranged as:

label5
textbox5

several are arranged as
label6 textbox6 label7 textbox7

the textbox needs to grow or shrink to size, so it is impossible to know what the top margins are when all are visible because that varies depending on how much text is in each textbox.

The code I posted before handled the shrink grow on the form very well, but only if all are visible.

 

[Eupher]

Okay, just so we're all talking the same language here:

1) When you say "section" you mean simply a textbox/label pair, right? Is the label associated with the textbox, IOW is it the label that Access automatically attaches when you place a textbox control, or is it free-standing?

2) Some of the textbox/label pairs are arranged vertically, some are arranged horizontally, like so:

[tt]label1
textbox1

label2 textbox2 label3 textbox3[/tt]

3) The height of each textbox will vary, depending on the amount of text in the field. How many characters per line?

4) All these textbox/label pairs are contained within a subform, which also must shrink/grow depending on the data in the textboxes.

Have I got that right?

Ken S.

 

[Eupher]

p.s. Visibility of each textbox/label pair is determined by a corresponding checkbox value, right? What do you want to happen if the checkbox is true but there is no data in the field?

Ken S.

 

[SuryaF]

So first you need to expand the height of all textboxes, then get the sections' top margin and everything else remains the same. I took your code and adapted it to work for all controls at once without moving the focus.

Option Compare Database
Option Base 1
Dim STops As Variant  'the top margins of the sections if all are visible
Dim STopsCrt As Variant 'the current margins of the sections in inches

Private Sub Form_Load()
ResizeAllControls

STops = Array(1000, 1000, 1000, 1000)
'find the minimum value of the top margin of the controls in a section
Dim C As Control, SCrt As Integer
For Each C In Me.Controls
    If IsNumeric(Nz(C.Tag, "")) Then
        SCrt = Val(C.Tag)
        If STops(SCrt) > C.Top / 1440 Then STops(SCrt) = C.Top / 1440 'put the top of the first control
    End If
Next C

STopsCrt = STops
End Sub

Private Sub ResizeAllControls()

Dim objControl As Control, objControl2 As Control
Dim intOldTop As Integer, intRelativeAdjust As Integer
Const cCharactersPerLine = 40, cRowHeight = 0.1667

For Each objControl In Me.Controls
    If objControl.ControlType = acTextBox Then
        With objControl
          intOldTop = .Top
          intRelativeAdjust = .Height
          lngth = Len(Nz(.Value, ""))
          rows = ((Len(Nz(.Value, "")) \ cCharactersPerLine) + 1)
          .Height = ((Len(Nz(.Value, "")) \ cCharactersPerLine) + 1) * (cRowHeight * 1440)
          intRelativeAdjust = .Height - intRelativeAdjust
        End With
        Me.Detail.Height = Me.Detail.Height + intRelativeAdjust
        If intRelativeAdjust <> 0 Then
            For Each objControl2 In Me.Controls
              If objControl2.Top > intOldTop Then
                objControl2.Top = objControl2.Top + intRelativeAdjust
              End If
            Next objControl2
        End If
    End If
Next objControl

End Sub

The RefrSections is the same. See if this works.
Cheers!

 

[ares13]

Looks like I have this working, thanks to both of you for your help!