Hiding/Showing Subform Datasheet Columns based on Checkbox Selection

Posted on 2011-04-25
Last Modified: 2012-05-11
I've tried searching this site for the answer to this, and though I've found a couple similar, perhaps my search terms are not exact, because I'm not able to come up with much.

This is what I have so far:

Main form:  frm_AllAssetsMain
Subform (datasheet view) on main form:  AllAssetsSubform
Selection Form: frm_ColumnViewSelection

What I'm trying to accomplish.  User opens main form, all available columns are visible on subform.  User opens frm_ColumnViewSelection and selects via checkbox the columns that they wish displayed on AllAssetsSubform.  The state of those checkboxes is then saved and the form closed, and when the form closes, only the columns corresponding to the selected checkboxes on frm_ColumnViewSelection remain visible on AllAssetsSubform.  As long as the user does not go back into frm_ColumnViewSelection and change the selection, then for the rest of the users session, only those fields will be seen regardless of any requerying of the subform or whatnot.  The user, can however, at any time, go back into the frm_ColumnViewSelection and change the selection of what they view and the view will update accordingly.  when the database is closed and then reopened, however, I would like the view to be reset to the default state.


Selection DOES NOT have to be on separate form if that makes it much more difficult to accomplish.  I can list checkboxes on AllAssetsMain if I need to.

If more than one person is using db at same time, I need the selections that particular user selects to only be specific to THEIR session, and not changed globally, so that the second user suddenly loses columns because the first user changed the checkboxes selected in their session.

I am an extreme novice when it comes to Access and VBA, I've picked up a thing or two through trial and error and a lot of help on here, but please keep your responses as novice-like as possible.  Thank you.
    LVL 119

    Expert Comment

    by:Rey Obrero
    you can use check boxes or  have a list box that  list all the columns of the subform.

    let me know which option you want to use.

    upload a copy of the db with the form and subform and related objects
    LVL 16

    Expert Comment

    I have to admit that I am not clear on exactly what you are trying to achieve. But it seems that what you want to do is open a form and select a few checkbox and all the data after will be linked to the checkbox selection.

    You can achieve this by creating a global variable. Do this by creating a new module and in it type

    Global strCheckBox As String

    Then you can use the onclose event of the form to assign a value to  strCheckBox .

    Not sure what you data structure is so can't give specific code but I'd say you will generate some sql that you will loop through to create a string.

    Once you have that string you can use it for the rest of the session. And it would not affect other users

    Author Comment

    attached are both the Front and Back ends of the db. Instead of going with the separate form for checkbox selection,  I created the checkboxes on Frm_AllAssetsMain, figured it would be simpler for the user than having to open extra forms.

    the login to use would be:  UN:  richard
                                               PW:  2622 Hand-Receipt-Manager-be.accdb HR-Manager.accdb
    LVL 119

    Accepted Solution

    test this

    i just coded for several check boxes using the AfterUpdate event of the checkbox, just follow the coding for the other check boxes

    Author Closing Comment

    This did the trick, exactly as I was looking for, and allowed me to learn some more by looking at the code and figuring out how it worked so that in the future I can implement this on my own with other projects.  Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now