Resizing Forms

[Denburt]

Wow Melissa you sound busy lol.

Can you explain about how to cascade updates and deletes? That sounds like good practice regardless of how the forms/subforms are displayed!

In the main Database window click on tools - relationships from there add your tables create the relationship and set it to referential integrity then set cascade update and deletes.That should take care of any problems with corelating the records. If you want you can disable each control that is on the main form but that is about the only other thing you might consider. You will need some way to exit the subform and enable all the controls also so think about that before you attempt the following:

Dim ctl As Control
For Each ctl In frm.Controls
If not ctl.ControlType = 122 Then
ctl.Enabled = false
end if
Next

For resizing subforms I use the following and it works pretty good...

Private Sub Form_Resize()
  On Error GoTo ResizeError
ResizeIt Me, Me!Hedging_subform, 300, 2800
Exit_Sub:
Exit Sub
ResizeError:
        MsgBox Err.Number & "   " & Err.Description
End Sub

Sub ResizeIt(myFrm As Form, mySubFrm As Control, X As Integer, Y As Integer)
On Error GoTo ResizeError

        'Turn off screen redraw
        Application.Echo False
        If mySubFrm.Top + mySubFrm.Height + 100 <= ((myFrm.InsideHeight - mySubFrm.Top) - Y) Then  'myFrm.InsideHeight Then
            If Y > 0 Then
                mySubFrm.Height = ((myFrm.InsideHeight - mySubFrm.Top) - Y)
            End If
            myFrm.Section("DETail").Height = mySubFrm.Height
            If X > 0 Then
                mySubFrm.Width = myFrm.InsideWidth - X
            End If
        Else
            If Y > 0 Then
                If ((myFrm.InsideHeight - mySubFrm.Top) - Y) > 0 Then
                    mySubFrm.Height = ((myFrm.InsideHeight - mySubFrm.Top) - Y)
                End If
            End If
            If X > 0 Then
                mySubFrm.Width = myFrm.InsideWidth - X
            End If
        End If
        
        'Turn screen redraw back on
        Application.Echo True

    Exit Sub
Exit_Sub:
Exit Sub

ResizeError:
        ' NB: Turn screen redraw back on if an error occurs!
        Application.Echo True
        MsgBox Err.Number & "   " & Err.Description
End Sub

[AccessIdiot]

Thanks for the reply Denburt!

Actually I did figure out that I had the cascading already set when I created my table relationships diagram. :-)

I'm an Access noob and not very good with VB. Any chance you could explain your code a bit? Particularly the things I need to change for my own code? :-D

I imagine this line for example:

ResizeIt Me, Me!Hedging_subform, 300, 2800

I would change the name of the subform and the 300 and 2800. But change the values to what? What do they refer to exactly? The new dimensions I want? And what is an easy way of finding that out?

thanks!

[Denburt]

The numbers refer to the distance from the edge of your main form (side and bottom), I like to leave a little gap even if nothing is next to it. If you have a control beside or below the subform you may want more distance. I would like to add that anything beside or below it will need to be moved as well so the resizing of the subform doesn't overlap.

Me!MyButtonUnder.top = Me!MySubform.top+Me!MySubform.height+300

You can actually change the number, then resize the form and see the results immediately.

[Denburt]

I should probably have added that the unit of measurement in VBA is Twips.

twip: Unit of measurement that is equal to 1/20 of a point, or 1/1,440 of an inch. There are 567 twips in a centimeter.

[AccessIdiot]

Do you mean that the numbers refer to the size I want the form to be without the subform visible? Sorry, I guess I'm still not getting it. I tried playing with the numbers, making them really small or really big but I'm not sure how it's resizing.

[Denburt]

O.K. sry reread your original post the code I supplied earlier is for when it is visible and you resize the main form the subform resizes accordingly.

To set your subform to a specific size you would need to designate that manually. I have never set a subform to height to 0 that I can recall but you can try it.

ME!MySubform.He ight=0
ME!MySubform.Wi dth=0
After resizing the subform then you can set the size of the main form so you dont see a big blank space.
Me.height = 1440 '1 inch

Once you want to show the subform then you would make it visible again.

ME!MySubform.He ight=2880
ME!MySubform.Wi dth=2880

[NeoPa]

I don't think you can resize a form to be any smaller than it would need to be to handle the existing controls on it (Any more than you can do this as a designer when you have the form open in Design View).
I'm fairly sure that would not be a practical solution for your requirements. It's always good to get ideas from various sources though. Sometimes an idea that may not work for one thing can reap dividends later in another situation.
I'll go back to the other thread to consider what can be done.

[AccessIdiot]

Yes I'm not sure this works for me. I've been playing with it a bit (sorry I got waylaid but interviewing job applicants) and while it does a great job resizing the subform it does nothing with the space the subform occupies on the form itself.

I guess what I would need is to shrink the form down to just what it would be without a subform when the subform is not visible and then enlarge the form to include the subform when the subform shows.

I'll look into the other code you provided - haven't gotten to it yet.

[Denburt]

OK I did need to clean it up sry for that.
'you dont need to resize the subform or the detail section but it will remove or reduce the right scrollbar if it is enabled
Me!SubFormName. Height = 0
Me.Section(acDe tail).Height = 1440 '1 inch

'This is the size you want your form just adjust the number to suite your needs
Me.InsideHeight = 200


Tried and tested, just adjust the name of the subform and adjust your numbers you should be OK

[Denburt]

Oh and if you use this in conjunction with the resizing code I posted earlier you will want to check the status of the subform first or the previous post will not do anything.

In the resize section in the VBA you can add a line:

if me!Subform.visi ble = false then exit sub

[AccessIdiot]

Okay, I'm sorry, like I said I'm a total noob so I may need some help with actual code here . . . I'm not sure where to put the bits you just added?

[Denburt]

The following code can be placed in the forms open event.

'you dont need to resize the subform or the detail section but it will remove or reduce the right scrollbar if it is enabled
Me!SubFormName.Height = 0
Me.Section(acDetail).Height = 1440 '1 inch

'This is the size you want your form just adjust the number to suite your needs
Me.InsideHeight = 200

If you are using the code I provided earlier then you will need to add the following to the beggining of the forms resize event. If not skip this.

if me!Subform.visible = false then exit sub

[Denburt]

Oh and don't be sorry I was a noob once too! :) Glad to try and help.

[AccessIdiot]

Thanks for your patience!

I'm going to put this method on hold for now and see if I can get the form/subform to enable/disable using button events. For anyone interested that discussion can be found here:

enable subform with button