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

[Wanie230271]

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]

Maybe repeat the resize function that you use on open?

 

[CJ_London]

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]

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.

 

[CJ_London]

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]

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]

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]

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.

 

[isladogs]

Are you using my code?

 

[Wanie230271]

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]

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]

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

Automatic Form Resizing 1

Use automatic form resizing (AFR) to ensure that forms and their controls are automatically resized and repositioned for any screen size & resolution. It can also be used to add a zoom feature to Access forms

www.isladogs.co.uk

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

 

[isladogs]

Cross-posted at https://bytes.com/topic/access/answ...m-after-using-source-object-change-source-vba

@Wanie230271
ALWAYS state when you have crossposted and provide a link to the other site(s)

 

[Wanie230271]

@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

 

[isladogs]

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:

Database Analyzer Pro

The professional version of this utility allows you to quickly analyze the contents of any Access database. Features include view and analyze database objects, object metadata and get object sizes. You can view table data and table design. Also view table fields and field properties. View query...

www.isladogs.co.uk

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]

@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]

Suggest you try the suggestions I made in post #15 and let us know how it works for you