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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.