Change Detail section txtbox height in vba

[wallymeister]

Hello Guru's
Wally here again for advice and suggestions.
I have a report that the user may from time to time need to add a detail section line to the report. Just to keep from having to print 2 pages so adding a line is desired. They will come to me and I will go in and change the TxtBox heights etc to allow one more line to print on page. Then in a while they'll come back and want me to put it back the way it was. So, I am trying to give them a menu option to add a line or two, changing fontSizes, put back the way it was by clicking options on a form. All the selected values written to a table for all controls and properties that apply. However, I put the code that gets these values from table via DLookup in the On_format of detail section and tried On_Print of Detail section and got dialog that says report action was cancelled. Figured I can't change these properties during print. So I tried the On_Load event of report. Doesn't cancel now but my textBoxes now not visible but I didn't change visible property.

I'm at a loss here but will keep searching for answer. If anyone can point me in the direction I need to be taking or tell me what is happening I am mucho appreciative.

Hope this makes some sense. I've done report property changes before such as fontsize, visible, forecolor etc. Never had to change control heights before.

Thanks
Wally

 

[vbaInet]

Can you show us screenshots of these two reports.

 

[wallymeister]

Wait, I just realized that the value of FindBoxHeight is .2111 but adding .0104 makes it 288. Then Me.Text93.Height is 0 after so that explains why its not visible. But why won't Me.Text93.Height not accept .2111 value?

Code examlpe
FindBoxHeight = DLookup("[TextBox_Ht]", "ReportParameters", "[DeptNumber] = '7128A'") 'Gets .2111 value from table
Me.Detail.Height = FindBoxHeight + 0.0104 'Changes Detail Section Height
After execution the value of Me.Detail.Height = 288 (not .2215)


Me.Text93.Height = FindBoxHeight 'Changes PartNum TextBox Height
After execution the value of Text93.Height = 0 (not .2111) ????

This is very confusing, I can change the FontSizes with no problem but....
Any suggestions
Wally

 

[vbaInet]

I don't need to see code, I would like to see how the reports look.

 

[wallymeister]

Ok, but I know why the fields are not showing now. As I said in my last post the value of PartNum textbox is being set to zero. Rounding down to nearest integer from decimal value of .2111 I guess. So therefore it is invisible. I can't attach bmp files for some reason. I guess they need to be on website or something.

Anyway thanks for trying
Wally

 

[vbaInet]

If you believe you have sourced the problem and fixed that's great.

See the forum's FAQs on how to upload images or db files:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23

 

[wallymeister]

Thanks vbaInet,
Yes the problem I was having was just that I couldn't use inches to set heights in vba needed (Twips = (Inches * 1440))
Got that sorted out, now I need to get the Max value in an Option group.

Don't know yet if something like...
X = DMax([Forms]![OptionGroupForm]![Frame1].Value) will work or if I'll have to loop through each radial button inside the frame. Any ideas will be helpful, otherwise I'll have to use static value and I don't want to do that if I can avoid it.

Thanks
Wally

Nope, DMax() won't work

 

[vbaInet]

Is the frame bound to a field? And what do you mean by the max of an option group?

 

[wallymeister]

Hello vbaInet,
No the frame is not bound to anything. Each radial button has option value of 36 thru 42. I want to find the highest value (42) via vba. In case I add options I don't want to have to re-code because I am using this highest number in a calculated variable. I can easily get the Frames current value but I also need the highest value to do a calculation (ratio). e.g. Say current value is 38 my calculation would be a ratio of 38/42. This is all being used to format detail section of report on the fly. Everything is working but I am currently using hard code value of 42.

Hope this explains enough
Thanks
Wally

 

[vbaInet]

If you're needing to make such design changes on a regular basis then you have a design problem. I would suggest you look into using a different control that will grow automatically as the data grows or changes. Controls like a combo box or listbox are well suited for this.

In any case, to answer your question, you need to loop through the Controls collection of the frame comparing and compare the OptionValue of each option button to find the max. Here's the code:

    Dim ctl         As Control
    Dim intMaxValue As Long
    
    For Each ctl In Me.[COLOR=Red]FrameName[/COLOR].Controls
        If ctl.ControlType = acOptionButton Then
            If intMaxValue < ctl.OptionValue Then
                intMaxValue = ctl.OptionValue
            End If
        End If
    Next
    
    Debug.Print intMaxValue

 

[wallymeister]

Not on a regular basis but this application is used by different dept's that may or may not want to add an additional item to keep report from going to 2 pages. This may be temporary or permanent. The min lines per page is 36 and the max is 42. The ratio I'm calculating is to clean up the appearance of report when things change. At any rate I am giving them an interface to increase or decrease the amount of lines per page and also font sizes for the detail section fields. As I stated I have it working but just didn't want to use a hard Constant value just in case I do ever need to add more options to the Option Group.

Thanks so much
You are awesome
Wally

PS - I am not a programmer but am probably the most knowledable in Access/VBA here at work so they ask me to do these things and I oblige. I love the challenge I guess.

 

[vbaInet]

Alright. You're welcome!