form display problem

[ares13]

hope someone can offer a suggestion.

Have several input forms, and a 'summary' form with multiple unbound text boxes. The user starts on a
mostly blank summary form, selects a button to go to an input form, returns to the summary form and sees the data they just entered.

The control source for the text boxes on the summary form are functions, i.e. =customer_issues(), the reason for this is that complicated manipulation and concatenation happens inside the functions, and this is the only way I can get the data to display on the summary form exactly how I want it.

Here is the problem...I want the text boxes to shrink or grow to size.

I have tried Stephen Lebans CanGrow function, it works but does not allow spacing between the text boxes, if text box 1 grows vertically, it overlaps on top of text box 2 below it.

I have considered one large text box, concatenating all of the functions and adding line breaks etc, but I need a header for the contents of each text box in bold.

This led me to look at an rtf control, but havent found one that can use a function as a recordsource and create rtf formatted text on the fly.

Have also tried making a report, which does display, shrink and grow exactly as I want it, and displaying a snapshot on the summary form using docmd.outputto. Trouble there is that everytime the form reloads after a change on an input form, a msgbox saying "outputting to .snp path name blah blah" pops up, which also contains a cancel button. Doesnt seem to be any way to hide this box, tried echo=false, setwarnings false...googling tells me there is no way to hide it and cant risk the user not restraining themselves from clicking cancel.

sorry for the length...have worked on this problem for weeks now and need some new ideas. Thanks for any ideas.

 

[hneal98]

Why don't you just assign the value of the function to the rtf control when you are returning to the 'summary' form? like this:

rtfControl.text =customer_issues()

 

[CautionMP]

Here's something to try, this will dynamicly change the height of the defined text boxes and the detail section of the form at runtime.

I used the [tt]Exit()[/tt] event on the text boxes to trigger this code, you may want to change this to meet your needs.

Assumptions: The form has 4 text boxes with the names and layout as follows:
[tt] Text0: ____
Text1: ____
Text2: ____
Text3: ____[/tt]

The Height of each text box is 0.1771", with a width of 1.0833" or roughly 16 Characters.
The Labels for each text box ARE NOT moved with this code, I leave that to you.

Here is all the code in my forms' code module:

Option Compare Database
Option Explicit

'These constants are in inches, they will convert to twips later
Const cRowHeight = 0.1771
Const cControlSpacing = 0.0313
'Rough estimate of the characters that fit in a 1.083" wide control
Const cCharactersPerLine = 16
'Container for the controls that will resize
Dim arrControls(3) As String

Private Sub RepositionControls()
Dim objControl As Control
Dim blnMoveControl As Boolean
Dim intNewHeight As Integer, intNextTop As Integer
Dim intControlTextLength As Integer
Dim intCurrentControl As Integer
Dim strActiveControl As String

'Initialize variable
blnMoveControl = False
'Get the name and text length of the calling control
strActiveControl = ActiveControl.Name
intControlTextLength = Len(Nz(ActiveControl.Value, ""))

'Calculate the new height
intNewHeight = ((intControlTextLength \ cCharactersPerLine) + 1) * (cRowHeight * 1440)

'Cycle through all the controls defined within arrControls
For intCurrentControl = 0 To UBound(arrControls)
  If blnMoveControl Then
   'This is for controls after the active control
    Set objControl = Me.Controls(arrControls(intCurrentControl))
    objControl.Top = intNextTop
    intNextTop = intNextTop + objControl.Height + (cControlSpacing * 1440)
  ElseIf arrControls(intCurrentControl) = strActiveControl Then
    'This is for the active control
    Set objControl = Me.Controls(strActiveControl)
    Me.Detail.Height = Me.Detail.Height + (objControl.Height - intNewHeight)
    objControl.Height = intNewHeight
    intNextTop = objControl.Top + intNewHeight + (cControlSpacing * 1440)
    blnMoveControl = True
  End If
Next intCurrentControl
End Sub

Private Sub Form_Load()
arrControls(0) = "Text0"
arrControls(1) = "Text1"
arrControls(2) = "Text2"
arrControls(3) = "Text3"
End Sub

Private Sub Text0_Exit(Cancel As Integer)
RepositionControls
End Sub

Private Sub Text1_Exit(Cancel As Integer)
RepositionControls
End Sub

Private Sub Text2_Exit(Cancel As Integer)
RepositionControls
End Sub

Private Sub Text3_Exit(Cancel As Integer)
RepositionControls
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

Oops, botched a line:

