Subform resizes after using sourceobject in vba to change form source (1 Viewer)

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
Hi, I use a dropdown on a main form to change the source object of a subform. When I use the dropdown then the subform changes the sizing and goes smaller.
I use a resize form code when opening the main form but then it changes when I change the source object. Anyone got any advice on how to resize the subform again?
 

Cotswold

Active member
Local time
Today, 22:53
Joined
Dec 31, 2020
Messages
531
Maybe repeat the resize function that you use on open?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,683
changing the subform control source object will not in itself cause the subform control to change size. If it is, you are using some code to do that
 

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
I resize the subform with the on load property of the main form as below.
ReSizeForm SUBFORM.Form
When I change thesubform source object then it shrinks back to the original size.
When I try to to use the resize vba again then it covers part of the screen and doesn't fit into the subform as the screenshots show.
I need to know how to resize the subform again as it appeared after loading the main form.
 

Attachments

  • SUBFORM AFTER RESIZING.png
    SUBFORM AFTER RESIZING.png
    75.4 KB · Views: 61
  • SUBFORM AFTER UPDATING SOURCE OBJECT.png
    SUBFORM AFTER UPDATING SOURCE OBJECT.png
    83.7 KB · Views: 63
  • ORIGINAL SUBFORM.png
    ORIGINAL SUBFORM.png
    101.8 KB · Views: 59

CJ_London

Super Moderator
Staff member
Local time
Today, 22:53
Joined
Feb 19, 2013
Messages
16,683
The load event only runs once

suggest post the code you are using as you refer to a subform but your images imply more than one
 

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
I'm using 3 subforms. The on load vba is below.

Private Sub Form_Load()

DoCmd.Maximize

'auto resize form
ReSizeForm Me

'use the line below ONLY if its not included in the subform itself

ReSizeForm SUBFORM1.Form

ReSizeForm SUBFORM2.Form

ReSizeForm SUBFORM3.Form

End Sub

The VBA to change the source object is below which is repeated dependant on the year info that I want to view.

If Me!year = "2020" Then

Me![SUBFORM1].SourceObject = "SUBFORM12020"
Me![SUBFORM2].SourceObject = "SUBFORM22020"
Me![SUBFORM3].SourceObject = "SUBFORM32020"

ReSizeForm SUBFORM1.Form

End If
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,258
It appears that you are using my automatic form resizing code in the main form.
If so, you may not need to resize each individual subform separately.
 

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
If I remove the resize subform code then it appears as when I change the source object and doesn't fill the subform as the attached screenshot.
Somehow I need to resize the subform again but if I use the same code then it doesn't do the same resizing.
 

Attachments

  • SUBFORM WITHOUT RESIZING SUBFORM ON FORM LOAD.png
    SUBFORM WITHOUT RESIZING SUBFORM ON FORM LOAD.png
    83.7 KB · Views: 52

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
I'm using the below code so not sure where it comes from.

Private Sub Form_Load()

DoCmd.Maximize

Application.Echo False

'auto resize form
ReSizeForm Me

'use the line below ONLY if its not included in the subform itself

'ReSizeForm JKTPRODSHIP.Form

'ReSizeForm TRSPRODSHIP.Form

'ReSizeForm VESTPRODSHIP.Form

'next 2 lines help ensure no over enlargement of subform
'only needed for this example app
'Me.subfrmControlTypes.Width = Me.Graph67.Width
'Me.subfrmControlTypes.left = Me.Graph67.left

'minimize nav pane
MinimizeNavigationPane

'minimize ribbon
'If IsRibbonMinimized = False Then ToggleRibbonState

EnableControls True, "A", "B", "C", "D"
ShowControls True, "A", "B", "C", "D"
LockControls False, "A", "B", "C", "D"



Application.Echo True

''set form footer captions
'Me.lblVersion.Caption = GetProgramName & " Version " & GetVersion & " " & GetVersionDate
'Me.lblMDS.Caption = GetAuthor & ", " & GetCopyright

'get screen resolution
'Me.lblResolution.Caption = GetResolution() & " " & GetScreenShape() & " (" & GetFormFactor() & ")"

End Sub
 

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
Sorry, I found this

'---------------------------------------------------------------------------------------
' Procedure : ReSizeForm
' DateTime : 27/01/2003
' Author : Jamie Czernik
' Purpose : Routine should be called on a form's onOpen or onLoad event.

'Modifications: Jeff Blumson / Colin Riddington
'---------------------------------------------------------------------------------------

Public Sub ReSizeForm(frm As Access.Form)

Dim rectWindow As tRect, sngFactor As Single
Dim lngWidth As Long, lngHeight As Long

On Error Resume Next
GetScreenResolution
sngFactor = GetFactor 'local function
If NewRes.WIDTH <> DESIGN_HORZRES Then 'no resize necessary
RESIZE sngFactor, frm 'local procedure

