Subform grow/shrink

[kirkm]

Hi,

Been playing with the Can Grow/Shrink settings without much luck. Do they work as the names suggest?
I get scrollbars, v & h, without any resizing of the subform.

Thanks - Kirk

 

[ShaneMan]

Hi,

Been playing with the Can Grow/Shrink settings without much luck. Do they work as the names suggest?
I get scrollbars, v & h, without any resizing of the subform.

Thanks - Kirk

Hello Kirk,

This is from Access help:
You can use the CanGrow and CanShrink properties to control the appearance of sections or controls on forms and reports that are printed or previewed. For example, if you set both properties to Yes, a section or control automatically adjusts vertically to print or preview all the data the section or control contains.

Notes

The CanGrow and CanShrink properties don't apply to form or report page header and page footer sections, although they do apply to controls in such sections.


These properties affect the display of form sections and controls only when the form is printed or previewed, not when the form is displayed in Form view, Datasheet view, or Design view.


You can hit F1 while on "can grow" to read the rest. You could play with the Height and Width properties if you know ahead of time what size you are wanting it to grow to.

HTH,
Shane

 

[kirkm]

Hi Shane,

Thanks... I ignored 'print' and didn't know what 'preview' meant! Looks like can grow/shrink isn't the way to go.

I've tried to set a new height depending on the number of rows required (between 4 and 8) but ran into a wee problem. This is a subform and the problem line is :-

Forms!frmTracks.Height = "1.8cm"

Invokes this error :
Run-time error '2450':
Microsoft Access can't find the form 'frmTracks' referred to in a macro expression or Visual Basic code. * The form you referenced may be closed or may not exist in this database.

I don't think it's closed... is there a different syntax for setting a sub form property ?

Thanks - Kirk

 

[ShaneMan]

Hi Shane,

Thanks... I ignored 'print' and didn't know what 'preview' meant! Looks like can grow/shrink isn't the way to go.

I've tried to set a new height depending on the number of rows required (between 4 and 8) but ran into a wee problem. This is a subform and the problem line is :-

Forms!frmTracks.Height = "1.8cm"

Invokes this error :
Run-time error '2450':
Microsoft Access can't find the form 'frmTracks' referred to in a macro expression or Visual Basic code. * The form you referenced may be closed or may not exist in this database.

I don't think it's closed... is there a different syntax for setting a sub form property ?

Thanks - Kirk

I'm going to assume frmTracks is your subform and if my assumption is right then try this instead: Forms!frmTracks.Form.Height

HTH,
Shane

 

[kirkm]

Hi Shane,

Yes frmTracks is the subform name. The properties are

_ Subform/Subreport: frmTracks
All
Name . . . . . . . . . . . frmTracks
Source Object . . . . . frmTracks
Link Child Fields . . . . Cat
Link Master Fields . . . Cat
Status Bar Text . . . .
Visible . . . . . . . . . . . Yes
Display When . . . . . . Always
Enabled . . . . . . . . . . Yes
Locked . . . . . . . . . . No
Tab Stop . . . . . . . . . Yes
Tab Index . . . . . . . . 6
Can Grow . . . . . . . . Yes
Can Shrink . . . . . . . . Yes
Left . . . . . . . . . . . . 0.989cm
Top . . . . . . . . . . . . 5.998cm
Width . . . . . . . . . . . 13.199cm
Height . . . . . . . . . . . 3.6cm
Special Effect . . . . . . Sunken
Border Style . . . . . . . Solid
Border Width . . . . . . Hairline
Border Color . . . . . . 0
Tag . . . . . . . . . . . .
On Enter . . . . . . . . .
On Exit . . . . . . . . . .

I've tried Forms!frmTracks.Form.Height but it brings up the same error.

Can you suggest anything else?

Thanks - Kirk

 

[ShaneMan]

Hi Shane,

Yes frmTracks is the subform name. The properties are

