Access 2003 forms lookup problem

[FrankSpokane]

I have a small mdb I've written to track exercise and calories consumed.

I have 2 small tables -

Food (All foods I might eat)
ID Autonumber
Food Text
Calories Number

Food Eaten (Food actually eaten)
Id Number
Food Text
Calories Number

And a simple query- foodsort - that sorts the Food table alphabetically by
Food and Calories

I created a form for the Food Eaten Table with a combo box that uses the
Foodsort query to pick a food and its related calories, and place it in the
Food Eaten Table.

It looks at the Foodsort query to pick the food, and place the related
calorie value in the calorie field of the Food Eaten table.

This works fine, except for foods that have the exact same calories, in
which case it always picks the first food with the same calories.

Can someone tell me what I'm doing wrong?

 

[Wayne Morgan]

While this can be done, you are actually storing too much in the Food Eaten
table. All you should need from the Food table in the Food Eaten table is
the ID of the food. You may also want fields for when eaten, quantity eaten,
etc, but no other data from the Food table. You should also have a unique ID
field for each record in the Eaten table. When you need the other data from
the Food table, such as for a report, include both tables in a query and
link them on the ID field.

Now, to get the correct value (the ID number) in the Eaten table. In the
combo box, set the Row Source to your query. The query should include the ID
field from the Food table. The columns will display in the combo box in the
order that you have them listed in the query. Set the Width of the column
with the ID field to zero so that the ID won't be visible. Set the Bound
Column of the combo box to this column. Since ID is an Autonumber, it is a
Long Integer, make sure the data type for the ID number in the Eaten table
is also set to Long Integer. When you make a selection in the combo box, the
remaining visible columns will show in the drop down, but only the first
visible column will show in the textbox part of the combo box once you've
made your selection.

 

[FrankSpokane]

Hi Wayne

I think my problem lies in how I want to see this info. Your explanations
and suggestions make perfect sense, but what I am trying to do is display
onscreen in the Food Eaten form the foods I ate for the day, and I have the
form summing the calories. Everything id working except for my problem with
foods that have the same calorie count.

This form is actually a subform on another form which shows my daily
mileage, times and other health related information. Thus this gives me one
form to review for each days workout, and I have supporting reports also.

This is a very small app and I could email you a copy to explain better.

Maybe what I want can't be done.

 

[Wayne Morgan]

It can be done, the trick is to get the bound column of the combo box to be
from a unique field. At the moment, I would guess that the bound column is
the Calorie field, so when you go back to look at the records, it displays
the first one it finds with that value for the calories.

 

[FrankSpokane]

When i change the bound column as you say, that solves the duplicate problem,
but then the ID shows up in the calories field, not what I want.

The tables are as I described and both id's are long integers.

The foodsort query sorts
1 food 2 calories 3 id to get the food in alpha order

On the Food Eaten form I placed one field - the calories field.

Then I created a combox box which used the foodsort query -
and it uses the 3 fields in the query - and I told it to fill in the
calories field.

When i do this, the Foodeaten form displays the food and its calories but I
still have the duplicate problem.

When I change the bound column as you suggested from 2 (calories) to 3 (id)
it then displays the food and its id and solves the duplicate problem, but I
want to see the food and its calories.

Pls advise

 

[Wayne Morgan]

When I change the bound column as you say, that solves the duplicate

problem,
but then the ID shows up in the calories field, not what I want.

As stated in the first message, set the width of the ID field to zero to
hide it. The first VISIBLE column is what will show in the combo box once a
selection has been made.

To do this, on the Format tab of the combo box's Properties sheet, there is
a Column Widths option. Enter the widths of the columns as desired, in order
from Left to Right, entering 0 for the ID column. Separate the entries with
semicolons (US, other countries may use a different separator).

Example:
If the ID column is the third column,
0.5";1";0"

Set the widths as needed to see the full entry for each column in the drop
down. There is also a List Width option, if this is left at Auto, it will
size itself to the sum of what you entered above.

 

[FrankSpokane]

I think we might be talking about 2 different things - you're talking about
getting the right info in the table and I'm talking about getting the food
and calories to show up onscreen in the form - so I can visually see them,
and I have it summing the daily calories.

I'm sure I have a much lower-level view than yours.

Thanks for all your help and examples as I have worked through each and
learned a lot about combox boxes, cound columns, and column width adjustment.

 

[Wayne Morgan]

Well, you can't get the correct data to show on the form unless the correct
data is in the table. If you want the form to display data from more than
one column in the combo box, that can be done. Place a textbox next to the
combo box and set the textbox's control source to an equation that points to
the column of the combo box you want to display.

Example:
=cboMyCombo.Column(1)

The index number for the Column property is zero based, so 0 is the first
column, 1 is the second, etc. While you can display multiple columns in the
drop down list, once a selection has been made, the first visible column
will display in the combo box, you can use the above example to display
other desired columns. Once you have a unique value stored, you will get the
correct value back instead of the first one with the same calorie content as
you were getting.

To get the sum from this, use a query with the form's record source and the
lookup table with the food and calories linked together. Use DSum() to
retrieve the sum of the records for that day.

One possible setup for all of this would be a main form with the day and sum
information and a subform with the multiple food entries for that day.