...
'Cycle through all the controls defined within arrControls
For intCurrentControl = 0 To UBound(arrControls)
  If blnMoveControl Then
   'This is for controls after the active control
    Set objControl = Me.Controls(arrControls(intCurrentControl))
    objControl.Top = intNextTop
    intNextTop = intNextTop + objControl.Height + (cControlSpacing * 1440)
  ElseIf arrControls(intCurrentControl) = strActiveControl Then
    'This is for the active control
    Set objControl = Me.Controls(strActiveControl)
    '[s]Me.Detail.Height = Me.Detail.Height + (objControl.Height - intNewHeight)[/s]
    Me.Detail.Height = Me.Detail.Height + [b](intNewHeight - objControl.Height)[/b]
    objControl.Height = intNewHeight
    intNextTop = objControl.Top + intNewHeight + (cControlSpacing * 1440)
    blnMoveControl = True
  End If
Next intCurrentControl
...

(GMT-07:00) Mountain Time (US & Canada)

 

[ares13]

Thank you both for the suggestions...

hneal98, when I try this it says the object does not support that method...I would still be unable to format the headings as bold text if I understand correctly what you are suggesting and it did work.

CautionMP, thanks for sharing that code. I have not had a lot of time to work on this, but did do some testing and this gets me closer than I have been to getting what I am looking for.

Adding labels into the array produces a variety of errors, as does having two objects on the same line that need to be move together (like a 'textbox as a label' or a label and a textbox.)

Do you know of any way to include objects that need to move down but not resize? I will continue to work on this.
Thanks again to both of you for the help.

 

[hneal98]

In an rtf control, you should have more control over formatting than a regular text box. I would have to see your code to be able to tell you what the problem is, but I would think it is very doable, but if what cautionMP is suggesting is easier, by all means, go for it. It is always better to do something you understand as apposed to trying to figure out something else when you are in a crunch.

 

[CautionMP]

[tt]arrControls()[/tt] is esentially an array representation of your form. As written it is only one dimension, if you have controls that are next to each other (parallel) you need to add another (or multiple) dimension(s) to the array.

Assumption: The form has 4 text boxes, each with an associated label with the names and layout as follows:[tt]
Label_0 : Text0
Label_1 : Text1
Label_2 : Text2
Label_3 : Text3[/tt]

This necessitate the following changes to the code:
Global Declarations

'Container for the controls that will resize
Dim arrControls(3,1) As String

Intialize [tt]arrControls()[/tt]

Private Sub Form_Load()
arrControls(0,0) = "Text0"
arrControls(0,1) = "Label_0"
arrControls(1,0) = "Text1"
arrControls(1,1) = "Label_1"
arrControls(2,0) = "Text2"
arrControls(2,1) = "Label_2"
arrControls(3,0) = "Text3"
arrControls(3,1) = "Label_3"
End Sub

Updated [tt]RepositionControls[/tt]

Private Sub RepositionControls()
Dim objControl As Control
Dim blnMoveControl As Boolean
Dim intNewHeight As Integer, intNextTop As Integer
Dim intControlTextLength As Integer
Dim intCurrentControl As Integer, [b]intDependantControl As Integer[/b]
Dim strActiveControl As String

'Initialize variable
blnMoveControl = False
'Get the name and text length of the calling control
strActiveControl = ActiveControl.Name
intControlTextLength = Len(Nz(ActiveControl.Value, ""))

'Calculate the new height
intNewHeight = ((intControlTextLength \ cCharactersPerLine) + 1) * (cRowHeight * 1440)

'Cycle through all the controls defined within arrControls
For intCurrentControl = 0 To [b]UBound(arrControls, 1)[/b]
  If blnMoveControl Then
   'This is for controls after the active control
    Set objControl = Me.Controls([b]arrControls(intCurrentControl, 0)[/b])
    objControl.Top = intNextTop
    [b]For intDependantControl = 0 To UBound(arrControls, 2)
      'Test to make sure there is a Dependant Control
      If Len(arrControls(intCurrentControl, intDependantControl)) > 0 Then
        Set objControl = Me.Controls(arrControls(intCurrentControl, intDependantControl))
        objControl.Top = intNextTop
      End If
    Next intDependantControl[/b]
    intNextTop = intNextTop + objControl.Height + (cControlSpacing * 1440)
  ElseIf [b]arrControls(intCurrentControl, 0)[/b] = strActiveControl Then
    'This is for the active control
    Set objControl = Me.Controls(strActiveControl)
    Me.Detail.Height = Me.Detail.Height + (intNewHeight - objControl.Height)
    objControl.Height = intNewHeight
    intNextTop = objControl.Top + intNewHeight + (cControlSpacing * 1440)
    blnMoveControl = True
  End If