'modification by Jeff Blumsom 18/1/07
'the following code controls the positioning of pop-up forms
'but only if the form tag is null. This allows more control where it causes a problem
If WM_apiIsZoomed(frm.hwnd) = 0 Then 'Don't change window settings for max'd form.
Access.DoCmd.RunCommand acCmdAppMaximize 'Max Access Window
Call WM_apiGetWindowRect(frm.hwnd, rectWindow)
With rectWindow
lngWidth = .right - .left
lngHeight = .bottom - .top
End With
If Nz(frm.Tag, 1) <> 1 Then
Call WM_apiMoveWindow(frm.hwnd, ((NewRes.WIDTH - _
(sngFactor * lngWidth)) / 2) - getLeftOffset, _
((NewRes.Height - (sngFactor * lngHeight)) / 2) - getTopOffset, _
lngWidth * sngFactor, lngHeight * sngFactor, 1)
End If
End If
End If

'========================================================================================
'Modification by Colin Riddington 13/03/2019
'UseMDIMode property =1 (overlapping windows) or = 0 (tabbed documents)
'next section fixes display issue for users of tabbed documents (MDIMode=0)
If CurrentDb.Properties("UseMDIMode") = 0 Then
MaximizeNavigationPane
DoEvents
MinimizeNavigationPane
End If
'========================================================================================

End Sub
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,258
You are using an old version of the code originally written by Jamie Czernik back in 2003 but significantly modified by me over the past 20 years.
Please study my detailed 4-part tutorial starting with

I recommend you also download the latest version of the example app with updated code
 

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
@isladogs Thanks for the advice.

I've gone through a lot of the tutorials and updated the code so it works perfect when I open the form.
However it still has the same issue when I update the source object as the attachment.
What's really strange is that if I open the navigation pane then the subform changes and is okay.
I'd appreciate it if you have any ideas.

What I did was put the resizeform on the subform and then also unresizeform on close.

Below is the code to change the source object.

If Me!year = "2020" Then

Me![SUBFORM].SourceObject = "SUBFORM2020"

End If
 

Attachments

  • ORIGINAL SUBFORM.png
    ORIGINAL SUBFORM.png
    101.8 KB · Views: 52
  • SUBFORM AFTER UPDATING SOURCE OBJECT.png
    SUBFORM AFTER UPDATING SOURCE OBJECT.png
    83.7 KB · Views: 49
  • NAVIGATION PANE.png
    NAVIGATION PANE.png
    180.4 KB · Views: 55

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,258
To be honest its difficult to understand exactly what your screenshots are showing
Just to clarify, I believe you have a form with 3 subforms, each of which you are resizing on load & unresizing on close.
Each of those subform source objects are replaced when you filter using the combo to a different year.

The nav pane screenshot indicates a design problem unrelated to the use of AFR but which impacts significantly on your problem.
You have multiple copies of the same subform, each for a particular year. That's not a good approach whether or not AFR is used.

Instead, you should have just one of each type of subform which then gets FILTERED using the combo.
By doing that there are several advantages including:
  • only one form & set of code to maintain
  • fewer objects so a smaller database
  • no need to change the source object when the combo selection is changed therefore your issue should no longer occur
  • faster due to not needing to keep changing source objects
In addition, I would recommend trying to avoid resizing the subforms themselves.
It is something that I hardly ever do and it significantly reduces any issues with AFR.

By chance, I released a new app today which has a main form with a subform.
There are ten different subforms that are used as the subform source object dependant on what the user selects.
There are multiple screenshots of this in my web article:

The source object is controlled by toggle button clicks in a frame and there are no issues with positioning.
Originally, I used a tab control and that did cause issues - explained in one of my tutorial articles
Using tabbed documents display also used to cause issues but I rewrote the AFR code several years ago to deal with that.

So my advice is:
1. Eliminate the multiple versions of each subform and use the combo to filter
2. Try to redesign the subforms so there is no need to resize each of these as well as the main form
3. Use overlapping windows display
4. Don't use tab controls

I have many form/subform arrangements that are far more complex than yours and which use AFR successfully
 

Wanie230271

New member
Local time
Tomorrow, 03:53
Joined
Sep 1, 2023
Messages
8
@isladogs You are correct in that I have a form with 3 subforms, each of which I am resizing on load & unresizing on close.
The 3 combos are used to change the source object on the subforms dependant on year, factory or owner.
Basically, I want to analyse the quantity or value per customer month wise and yearly dependant on criteria.

The 3 subforms use a crosstab/pivot query and my issue is that I can't understand how to use vba to change the crosstab query by the combos and its led me down a route which is too complex and unmanageable.

Thanks for making me see sense and I'm now presuming its better to start a new thread but any other advise would be welcome.
 

isladogs

MVP / VIP
Local time
Today, 22:53
Joined
Jan 14, 2017
Messages
18,258
Suggest you try the suggestions I made in post #15 and let us know how it works for you
 

Users who are viewing this thread

Top Bottom