If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Access 2003 forms lookup problem
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? |
#2
|
|||
|
|||
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. -- Wayne Morgan MS Access MVP "FrankSpokane" wrote in message ... 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? |
#3
|
|||
|
|||
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" wrote: 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. -- Wayne Morgan MS Access MVP "FrankSpokane" wrote in message ... 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? |
#4
|
|||
|
|||
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. -- Wayne Morgan MS Access MVP "FrankSpokane" wrote in message ... 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. |
#5
|
|||
|
|||
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" wrote: 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. -- Wayne Morgan MS Access MVP "FrankSpokane" wrote in message ... 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. |
#6
|
|||
|
|||
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. -- Wayne Morgan MS Access MVP "FrankSpokane" wrote in message ... 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" wrote: 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. -- Wayne Morgan MS Access MVP "FrankSpokane" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Why is a French Spellchecker a "required" update for English speak | French Spellcheck Required? | General Discussion | 23 | April 26th, 2005 01:17 AM |
Access 2003 Barcode Problem | Alan | New Users | 0 | November 25th, 2004 04:27 PM |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
Useless Access 2003 | tired, angry, sucidial and bored | General Discussion | 10 | July 21st, 2004 11:52 PM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |