global checkboxes

Gary Samuels
Gary Samuels used Ask the Experts™
on
Is it possible to define a checkbox in Module1, then place the checkbox on form1 making the checkbox value a global variable that can be used on form2?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
It is probably better to leave Form1 (the one with the checkboxes) open but hidden, and refer directly to the checkboxes.  Otherwise you will have to manage 30+ global variables.

If what you are trying to do is allow the user to identify which fields to include in a query, I would recommend the multi-select list I mentioned in response to your previous question.  Would help if we knew what you were trying to do, rather than proposing a solution and asking us to help with that solution.
Gary SamuelsPlant Manager

Author

Commented:
Yes, I'm trying to develop a way that the user can identify which fields to include in a query. I did a quick look into the multi-select list and it told me I could not have over 20 items in the list?

My thought on the checkboxes was to have a default value of true on 6 of the most common fields. If the user did a search without opening the checkbox form these 6 fields would be included in the query. If the user wanted something different they would open the checkbox form, make their selection and run the query while the checkbox is open. When the checkbox is closed the query defaults back to the 6 common fields.
Most Valuable Expert 2014
Commented:
I wrote an article on an approach that might be much more elegant for what you are trying to do.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_6692-Overcoming-unbound-continuous-forms-limitations-by-staging-data.html
If you create a table that has two fields fldName (text 255)  and Include (boolean) it is easy enough to create a loop that will populate it with the field names from a given table.  
Next, a continuous form can be bound to that table.  
Just two controls (fldname and include) would be needed.
Once the user makes their selections, it's easy to build a SQL string from the table entries where Include=true.

Have a look at the article and its sample.
The code for pulling the field names from a TableDef object is an easy enough adaptation.
So is the code for building the string.

This is a LOT simpler than 30 global variables, setting them via checkboxes, and parsing them for True or False to build your SQL string.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Don't know where you looked concerning the multi-select list, but I can assure you it is not limited to 20 items.  Don't recall right off the top of my head what the limit is, but it is in the thousands, although that tends to get problematic.

I've attached a really simple Access 2003 database which will demonstrate the concept.
MultiList.mdb
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Actually, I've used the same technique recommended by Nick for a wide variety of projects.  

The advantage of his method (continuous subform with checkboxes) is that multi-select lists can be tempermental.  If you for get to hold down the ctrl or shift key, you can wipe out all of your previous selections.  With the continuous form displaying the fieldname and a checkbox,  you don't have that problem.
Most Valuable Expert 2014

Commented:
Other nice advantages are that
1. The choices get stored in a table, so they don't get wiped out by a run-time error,which can happen to variables.
2. There will only be the choices selected to build your string from.  No parsing out unselected choices.
3. Many fewer controls
4. Much simpler code
Gary SamuelsPlant Manager

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial