What I have is 1 main form (let's say: MF) and 2 subforms (say SF1 and SF2). Both are to be positioned beneath the fields of MF.
SF1 is always present but can have varying number of records (i.e. its height will not remain constant).
SF2 is optionally present, depending on checkbox in MF and will appear beneath SF1.
I have this working by setting first:- SF1.Height to be number of records * height of row + 1 height of row (for new record entry) and then, second: setting SF2.Top to be SF1.Top + SF1.Height.
This does work well enough, but...
If any access is made to SF1 (even a click with no actual entry) and the MF is then changed to a different record, the whole of MF (incl SF's) shifts up about a centimetre with the top fields disappearing into (behind) the Form's Header (and my code results in a vertical scroll bar appearing where none is ever needed).
I have tried just about all possible variables of AutoSize, Can Grow, Can shrink etc for MF and SF's but nothing has any effect.
I have put in a dummy field, at the very top of MF, to take focus on Current which either prevents or recovers from the abnormal shift, but i'm not happy knowing there's a problem still there.
Below is my code. My Access (and certainly my VBA) knowledge is very weak so would appreciate any insight from those more experienced...
Private Sub Form_Current()
Me!TxtDummy.SetFocus ''''This is to counter the unexplained shift up of form after accessing sForm_OTbyDay ?!!
'Show/Hide Transport subform as required - by record's Transport Y/N value
If (Me!CheckTransport = -1) Then
Me!sFormTransport.Visible = True
Else
Me!sFormTransport.Visible = False
End If
'''''''''''''''''
'Change position of sFormTransport as sForm_OTbyDay resizes
''' 1cm = 567twips
Dim rowsCount As Integer 'Number of rows in OTDaybyDay subForm
Dim yRowHeight As Integer 'Row height in Twips of OTDaybyDay subForm
Dim headerHeight As Integer 'Header height of OTDaybyDay subForm
Dim sf1Top 'Vertical position of 1st subform
Dim sf2Top 'Vertical position of 2nd subform
yRowHeight = 450
headerHeight = 330
rowsCount = Me.Controls("sForm_OTbyDay").Form.Recordset.RecordCount
sf1Top = Me![sForm_OTbyDay].Top
Me![sForm_OTbyDay].Height = (sf1Top + headerHeight) + (rowsCount * yRowHeight) + yRowHeight
If (Me!sFormTransport.Visible = True) Then
Me![sFormTransport].Top = Me![sForm_OTbyDay].Height
End If
End Sub
Glenn
SF1 is always present but can have varying number of records (i.e. its height will not remain constant).
SF2 is optionally present, depending on checkbox in MF and will appear beneath SF1.
I have this working by setting first:- SF1.Height to be number of records * height of row + 1 height of row (for new record entry) and then, second: setting SF2.Top to be SF1.Top + SF1.Height.
This does work well enough, but...
If any access is made to SF1 (even a click with no actual entry) and the MF is then changed to a different record, the whole of MF (incl SF's) shifts up about a centimetre with the top fields disappearing into (behind) the Form's Header (and my code results in a vertical scroll bar appearing where none is ever needed).
I have tried just about all possible variables of AutoSize, Can Grow, Can shrink etc for MF and SF's but nothing has any effect.
I have put in a dummy field, at the very top of MF, to take focus on Current which either prevents or recovers from the abnormal shift, but i'm not happy knowing there's a problem still there.
Below is my code. My Access (and certainly my VBA) knowledge is very weak so would appreciate any insight from those more experienced...
Private Sub Form_Current()
Me!TxtDummy.SetFocus ''''This is to counter the unexplained shift up of form after accessing sForm_OTbyDay ?!!
'Show/Hide Transport subform as required - by record's Transport Y/N value
If (Me!CheckTransport = -1) Then
Me!sFormTransport.Visible = True
Else
Me!sFormTransport.Visible = False
End If
'''''''''''''''''
'Change position of sFormTransport as sForm_OTbyDay resizes
''' 1cm = 567twips
Dim rowsCount As Integer 'Number of rows in OTDaybyDay subForm
Dim yRowHeight As Integer 'Row height in Twips of OTDaybyDay subForm
Dim headerHeight As Integer 'Header height of OTDaybyDay subForm
Dim sf1Top 'Vertical position of 1st subform
Dim sf2Top 'Vertical position of 2nd subform
yRowHeight = 450
headerHeight = 330
rowsCount = Me.Controls("sForm_OTbyDay").Form.Recordset.RecordCount
sf1Top = Me![sForm_OTbyDay].Top
Me![sForm_OTbyDay].Height = (sf1Top + headerHeight) + (rowsCount * yRowHeight) + yRowHeight
If (Me!sFormTransport.Visible = True) Then
Me![sFormTransport].Top = Me![sForm_OTbyDay].Height
End If
End Sub
Glenn
