No Verticle Scroll Bar

[SteveApa]

Hey... Weird. I have a datasheet subform. I have properties set for scroll bars = both on the subform. But, The subform will not show the vertical scroll bar unless I "navigate" to the 1st row that is not showing, then the vertical scroll bar shows.



Wierder.. Unless I set a variable in the subform "Form_Current", Me.Scrollbars = 1! 1=Horizontal only... 3 is both. But setting to 3 only shows horizontal. Setting to 1 shows both.

Any help would be great!

Thanks
Steve
Harrisburg, PA USA

[SteveApa]

I just found this...
In the subform's "Form_Current", I set some subform form properties based on security... edit, delete, additions, and I set some data element access. If I log in, EWS_GLOBAL_ADMIN is set to true since I wrote it and I am the admin and I want to be able to do anything! If I force EWS_GLOBAL_ADMIN to false, the vertical scroll bar shows, but set as true it does not show???

Private sub Form_Current()
     Call Lock_Or_Unlock_Selected_Element_By_Phase
' the below is to display the security settings on the main form so I know how they are set
     Forms![ews 50 4b) Main Form - Server]![g_element_delete] = Me.AllowDeletions     Forms![ews 50 4b) Main Form - Server]![g_element_edit] = Me.AllowEdits
     Forms![ews 50 4b) Main Form - Server]![g_element_insert] = Me.AllowAdditions
end sub

Private Sub Lock_Or_Unlock_Selected_Element_By_Phase()
       
    If EWS_GLOBAL_ADMIN Then
        'form overall
        Me.AllowDeletions = True
        Me.AllowEdits = True
        Me.AllowAdditions = True
    
        'data controls
        Me.Phase_of_Work.Locked = False
        Me.Element_of_Work.Locked = False
        Me.Value__Perct_.Locked = False
        Me.ECD.Locked = False
        Me.Started.Locked = False
        Me.Finished.Locked = False
        Me.OwnerCombo.Locked = False
    End If
' there is alot more code below this if EWS_GLOBAL_ADMIN = false.. based on login, etc.  Nothing below should effect the vertical scroll bar

With EWS_GLOBAL_ADMIN set to false, I do not need the me.scrollbars = 1 as described in the above thread

[mike60smart]

Hi Steve

The Vertical scrollbar will only be displayed when the number of records in the subform extend
past the vertical height of the Subform.

[SteveApa]

I have 90 rows.... only 6 showing.
Code correction.. I did not show setting the me.scrollbars...

Private sub Form_Current()
     Call Lock_Or_Unlock_Selected_Element_By_Phase
     me.ScrollBars = 1 ' 1 is horizontal only... this should be 3 for both but 3 does not work and 1 works
' the below is to display the security settings on the main form so I know how they are set
     Forms![ews 50 4b) Main Form - Server]![g_element_delete] = Me.AllowDeletions
     Forms![ews 50 4b) Main Form - Server]![g_element_edit] = Me.AllowEdits
     Forms![ews 50 4b) Main Form - Server]![g_element_insert] = Me.AllowAdditionsend sub

[Micron]

I have read that this can happen if everything is in the header or if Windows dpi setting is too high. Pics of your forms in design (including relevant section of property sheet) and form view might help. Note, you cannot just paste a pic in your post; use 'insert image' control on posting toolbar.

[SteveApa]

Thanks... checked it. Not in the suform header.. in detail

[SteveApa]

Dont' know why this function is causing scrollbar problems, but here is what I found. If I set a global variable on the cmd button to open the main form / subform, I set the global to:

EWS_ELEMENT_FIRST_TIME_IN = TRUE

Then in the subform Form_Current, I don't run the security on the 1st time in, but run it every time after that and the scroll bars work as they should

Private Sub Form_Current()    
    'set security based on Admin, Creator, Responsible person
    Call EWS_Set_Form_Security("Element", Nz(Me.CreatorNID), Nz(Me.OwnerCombo)) '  nz because insert new line and no owner combo


    'set Lock based on phase / EWS Override of Lock
    If EWS_ELEMENT_FIRST_TIME_IN = False Then
        Call Lock_Or_Unlock_Selected_Element_By_Phase
    End If
    EWS_ELEMENT_FIRST_TIME_IN = False
    Forms![ews 50 4b) Main Form - Server]![g_element_delete] = Me.AllowDeletions
    Forms![ews 50 4b) Main Form - Server]![g_element_edit] = Me.AllowEdits
    Forms![ews 50 4b) Main Form - Server]![g_element_insert] = Me.AllowAdditions
    Display_Sub_Locked_Elements
