[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Hiding/Showing Subform Datasheet Columns based on Checkbox Selection

Posted on 2011-04-25
Medium Priority
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.
  • 2
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35462687
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

ID: 35462798
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

ID: 35463302
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 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 35463804
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

ID: 35464117
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.

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

834 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