_ Subform/Subreport: frmTracks
All
Name . . . . . . . . . . . frmTracks
Source Object . . . . . frmTracks
Link Child Fields . . . . Cat
Link Master Fields . . . Cat
Status Bar Text . . . .
Visible . . . . . . . . . . . Yes
Display When . . . . . . Always
Enabled . . . . . . . . . . Yes
Locked . . . . . . . . . . No
Tab Stop . . . . . . . . . Yes
Tab Index . . . . . . . . 6
Can Grow . . . . . . . . Yes
Can Shrink . . . . . . . . Yes
Left . . . . . . . . . . . . 0.989cm
Top . . . . . . . . . . . . 5.998cm
Width . . . . . . . . . . . 13.199cm
Height . . . . . . . . . . . 3.6cm
Special Effect . . . . . . Sunken
Border Style . . . . . . . Solid
Border Width . . . . . . Hairline
Border Color . . . . . . 0
Tag . . . . . . . . . . . .
On Enter . . . . . . . . .
On Exit . . . . . . . . . .

I've tried Forms!frmTracks.Form.Height but it brings up the same error.

Can you suggest anything else?

Thanks - Kirk

Where are you calling this code from? The main form? BTW, side note, you did put the height you wanted for your subform didn't you?

 

[kirkm]

Yes - from the 'Current' event, like so
--
Private Sub Form_Current()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()

Dim sql$
sql$ = "SELECT Tracks.Cat FROM Tracks WHERE (((Tracks.Cat)="
sql$ = sql$ & Chr$(34) & Me.Text1 & Chr$(34) & "));"
Set rst = db.OpenRecordset(sql$)
rst.MoveLast
Select Case rst.RecordCount
Case 4
'Forms!frmTracks.Height = "1.8cm"
Case 8
Forms!frmTracks.Form.Height = "1.8cm"
Case Else
Stop
End Select

rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub
--

 

[ShaneMan]

Yes - from the 'Current' event, like so
--
Private Sub Form_Current()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()

Dim sql$
sql$ = "SELECT Tracks.Cat FROM Tracks WHERE (((Tracks.Cat)="
sql$ = sql$ & Chr$(34) & Me.Text1 & Chr$(34) & "));"
Set rst = db.OpenRecordset(sql$)
rst.MoveLast
Select Case rst.RecordCount
Case 4
'Forms!frmTracks.Height = "1.8cm"
Case 8
Forms!frmTracks.Form.Height = "1.8cm"
Case Else
Stop
End Select

rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub
--

Ok, let me try answering this way. If the above code is on the main forms (Parent form) On Current then try it this way:
Forms!ParentFormName!subFormName.Form.Height = EnterHeight

Of course you need to replace the parent form and subform names with your forms names.

See what that does for you,
Shane

 

[kirkm]

Hi Shane,

Still in the woods. I think the value should be in twips, not "1.8cm" so I used this syntax:

Forms!Form1!frmTracks.Form.Height = 1440

which gave me

Run-time error '2465':
Application-defined or object-defined error

And I can't quite figure (from Help) what the error means.

Might just have to leave it a fixed Height. Relunctantly

Cheers - Kirk

 

[ShaneMan]

Hi Shane,

Still in the woods. I think the value should be in twips, not "1.8cm" so I used this syntax:

Forms!Form1!frmTracks.Form.Height = 1440

which gave me

Run-time error '2465':
Application-defined or object-defined error

And I can't quite figure (from Help) what the error means.

Might just have to leave it a fixed Height. Relunctantly

Cheers - Kirk

Kirk,

I just tried changing the height on one of my subforms and everything worked fine. I think you have a name of a form wrong or something is misspelled. Be sure you are calling your code from the main form.

You could also try: Me.frmTracks.Form.Height = whatever (be sure frmTracks is the name of your subform)

I have my subforms in inches and have never done cm so I don't know if that would change things or not.

Shane

 

[kirkm]

Hi Shane,

We're there! Solved. But very odd... the syntax that works is just
frmTracks.Height =

Anything else causes an error. I got these by adding a temp button that showed the current height via a msgbox, then just tried a zillion things until it worked.

Thanks a million for all the help.
Cheers - Kirk

 

[ShaneMan]

Hi Shane,

We're there! Solved. But very odd... the syntax that works is just
frmTracks.Height =

Anything else causes an error. I got these by adding a temp button that showed the current height via a msgbox, then just tried a zillion things until it worked.

Thanks a million for all the help.
Cheers - Kirk

Your right, that is odd. Glad you have it going and your welcome for the help.

Good luck the rest of the way on your project,
Shane