End Sub

Here is the routine that sets form access and data element access based on status of project. THis is the one that If I don't run on the 1st time in, scroll bars work. Nothing in the function about scroll bars, just access settings to the form based on "Role", admin, owner, responsible person... and phase of project. All Caps are GLOBAL variables. This is called from the Form_Current of the subform that has the scroll bar problem

Private Sub Lock_Or_Unlock_Selected_Element_By_Phase()
    
    'EWS_FORM_SECURITY_SETTINGS_ELEMENTS are already defined based on Admin, Creator, Responsible person
    
    If EWS_GLOBAL_ADMIN Then
        'form overall
        Me.AllowDeletions = True
        Me.AllowEdits = True
        Me.AllowAdditions = True
   
        'data controls
        Me.Phase_of_Work.Locked = False
        Me.Element_of_Work.Locked = False
        Me.Value__Perct_.Locked = False
        Me.ECD.Locked = False
        Me.Started.Locked = False
        Me.Finished.Locked = False
        Me.OwnerCombo.Locked = False
        'set based on owner / creator and user logged in
        Forms![ews 50 4b) Main Form - Server]![ews_element_status] = "Admin"
        Exit Sub
    End If
    
    'if test project
    If IsNull(EWS_PROJECT_CURRENT_PHASE) Then
        'form overall
        Me.AllowDeletions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.deleteable
        Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
        Me.AllowAdditions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.insertable
        
        'data controls
        Me.Phase_of_Work.Locked = False
        Me.Element_of_Work.Locked = False
        Me.Value__Perct_.Locked = False
        Me.ECD.Locked = False
        Me.Started.Locked = False
        Me.Finished.Locked = False
        Me.OwnerCombo.Locked = False
        
        Forms![ews 50 4b) Main Form - Server]![ews_element_status] = "Test Project"
        Exit Sub
    End If
    
    'if pre G2, allow edit / insert / delete all
    
    If EWS_PROJECT_CURRENT_PHASE = "Concept" Or EWS_PROJECT_CURRENT_PHASE = "Definition" _
        Or EWS_PROJECT_CURRENT_PHASE = "G0" Or EWS_PROJECT_CURRENT_PHASE = "G1" Or EWS_PROJECT_CURRENT_PHASE = "G2" Then
        
        Me.AllowDeletions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.deleteable
        Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
        Me.AllowAdditions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.insertable
        
        'if project manager, edit all
        If EWS_USER_SECURITY.PROJECT_OWNER = True Then
            'data controls
            Me.Phase_of_Work.Locked = False
            Me.Element_of_Work.Locked = False
            Me.Value__Perct_.Locked = False
            Me.ECD.Locked = False
            Me.Started.Locked = False
            Me.Finished.Locked = False
            Me.OwnerCombo.Locked = False
        Else ' if responsible person, only edit started or finished
            'data controls
            Me.Phase_of_Work.Locked = True
            Me.Element_of_Work.Locked = True
            Me.Value__Perct_.Locked = True
            Me.ECD.Locked = True
            Me.Started.Locked = False
            Me.Finished.Locked = False
            Me.OwnerCombo.Locked = False
        End If
        Forms![ews 50 4b) Main Form - Server]![ews_element_status] = "Phase Allowed"
        Exit Sub
    End If
        
    ' lock everything here after G2, but start and finish... then adjust for special cases later
    If EWS_PROJECT_CURRENT_PHASE = "Design" Or EWS_PROJECT_CURRENT_PHASE = "Validation" Or _
            EWS_PROJECT_CURRENT_PHASE = "Industrialize" Or EWS_PROJECT_CURRENT_PHASE = "Production" Or _
            EWS_PROJECT_CURRENT_PHASE = "G3" Or EWS_PROJECT_CURRENT_PHASE = "G4" Or EWS_PROJECT_CURRENT_PHASE = "G5" Then
            
        Me.AllowDeletions = False
        Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
        Me.AllowAdditions = False
        
        'if started or finished is checked, allow 24hrs to edit it
        If Me.Started = False Then
            Me.Started.Locked = False
        ElseIf DateAdd("d", 1, Me.Date_Started_Updated) > Now() Then
            Me.Started.Locked = False
        Else
            Me.Started.Locked = True
        End If
        
        If Me.Finished = False Then
            Me.Finished.Locked = False
        ElseIf DateAdd("d", 1, Me.Date_Finished_Updated) > Now() Then
            Me.Finished.Locked = False
        Else
            Me.Finished.Locked = True
        End If
        ' lock everything else
        Me.Phase_of_Work.Locked = True
        Me.Element_of_Work.Locked = True
        Me.Value__Perct_.Locked = True
        Me.ECD.Locked = True
        Me.OwnerCombo.Locked = False
        Forms![ews 50 4b) Main Form - Server]![ews_element_status] = "Phase Locked"
    End If
    
    'adjust for special cases if applicable
    'if after g3 and within 2 weeks of g3 gate, allow editing of all non started / finished elements
    'override permissions given above
    'only project owner can edit all... owner of element can update start or finish
    If EWS_PROJECT_CURRENT_PHASE = "Validation" Then
        'if within 2 weeks of g3 allow editing non started or finished elements
        If DateAdd("d", 14, EWS_MAX_GATE_DATE) > Now() Then
            If EWS_USER_SECURITY.PROJECT_OWNER = True Then
                If Me.Started Or Me.Finished Then
                    'cannot delete if started or finished
                    Me.AllowDeletions = False
                Else
                    Me.AllowDeletions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.deleteable
                End If
                Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
                Me.AllowAdditions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.insertable
                Me.Phase_of_Work.Locked = False
                Me.Element_of_Work.Locked = False
                Me.Value__Perct_.Locked = False
                Me.ECD.Locked = False
                Me.Started.Locked = False
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            Else
                Me.AllowDeletions = False
                Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
                Me.AllowAdditions = False
                
                Me.Phase_of_Work.Locked = True
                Me.Element_of_Work.Locked = True
                Me.Value__Perct_.Locked = True
                Me.ECD.Locked = True
                Me.Started.Locked = False
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            End If
        
            'if finished then it cannot be edited unless it was checked < 1 day ago
            If Me.Finished = True Then
                If DateAdd("d", 1, Me.Date_Finished_Updated) > Now() Then
                    Me.Finished.Locked = False
                Else
                    Me.AllowDeletions = False
                    Me.AllowEdits = False
                    Me.AllowAdditions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.insertable
                End If
            End If
            
            'if only started then it can be edited for ecd and finished unless < 1 day
            If Me.Started = True Then
                If DateAdd("d", 1, Me.Date_Started_Updated) > Now() Then
                    Me.Started.Locked = False
                Else
                    Me.AllowDeletions = False
                    Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
                    Me.AllowAdditions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.insertable
                    
                    Me.Phase_of_Work.Locked = True
                    Me.Element_of_Work.Locked = True
                    Me.Value__Perct_.Locked = True
                    Me.ECD.Locked = False
                    Me.Started.Locked = True
                    Me.Finished.Locked = False
                    Me.OwnerCombo.Locked = False
                End If
            End If
            Forms![ews 50 4b) Main Form - Server]![ews_element_status] = "Phase 2 Weeks Open"
        End If
    End If


    'EWS Lookup for 14day unlock override based on meeting minutes
    If G2G3_LOCK_OVERRIDE_DATE <> Empty Then
         'if within 2 weeks of override date allow editing non started or finished elements
        If DateAdd("d", 14, G2G3_LOCK_OVERRIDE_DATE) > Now() Then
            'FORM security
            'if project owner
            If EWS_USER_SECURITY.PROJECT_OWNER = True Then
                If Me.Started Or Me.Finished Then
                    'cannot delete if started or finished
                    Me.AllowDeletions = False
                Else
                    Me.AllowDeletions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.deleteable
                End If
            Else
                'if repsonisble person
                Me.AllowDeletions = False
                Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
                Me.AllowAdditions = False
            End If
            Forms![ews 50 4b) Main Form - Server]![ews_element_status] = "Lock Override 2wks"
            'data element security
            If Me.Started = True Then
                Me.Phase_of_Work.Locked = True
                Me.Element_of_Work.Locked = True
                Me.Value__Perct_.Locked = True
                Me.ECD.Locked = False
                Me.Started.Locked = True
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            Else
                Me.Phase_of_Work.Locked = False
                Me.Element_of_Work.Locked = False
                Me.Value__Perct_.Locked = False
                Me.ECD.Locked = False
                Me.Started.Locked = False
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            End If
                
            If Me.Finished = True Then
                Me.Phase_of_Work.Locked = True
                Me.Element_of_Work.Locked = True
                Me.Value__Perct_.Locked = True
                Me.ECD.Locked = True
                Me.Started.Locked = True
                Me.Finished.Locked = True
                Me.OwnerCombo.Locked = True
            Else
                Me.Phase_of_Work.Locked = True
                Me.Element_of_Work.Locked = True
                Me.Value__Perct_.Locked = True
                Me.ECD.Locked = False
                Me.Started.Locked = True
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            End If
        End If
    End If
    If G3G5_LOCK_OVERRIDE_DATE <> Empty Then
        If DateAdd("d", 14, G3G5_LOCK_OVERRIDE_DATE) > Now() Then
            'form security
            If EWS_USER_SECURITY.PROJECT_OWNER = True Then
                If Me.Started Or Me.Finished Then
                    'cannot delete if started or finished
                    Me.AllowDeletions = False
                    Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
                    Me.AllowAdditions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.insertable
                Else
                    Me.AllowDeletions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.deleteable
                    Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
                    Me.AllowAdditions = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.insertable
                End If
            Else
                'if repsonisble person
                Me.AllowDeletions = False
                Me.AllowEdits = EWS_FORM_SECURITY_SETTINGS_ELEMENTS.editable
                Me.AllowAdditions = False
            End If
            Forms![ews 50 4b) Main Form - Server]![ews_element_status] = "Lock Override 2wks"
            
            'element security
            If Me.Started = True Then
                Me.Phase_of_Work.Locked = True
                Me.Element_of_Work.Locked = True
                Me.Value__Perct_.Locked = True
                Me.ECD.Locked = False
                Me.Started.Locked = True
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            Else
                Me.Phase_of_Work.Locked = False
                Me.Element_of_Work.Locked = False
                Me.Value__Perct_.Locked = False
                Me.ECD.Locked = False
                Me.Started.Locked = False
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            End If
                
            If Me.Finished = True Then
                Me.Phase_of_Work.Locked = True
                Me.Element_of_Work.Locked = True
                Me.Value__Perct_.Locked = True
                Me.ECD.Locked = True
                Me.Started.Locked = True
                Me.Finished.Locked = True
                Me.OwnerCombo.Locked = True
            Else
                Me.Phase_of_Work.Locked = True
                Me.Element_of_Work.Locked = True
                Me.Value__Perct_.Locked = True
                Me.ECD.Locked = False
                Me.Started.Locked = True
                Me.Finished.Locked = False
                Me.OwnerCombo.Locked = False
            End If
        End If
    End If
