Query Help for nutritional database

[colliebe]

Hello all,



I am trying to set up a nutritional database where I can log food by selecting the item from a drop down menu and have access autofill the proteins, carbs, and fats columns. I currently have two tables, one with the master list of foods with their caloric content and another that I was wanting to be my Food Log. I've been able to figure out the lookup wizard so I've got my drop down list of foods from the Foods table already in my Food Log table. I've attached a screenshot in hopes that it will help explain what I'm trying to do. I messed around a little with the dlookup function but I think I can accomplish auto filling the fat/protein/carbs columns with the simple query. I've been getting stuck when creating the query where I can have the drop down menu, but when I go to select a different food, Access dings at me and doesn't let me change the food item. Any help will be greatly appreciated. Thank you!

[June7]

DLookup expressions should work but can be slow. Alternatively, a query that joins the two tables with join type "Show all records from FoodLog and only those from Foods that match" should do the job. However, really should use a form for data entry interface. That way you can set textboxes bound to the Foods fields as Locked Yes and TabStop No to prevent editing.

Might want to look at the Microsoft Nutrition Tracking database template.

[colliebe]

June7,

I have seen the database template; however it's way too clustered and filled with information I don't need which is why I was wanting to start my own database so that it has just the information I'm looking to track.

[alansidman]

To reiterate what June7 has stated. You should use a form for this. Look at this link on why lookups in Tables are not a good idea. These should be accomplished in forms

http://access.mvps.org/access/lookupfields.htm

Once that has been resolved, look at this link on how to populate controls in a form based upon information in a control

http://baldyweb.com/Autofill.htm

[colliebe]

Thank you for the replies.

So is the for source to display fat for a food item would be similar to "SELECT CustID, CustName, CustPhone FROM Customers"? In my foodslist combo box the row source reads " SELECT Foods.FoodItem,Foods.Fat,Foods.Protein,Foods.Carbs FROM Foods ORDER BY Foods.[FoodItem]; ". In the Fat textbox I tried to enter " =Combo11.Column2 " where combo 11 is the name of my combo list but I'm getting a #Name? error in the box.

[June7]

The index must be in ().

Index begins with 0.

Fat is in column 2 so its index is 1.

=Combo11.Column(1)

However, this will not work in query. Must be expression in textbox on form.

[colliebe]

Okay that worked for the Fat column but by me trying to add the same =Combo11.Column(2) and (3) for the other two it isn't pulling any information.

[June7]

Did you set combobox ColumnCount to 4 and ColumnWidths to 1.0";0";0";0"?

You don't show Foods table ID field in that query. Are you saving the full descriptive text instead of ID?

[colliebe]

Thank you changing the ColumnCount and Widths worked!! Thank you for all the help and patience.