Turning on Record Selectors moves control boxes to the right

Access 2010 forms. I normally turn record selectors off when designing continuous forms to prevent deletion of records.  When I turn them on in VBA for certain users all my column headings move right by the width of the record selector column and the mouse over area associated with each control also moves right. eg when clicking on a combo box the down arrow disappears and the mouse must click on the blank area immediately to the right of the combo box.  Any suggestions to solve this would be greatly appreciated
Ian JarvisRetiredAsked:
Who is Participating?
 
mbizupCommented:
Hi Ian,

This still definitely qualifies as a bug, but I've got a better/easier work-around for you.  Start with the record selectors ON rather than off, and use VBA in the form's Open Event to turn them off if you want them to initially be hidden.  Testing this again, it seems like this problem only occurs if the record selectors are turned off through the property sheet.  If the property is set to yes, you can toggle record selectors on and off through VBA, and the 'clickable area' stays fixed to the size of the controls.
0
 
mbizupCommented:
That seems to be a bug.  I thought that you might be able to use vba to offset the header controls to fix this, but it appears that the 'clickable area' of the controls also moves by that offset, and remains out of alignment with the controls.  

This is a workaround that will keep things aligned (but you may need to modify code associated with your combo boxes accordingly):

1.  Place your continuous form inside another form, so that it is a subform.
2.  Move your header controls, including the button, to the main form, immediately above the subform and align them with the columns of the subform.
3.  Revise the button code to refer to your subform code, and have it call a procedure to shift the location of the header controls when the recordselectors are enabled.  This is using default control names.  Adjust the code to use your own control names.

Private Sub Command10_Click()
    Dim dblOffset As Double
    dblOffset = 0.25 * 1440  ' 1440 is inches to twips conversion factor

    With Me.SubformControlName.Form
        .RecordSelectors = Not .RecordSelectors  ' Toggle record selectors on/off
        If .RecordSelectors = True Then AdjustHeaderControls (dblOffset) Else AdjustHeaderControls (-dblOffset)  ' Shift header controls left/right
    End With

End Sub

Sub AdjustHeaderControls(dblOffset As Double)
    Me.Label1.Left = Me.Label1.Left + dblOffset
    Me.Label2.Left = Me.Label2.Left + dblOffset
    Me.Label4.Left = Me.Label4.Left + dblOffset
    Me.Label6.Left = Me.Label6.Left + dblOffset
    Me.Label8.Left = Me.Label8.Left + dblOffset
    Me.Command10.Left = Me.Command10.Left + dblOffset    
End Sub

Open in new window


See the attached image for the general idea about the layout.Layout
0
 
mbizupCommented:
This is haphazardly thrown together, but here's the sample database I used for the info in my previous comment.
Database5.accdb
0
 
Ian JarvisRetiredAuthor Commented:
Thanks very much for your time and effort in responding.  I will try your workaround soon when I clear some other urgent work.  It is a shame that Microsoft cannot fix this bug.
0
 
Ian JarvisRetiredAuthor Commented:
Thanks again.  That is a far better solution. I will leave record selectors on then turn them off On Open for those users who should not see them. Regards Ian
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.