A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access 2003 forms lookup problem



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2005, 06:43 AM
FrankSpokane
external usenet poster
 
Posts: n/a
Default 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  
Old May 6th, 2005, 02:25 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

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  
Old May 6th, 2005, 08:58 PM
FrankSpokane
external usenet poster
 
Posts: n/a
Default

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  
Old May 6th, 2005, 09:39 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

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  
Old May 7th, 2005, 12:08 AM
FrankSpokane
external usenet poster
 
Posts: n/a
Default

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  
Old May 7th, 2005, 02:30 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

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.






  #7  
Old May 8th, 2005, 04:12 AM
FrankSpokane
external usenet poster
 
Posts: n/a
Default

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" wrote:

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.






  #8  
Old May 9th, 2005, 11:00 AM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

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.

--
Wayne Morgan
MS Access MVP


"FrankSpokane" wrote in message
...
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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 05: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


All times are GMT +1. The time now is 10:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.