Need info on a Check Box

[sondriven]

I want to make a form where I can track my fat food intake. Each check box will represent a food that I consumed that day. I want to know if I can make the value of a check box any number I choose and how to do that?

For Example:

Bag of Chips - 10grams of fat

I check the box that I made for Chips and I want the value of this box to be 10.


Thanks for the help.

j

 

[JCross]

If you use the frame wizard, then you can assign any value you want to the checkboxes in the frame.

Jennifer

 

[sondriven]

Frame Wizard? Is this the property box? If you are talking about the property box for the field I tried to assign a value that way and it didnt work for me.

Thanks

 

[JCross]

Nope - in design view you have your toolbox with all of the things you can put on a form.........and one of them is a frame. Have the wizard icon clicked( the magic wand) and put a frame on your form. the wizard will open and run you though all of your options for the form. one of the last will be what values you want to assign to your checkboxes.

Let me know if you need more info.

J

 

[sondriven]

Hmm this is wierd. The Magic Wand icon is always clicked and I never have had a wizard come up when I select things or do other tasks. Now Im really lost of why this is.

If the wand is selected and I put a frame on the form. What Im trying to do is select all the check boxes. But nothing is happening. Is this the way to put a frame on the form?

j#2

 

[JCross]

I'm an ass. I'm so sorry. I was looking at the picture for "Option Group" and thinking that it LOOKS like a frame, so i typed frame. But it's the Option Group that you want to use.

apologies
J

 

[Pat Hartman]

I know that people like to take shortcuts when building apps for themselves but, you are taking a direction that is going to cause you a great deal more work than you think as well as being a real pain to expand or change. Having a form with a checkbox for each food item means having to modify the form and the table every time you want to add a new food item. This design also means that you need to manually sum the fat grams by adding the fields together, both on the form to show a total and also in any query or report you want to build. This calculation also needs to change if you add a new food. Your design also doesn't allow for multiple servings. What if you're having a really bad day and eat three bags of chips?

You have a many-to-many relationship between your day and the items that you consume. Therefore you'll need three tables to store this information properly.

tblMyDays
DailyID (autonumber, primary key)
FullName (your spouse might also want to use the db)
TodaysDate (defaulat to Date())
(you should make a unique alternate index that includes FullName and TodaysDate so that you can only create one record per day per person)

tblFoods
FoodId (autonumber, primary key)
FoodName
ServingSize
FatGramsPerServing

tblDailyFoods
DailyID (primary key, field1)
FoodID (primary key, field2)
NumOfServings (default to 1)

To implement this solution, you'll need a mainform to display the information from a query based on tblMyDays that contains a subform based on tblDailyFoods. The subform will contain a combo that is based on a query of tblFoods. You choose a food item from the combo. The default number of servings should be set to 1.

With this structure, simple queries will calculate whatever you need to calculate.

 

[sondriven]

Thanks Jennifer and Pat. Looks like I'll be fit and trim in no time.......after my 3 bags of chips

j

 

[sondriven]

Pat, I seem to have a mental block here:

Once the subform is created and I have a combo box inside with the food and calorie count

Food = Column 1
Calorie = Column 2

How do I go about creating the formula to add up the second column in the subform. So far I created a unbound box for the total but dont know how to add the second column. thanks.

j

 

[Pat Hartman]

The subform needs to be based on a query that joins tblDailyFoods to tblFoods. In this query you need a calculated field.

TotalCalories:tblDailyFoods.[NumOfServings ] * tblFoods.FatGramsPerServing


Add a footer section to the subform. In the footer, create an unbound column. In the controlSource put,

=Sum(TotalCalories)


Your subform needs to show food, number of servings, and total calories. When food or number of servings is changed, the value in TotalCalories is automatically recalculated.

 

[sondriven]

I need to smack my head against the metal cabinet corner

For the subform query this table has me confused:

tbl Daily Foods

How can you have 2 primary keys in the same table as you diagrammed?

DailyID - Primary Key - Field 1
FoodID - Primary Key - Field 2

Thanks for any clarification

 

[sondriven]

Pat,

If you could please help me once again with this I would be very grateful!

Im almost there with understanding how the many-many relationships is working.

I built the database exactly the way you say to. I have trouble with linking the two tables together in the query for the subform. I made the tblDailyFoods and figured out how to make both the DailyID and FoodID primary keys so this is now a junction table. From there I need to set up a relationship between these two tables right? Which fields do I join together?

Also once this is accomplished, I build the query for the subform using the same 2 tables. How are these linked? I tried using the FoodID field to link them but when I build the form and put the subform in, the subform is grey inside.

Thank you for your help. Sorry to be a pain. But I feel like Im getting closer to understanding this all.

j