Resize sub-forms when scorllbar appears (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 10:46
Joined
Jul 10, 2019
Messages
277
Hi guys,

I was wondering if anyone has a nice easy solution to this problem I am facing.

I have a subform that when needed a scrollbar appears. I need to have the width of the subform wider to make room for when the scroll bar appears. The problem is that when the scrollbar is not needed, the width of the subform looks odd compared to subforms above and below that don't ever need a scrollbar.

I was hoping that I could do an If statement that if scrollbar is true then width of the box be bigger, and if scrollbar is false then make the width smaller. I don't even know if such property exist, thought someone out there would know how to approach this in a simple manner.
 

June7

AWF VIP
Local time
Today, 09:46
Joined
Mar 9, 2014
Messages
5,423
Why does scrollbar come and go? If subform width 'looks' odd compared to others, why would you want it to be wider than the others?

I am not aware of any property that indicates whether datasheet scrollbar is "on/off".
 

Lkwdmntr

Registered User.
Local time
Today, 10:46
Joined
Jul 10, 2019
Messages
277
When the subform has more the 7 or eight records the scrollbar appears and it needs to be wider then the other subforms or the data will be cut off. When there is only a few records in the subform, no scrollbar is there but the space where the scrollbar will be is there adding to the width of the subform and making the entire form look off. Looks like I'll have to do some kind of counter and change the width of the box according to how many records there are.
 

June7

AWF VIP
Local time
Today, 09:46
Joined
Mar 9, 2014
Messages
5,423
Ah, I get it now. It's the vertical scrollbar that uses up some width when it appears. If uniformity is important, maybe size all subforms to width needed when scrollbar is on and live with a little extra white space.
 

Lkwdmntr

Registered User.
Local time
Today, 10:46
Joined
Jul 10, 2019
Messages
277
I thought about doing that, but want to try to resize on the record size. I made a query called CountSAWED. I added code in the onload event and am getting a type mismatch error. Not sure what I did wrong. Just to see if it workes I am making the width smaller if more than 6 records.
Private Sub Form_Load()

Dim CountWED As Integer

DoCmd.OpenQuery "CountSAWED" = CountWED

If CountWED > 6 Then
Form_FRM_Wed_Challenges.Sub_WedSAChallenges.Width = 7.5521
End If

DoCmd.Close acQuery, "CountSAWED"


End Sub​
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:46
Joined
Oct 29, 2018
Messages
21,358
Hi. Assuming CountSAWED is the record source for the subform, there isno need to open and close the query. You can do something like this.

CountWED = DCount("*","CountSAWED")
 

Lkwdmntr

Registered User.
Local time
Today, 10:46
Joined
Jul 10, 2019
Messages
277
I took your advice, but it is still not working. No error, just not making the subform smaller.

Private Sub Form_Load()

Dim CountWED As Integer

CountWED = DCount("*", "CountSAWED")

If CountWED > 6 Then
Form_FRM_Wed_Challenges.Sub_WedSAChallenges.Width = 7.5521
End If

DoCmd.Close acQuery, "CountSAWED"


End Sub​
 

Lkwdmntr

Registered User.
Local time
Today, 10:46
Joined
Jul 10, 2019
Messages
277
I stoped the code when loading and the CountWED variable is only at 1. It should be 22 if it is getting the number from the CountSAWED query.
 

Micron

AWF VIP
Local time
Today, 13:46
Joined
Oct 20, 2018
Messages
3,476
You need to use the MoveSize method of the DoCmd object and specify a number in twips:

Docmd.MoveSize Right, Down, Width, Height or

Docmd.MoveSize ,,5000
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:46
Joined
Oct 29, 2018
Messages
21,358
I stoped the code when loading and the CountWED variable is only at 1. It should be 22 if it is getting the number from the CountSAWED query.

Hi. You didn't get an error when you try to close the query that is not open using DoCmd.Close? If you open the query manually, how many records do you see? Then, if you enter the DCount() expression in the Immediate Window, what do you get?
 

Lkwdmntr

Registered User.
Local time
Today, 10:46
Joined
Jul 10, 2019
Messages
277
Ok, so I got it to work, but ran into another problem. I am using the same subform in two separate forms. One form is Form_FRM_Wed_Challenges and the other is Form_FRM_Wed_Challenges1. How do I use the code to work on both forms? This is what I tried, but kinda knew it was a shot in the dark. I was trying to find a way to which form I'm on then run the proper code. By the way, the DCount is working great. I don't want to change that.

Dim CountWED As Integer

CountWED = DCount("Wednesday", "Weekly_Challenges", "Weekly_Challenges.UserID = [TempVars]![TmpUserID] And Weekly_Challenges.WeekNumber = [TempVars]![TmpWeekNumber] And Weekly_Challenges.Wednesday <> 'T' And Weekly_Challenges.Index > 300 And Weekly_Challenges.Index < 400")


If Me.Form = Form_FRM_Wed_Challenges Then
If CountWED > 6 Then
Form_FRM_Wed_Challenges.Sub_WedSAChallenges.Width = 10875
Else
Form_FRM_Wed_Challenges.Sub_WedSAChallenges.Width = 11040
End If

Else
If CountWED > 6 Then
Form_FRM_Wed_Challenges1.Sub_WedSAChallenges.Width = 10875
Else
Form_FRM_Wed_Challenges1.Sub_WedSAChallenges.Width = 11040
End If
 

Micron

AWF VIP
Local time
Today, 13:46
Joined
Oct 20, 2018
Messages
3,476
I'm confused by that. If you're using Me. it can only be on the form Me applies to, thus the form name is known, thus there's no reason to test for it. If you're using it in a standard module, you cannot use Me to refer to any form. Youy need an explicit forms reference such as Forms!frmNameHere.

You could put the code in a separate module so that any form you want to use it on can call the procedure, otherwise you'd have to repeat code in every form it will apply to and alter the settings accordingly. If reusable, you would call it in the opening form's Load event such as
ResizeForm Me.Name
where that sub might look like

Code:
Public Sub ResizeForm(strName As String)
Select Case strName
    Case "Form1"
       make one size

    Case "Form2" 
       make another size
    etc.
 End Select
End Sub
Whatever you do, pretty sure you cannot use Me.Form= "some name" because Name is not the default property of a form.

EDIT - I should add that if referring to a subform from some other place you will need to know the syntax for that
Forms![Main form name]![subform control name].Form
 
Last edited:

Users who are viewing this thread

Top Bottom