End Sub

[SteveApa]

Work around...
I set a variable for "First Time in" in subform's Form_Current, and apply it once the main form opens. Subform is datasheet view.

1 - Subform Form_Current: 1st time thru, I do not run the subform security routine, but every time after that I do.
2 - I apply the subfrom security routine after the main form has opened. This only runs the 1st time the form / subform opens

note: all CAPS = global variable

Main Form Opening on cmd button click:

Private Sub EarlyWarningBtn_Click()
    EWS_ELEMENT_FIRST_TIME_IN = True
    DoCmd.OpenForm "ews 50 4b) Main Form - Server" ' subform with scroll bar issue on this main form
    Lock_Or_Unlock_Selected_Element_By_Phase EWS_MAIN_ELEMENT_FORM ' this set security on 1st open of main / subform
	'set security (edit / delete / insert and data element, .enabled, based on login
	'of subform
End Sub


Private Sub Form_Current() ' current of subform
    If EWS_MAIN_ELEMENT_FORM Is Nothing Then
        Set EWS_MAIN_ELEMENT_FORM = Me
    End If
    If EWS_ELEMENT_FIRST_TIME_IN = False Then ' must not run funciton below on 1st time thru because it messes up the scroll bars
                                                ' Solution is not run 1st time thru, but run it once main form is opened.
                                                ' then this one runs on each current
        Lock_Or_Unlock_Selected_Element_By_Phase EWS_MAIN_ELEMENT_FORM
    End If
    EWS_ELEMENT_FIRST_TIME_IN = False    
    Display_Sub_Locked_Elements EWS_MAIN_ELEMENT_FORM
End Sub

[SteveApa]

It was still intermittent. I had to add in the btn_click event that opens the main form this code... now always works

    DoCmd.OpenForm "ews 50 4b) Main Form - Server"
    Lock_Or_Unlock_Selected_Element_By_Phase EWS_MAIN_ELEMENT_FORM
    Display_Sub_Locked_Elements EWS_MAIN_ELEMENT_FORM
    EWS_MAIN_ELEMENT_FORM.Recordset.MoveLast ' this is here to get the verticle scroll working on element sub form
    EWS_MAIN_ELEMENT_FORM.Recordset.MoveFirst ' this is here to get the verticle scroll working on element sub form

the movelast, movefirst is what fixed it always