Including two fields from record in table into subform (1 Viewer)

[Watergirl]

Whether I try a combo box or a list box, I seem to be unable to point to the item I want from my table and automatically include a second field from the same item's record. I am trying to add multiple ingredients to my recipe, and grab not only the ingredient, but also it's price. (I will then multiply that price by the quantity, get a sum, and figure out the total cost of this recipe.)

'He' won't let me bring the information in to the subform from my query, telling me that the query is already based on the table I am referencing. I know it shouldn't be that hard . . . I can get one field or the other, but not both, or not in sync. I have tried so many combinations and convoluted work-arounds I think I am getting slap happy.

And not so much on the 'happy' part, either!

Thanks in advance,
Pam

 

[RuralGuy]

If you have the ingredient table joined to your receipe table then all you need to have the ComboBox do is supply the IngredientID to your query and all of the other fields you included from the ingredient table will show in your query.

 

[Watergirl]

Thank you for the fast response, RuralGuy.
The ingredient table is joined to the recipe table through the recipe ingredients table. I'm sorry - I don't understand: why write a query when the 'answers' I need are already in two fields on the record of the ingredient table? The query I do have written shows the ingredient ID, ingredient name (which I'd like the customer to be able to see from the drop down list), and price. But I do not see that query as an option from the ...build events in the expression builder anyway. I'm sorry to be so dense . . . what am I missing?
Pam

 

[RuralGuy]

Use the query builder to join the two tables on the [ingredient ID] field and include whatever fields you need from that table in your query. Bind your form to that query and then bind you ComboBox to the [ingredient ID] field of the query and start changing the ingredient and see what happens. You will need to bind controls to the Price field of the query as well. If you post your db or at least a sample of it that includes these two tables and the form then I could show you how to do it. Queries are *very* powerful and the backbone of an RDBS. They can do most of the work for you. Having the price in more than one table is a normalization violation as well unless you have some historical reason to have it there.

 

[Watergirl]

I have attached the database and would be overwhelmingly grateful for any help! I have the cost field in the ingredients table only because it changes every single time we buy more of any given raw material, which is at least once a month - and exactly why I've been asked to write this database in the first place - making these changes in the old dbase program is outrageous and error prone, too.

Bless you.
Pam

 

[RuralGuy]

I see others are looking too. I expect we'll have a solution by tomorrow.

 

[boblarson]

As Simon Cowell says, "I'm not trying to be rude, but..." How in the world did you come up with this?

=[TotalCostPerLb/Gal]![Total]

Your syntax is off and you even tried combining two fields into one and both of them isn't even in either of your form's recordsources.

You could probably use a DLOOKUP to get the price from the appropriate field (not fields).

 

[Watergirl]

My excuse:

Sorry Mr. Larson - I should have deleted that from the subform before zipping the file - as I stated, I had tried many different ways (queries, combo boxes, list boxes etc.) that didn't work. From this database I began making a copy and using the copy to keep trying so as not to completely screw up what WAS working by mistake.
At one point I realized the slash sign in Lb/Gal was asking for trouble, so I changed the field to 'LbperGal' instead.
Aside from that blooper, any suggestions?
Again, many thanks,
Pam

 

[RuralGuy]

Mornin' Pam,
Here's some reading while you are waiting that should further your Access understanding.
The Evils of Lookup Fields in Tables

 

[Watergirl]

Not only a good article, but another excellent resource I was not aware of - thanks!
Pam

 

[RuralGuy]

Certainly one of the top 5 Access sites on the net.

 

[RuralGuy]

Pam,
Here's your db back with some changes. I changed some tables and added some queries and forms. I moved all of the calculations to the queries and dynamically calculated some of the values you had hard coded into your tables. Primarily try out frmIngredients and frmProducts. It is just a start but I think you will get the drift on queries after reading this db. Post back if you have some questions on what I've done and have fun with your new found abilities!