Access 2003 Combo lookup problem

[FrankSpokane]

I have a combo box on a form that looksup to a query - (foodsort) - which
sorts a table (foods) based on their name and caloric content, and then puts
these values in another table (food eaten).

My problem is that I can enter two foods that have the same caloric value in
the Foods table, but on the form when the combobox does the lookup to the
query, it always pulls in the first food that has that caloric value. I am
allowing for duplicates in the caloric values in each table.
Structures:

Food table:
Id autonumber
Food text
Calories Number dupes allowed

Food eaten table:
Id number
Food text
Calories number dupes allowed

The combobox is on a Form for the Food eaten table. It works fine for foods
with a unique caloric value , just not for dupes

Not sure where I am going wrong on this and all ideas appreciated.

I can upload the mdb as it is real small.

This is a running database I created years ago to track my jogging, and now
I want to be able to track my caloric consumption for each day too.

 

[Mark M]

It sounds like your combo box is searching by the calories field, where in
fact it should be searching by the ID number field. Make sure the ID number
is part of your combo box and change the search code to search by that
field. If the combo box is not the bound column, then your search code
would be something like: [ID]=Me.combobox.column(x). Where column(x) is
the column which holds the ID value; just remember that column counting
starts with zero.

 

[FrankSpokane]

I’m flummoxed on this one. When I delete the old combo box, and create a new
one based on the foodsort query, There’s only 3 fields: Food, id, calories

The foodsort query has 3 fields in it, and sorts by food to have the food in
alphabetical order:

The combobox wizard asks me what query to use (foodsort) and then asks what
fields do you want in your combo box? Food id calories


I want the combobox to show the foods in alpha order on the lookup, and then
put the related calories in the calorie field on this same form next to the
combobox.

Which fields do I use where?

The only thing that works correctly is when I choose all 3 field for the
combobox, sort the food field, and tell it to put that related value in the
calorie field, but then I’m back to my problem of duplicate calories for
different foods again.

It sounds like your combo box is searching by the calories field, where in
fact it should be searching by the ID number field. Make sure the ID number
is part of your combo box and change the search code to search by that
field. If the combo box is not the bound column, then your search code
would be something like: [ID]=Me.combobox.column(x). Where column(x) is
the column which holds the ID value; just remember that column counting
starts with zero.

I have a combo box on a form that looksup to a query - (foodsort) - which
sorts a table (foods) based on their name and caloric content, and then
puts
these values in another table (food eaten).

My problem is that I can enter two foods that have the same caloric value
in
the Foods table, but on the form when the combobox does the lookup to the
query, it always pulls in the first food that has that caloric value. I
am
allowing for duplicates in the caloric values in each table.
Structures:

Food table:
Id autonumber
Food text
Calories Number dupes allowed

Food eaten table:
Id number
Food text
Calories number dupes allowed

The combobox is on a Form for the Food eaten table. It works fine for
foods
with a unique caloric value , just not for dupes

Not sure where I am going wrong on this and all ideas appreciated.

I can upload the mdb as it is real small.

This is a running database I created years ago to track my jogging, and
now
I want to be able to track my caloric consumption for each day too.