Clearing Checkboxes

[Teri]

I have a personal database which I originally downloaded from Microsoft.com
as a template for recipes. I have added a form to it which lists items other
than those found in my recipes that I might need to pick up from the store
when I get groceries (ie-Kitty litter, dog food, papertowels, soda, cookies,
etc). These items (there are many) are hard coded in my form with the
following fields: Aisle number (this would reflect the aisle number of the
store where I normally shop that the item can be found), checkboxes
indicating if the item is on sale, I have a coupon and whether or not I need
it, I also have a combo box which contains the names of the individuals in my
family so that if there is something I need to pick up just for one person, I
remember who it is for so that I get the right brand/scent/flavor (deoderant
for example, my husband will only use one brand and insists on the same scent
each time). I originally started out with only one question, now I have two).

1. Is there a way I can clear all the checkboxes (a command button maybe)
when I am ready to start a new list? Would need to clear the boxes for sale,
coupon, and whether or not I need it. I would also like to be able to clear
the combo box at the same time.

2 Second question, is there a way that I can set the combo box up to let me
pick two names out of it at once? (say both my husband and my daughter need
deoderant, but my son and I do not, I would want to choose my husband's name
and my daughter's name to appear on my report/grocery list).

Thanks so much!

Teri.

 

[Klatuu]

Since I am not sure how your form is set up to start a new list, it would be
hard to give the best answer.
One option is to set the default values of the check boxes to False in
design view of your form.
Another would be to use VBA in your form's Current event to set the check
boxes to False:

If Me.NewRecord Then
Me.MyCheckBox1 = False
Me.MyCheckBox2 = False
End if

As to the Combo box. You cannot make multiple selections using a combo box.
For that, you have to use a List Box control with the Multi Select property
set to Simple or Extended. This will cause you to have to make some changes
to how your system works, because you can't just use the value of the List
box like you do a Combo. For a List Box, you have to use the ItemsSelected
property to know which selections have been made.

 

[Ron2006]

Setting all of the check boxes off is easiest.

create an update query that sets all of those checkboxes to false and
run the query from a button
(or put the query in a macro and run the macro from a button.)
And you can also set the combo box field to "" or null in that same
query.

Without major changes in your tables perhaps the best way to create
combinations (if there are not too many people in the family) is to
simply create the combinations in the data that is used as the source
for your combobox. (If you are using a table, which is the better way,
simply add another record with "Don and Jane" and "Don and Bill" and
"Bill and Jane" etc. all the viable combinations.

This is NOT the best normalized method, BUT it will work and after the
kids leave you can take those combinations out. To normalize it now
would most probably take more time and effort than it was worth. Now if
you end up with more than 5 people, then you may want to think about
normalizing it AND re-designing it AND all of the reports that print
out the information.

 

[Klatuu]

Sorry, Ron, but that is about the ugliest thing I have ever seen.

 

[Ron2006]

ok, but I am not sure that your solution answers her question. Once she
sets up the list and prints it she is trying to reset the file to
create another list, and I don't believe that you solution answers that
part. at least that is the impression that I am getting from my reading
of her request.

 

[Klatuu]

Perhaps we read it differently. If the form is pulling up an existing
record for reuse, then it is a different issue. My understanding, perhaps
mistakenly based on reasonable design, is it is create a new list that would
then be populated.

I guess without either of us seeing the actual product, we can only
speculate. I will take your point that I may have misread the statment, but
I stand by my opinion that building records for every possible combination of
family members is flushing Database Design and Normalization 101 down the
toilet.

 

[Ron2006]

It appears she is not getting rid of the shopping list records since
the aisle information is being maintained. So she needs to get rid of
the checks and who selections - therefore the query. Given that
scenario, it gets more complex if items are repeated soley because of
two people using the same item. That is the premise for the doable but
not pretty solution.

Ron

 

[Klatuu]

Certainly a possible scenerio, but it would certainly also be reasonalbe that
a query would join the product table with a person table to create a list.

Who knows.

 

[Ron2006]

agreed