Next intCurrentControl
Set objControl = Nothing
End Sub

You can resize [tt]arrControls()[/tt] to any size you need, if you have 2 controls on most lines and 3 on a couple, leave the 3rd dimension blank. The revised code will check for a value before trying to move the control.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[ares13]

hneal98, well, wouldnt say I totally understand but am making good progress

CautionMP, wow, this works great! Doing exactly what I need. Couple of questions and I think I am there. In reference to your last sentence above, I actually have 2 controls on most lines, 4 on several.

label_0 : text0
label_1 : text1 label_2 : text2
label_3 : text3

Have not worked with arrays much, in this case would arrControls be (3,3)??

still not exactly clear on what you mean by leave 3rd dimension blank...are you saying I could change arrControls as above without any other changes? This is not working, hoping im close...

arrControls(0, 0) = "Text0"
arrControls(0, 1) = "Label0"
arrControls(1, 0) = "Text1"
arrControls(1, 1) = "Label1"
arrControls(1, 2) = "Text2"
arrControls(1, 3) = "Label2"
arrControls(2, 0) = "Text3"
arrControls(2, 1) = "Label3"


Thank you so much for the help, cannot tell you how badly this has been vexing me.

 

[CautionMP]

Ares13,
You are almost there. You are correct on how to add another dimension to the array:
[tt][navy]Dim[/navy] arrControls([red]3[/red], [green]3[/green]) [navy]As String[/navy][/tt]

Since we are making [tt]label_2[/tt] and [tt]text2[/tt] dependent to text/label 1 the first dimension of the array is actually reduced by one.
[tt][navy]Dim[/navy] arrControls([red]2[/red], [green]3[/green]) [navy]As String[/navy][/tt]

Think of the first dimension as the total 'lines' top to bottom on your form (start counting at Zero), and the second dimension as the number of controls from left to right (did I mention starting at zero?).[tt]
[green] 0 1 2 3[/green]
[red]0[/red] label_0 : text0
[red]1[/red] label_1 : text1 label_2 : text2
[red]2[/red] label_3 : text3[/tt]

Once you get this working I can show you another solution to this pitfall,
CMP
[small]Color coding provided because a picture is worth a thousand words![/small]

(GMT-07:00) Mountain Time (US & Canada)

 

[ares13]

OK, starting to work through this, and so far so good, works perfectly. I have about 80 objects on the form so it will be slow going to get it all worked out...

pitfall?

 

[CautionMP]

Ok, moving eight objects is a LOT different than moving eighty.

I re-thought the logic, and your going to kill me. Long and short of the new logic, we reposition the [tt]ActiveControl[/tt] and determine an offset ([tt]intRelativeAdjust[/tt]), then loop through all the controls of the form and adjust any where the [tt].Top[/tt] value is greater than the active control:

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 didn't do much commenting so hopefully you will get this before you have too many controls loaded into an array.

Sorry the easier way didn't occur to me until you said '80',
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[ares13]

I havent gotten that far yet, did initial testing in a simple test db, then started applying to mine.

One thing I noticed trying this new method...
I have buttons to the right of these text fields, using the array they did not move, using this method they do. I may just make a subform with all of the text fields and leave the buttons on the main form unless there is some way to keep the buttons anchored. I had been thinking of doing this anyway because you have to scroll to see all of the fields, and I would rather have the user scroll through the data summary than the entire form.

Appreciate your effort and time on this...

 

[CautionMP]

When you cycle through the controls you can setup a 'test' to determine if the control should move.
[ol]
[li]Make the decision based on the [tt]ControlType[/tt] property.

...
For Each objControl In Me.Controls
   If objControl.Top > intOldTop And objControl.Properties("ControlType") <> 104 Then
...

[ul]
[li]100 = Label[/li]
[li]104 = CommandButton[/li]
[li]109 = TextBox[/li]
[/ul]
[/li]
[li]If you prefix the name of your controls, make the decision based on the prefix (standard Hungarian Notation listed).

...
For Each objControl In Me.Controls
   If objControl.Top > intOldTop And Left(objControl.Name,3) <> "cmd" Then
...

[ul]
[li]lbl = Label[/li]
[li]txt = Textbox[/li]
[li]cmd = CommandButton[/li]
[/ul]
[/li]
[/ol]

This will probably be my last post unless you post back with a problem. I fear that we may be approaching infromation overload.

Enjoy,
CMP

(GMT-07:00) Mountain Time (US & Canada)