Delay subform query and column widths (1 Viewer)

cosmarchy

Registered User.
Local time
Today, 00:15
Joined
Jan 19, 2010
Messages
116
I have a main form containing a subform. As the subform query takes a few minutes to complete and the information doesn't need to be available immediately anyway, I want to delay the subform query until I click an update button instead if it delaying the opening of the main form.
Is there anything built in to VBA to prevent a subform from loading the data when the main form is opened? Presumably to get the subform to update afterwards you'd just refresh / requery the subform on the button click event???

When I do update the data in the subform, I'd like columns 1, 2, 3, 5 and 6 to be fixed at a particular width and column 4 do expand to what ever is available. My thinking is when the mainform loads, I'll get the subform width and subtract the fixed column widths of 1, 2, 3, 5 and 6 before making column 4 width whatever value if left over.
How to you access the subforms column from the mainform? Is there an easier way to do this? I wondered whether you could do this from the subform itself but I'm not sure it could determine it's width on the mainform...

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:15
Joined
Oct 29, 2018
Messages
21,358
Hi. Simply leave the subform unbound and just use your button to assign a source object to it.
 

MarkK

bit cruncher
Local time
Today, 00:15
Joined
Mar 17, 2004
Messages
8,178
Typically classes should take care of themselves, so if the subform needs to alter it's column widths under certain circumstances, it should contain its own capability to do so. If you control one object from the outside--from code that resides in a different object--it's a bit like trying to drive your car from the back seat; worth avoiding. Any form, even when loading as a subform, raises a Resize event you can use to customize its display.

A little trick to (seemingly) asynchronously loading subforms is to do so after the main from opens, on a timer. Make sure the subform is NOT present in the subform control in design view, then do something like...
Code:
Sub Form_Open(Cancel As Integer)
    ' start the timer
    Me.TimerInterval = 25
    ' main form opens and becomes visible on screen
End Sub

Sub Form_Timer()
   Me.TimerInterval = 0
   ' load the subform programmatically after the main form has displayed itself
   Me.sfm.SourceObject = "fSubform"
End Sub
Makes sense?
 

cosmarchy

Registered User.
Local time
Today, 00:15
Joined
Jan 19, 2010
Messages
116
Thanks for your replies guys.

I have the delayed refresh working as suggested by creating the sourceobject for the subform in the button push event.

What I don't fully understand is the adjustment of the columns. I can see where @MarkK is coming from in that the subform should take care of itself, but what I don't get is how the subform knows what size it is in the main form so that column 4 can be adjusted to whatever width is left after fixing columns 1, 2, 3, 5 and 6.
If I fix the width of columns 1, 2, 3, 5 and 6, I need to know what width the subform is in order to adjust column 4 accordingly, but it would need to know about the with on the form hosting it...

Or, have I misunderstood something here?
 

MarkK

bit cruncher
Local time
Today, 00:15
Joined
Mar 17, 2004
Messages
8,178
Check the subform's InsideWidth property during its Resize event, and use that value to scale your controls. Consider...
Code:
Private Sub Form_Resize()
    Dim scaleBy As Single
    
    scaleBy = Me.InsideWidth / 9
    Me.JobSection.ColumnWidth = scaleBy
    Me.SectionName.ColumnWidth = 3 * scaleBy
    Me.Status.ColumnWidth = 3 * scaleBy
    Me.OwnerID.ColumnWidth = 2 * scaleBy
    ' note how with this approach all columns are scaled, but some (those multiplied by 3) are wider
End Sub
If you only need to scale one column you could do...
Code:
Private Sub Form_Resize()
    Me.JobSection.ColumnWidth = 0.5 * 1440
    Me.SectionName.ColumnWidth = Me.InsideWidth - 1.5 * 1440
    Me.Status.ColumnWidth = 0.5 * 1440
    Me.OwnerID.ColumnWidth = 0.5 * 1440
    ' this will error if the host subform control shrinks to less than 1.5 inches.
End Sub
See how both approaches consume the horizontal space available as provided by the InsideWidth property of the form during its Resize event?
 

cosmarchy

Registered User.
Local time
Today, 00:15
Joined
Jan 19, 2010
Messages
116
I have a working prototype from which to expand...

Thanks for this guys:)
 

Users who are viewing this thread

Top Bottom