Possible update query

[Alexs66]

Hi,
I know its not normally the done thing but what i want to do record the results of a calculated select query to fields in a table. I'm hoping to do this via a button on a data entry form. I have a "Recipe" form and from that i can pull in ingredients from a combo box in a subform. This gives me the fat, saturated fat, carbs, sugar, energy etc. (all of this info is from the nutrition label on the packaging of the ingredients which is stored in another table) I want to be able to change say a high fat item to a low fat item within the same recipe and then hit an update button to record and save the data. Its working beautifully when i run the query however, how do i record the results in table? I have read that an update query would work for me and Ive spent hours on this but i cant get it to work. I have tried to create a new update query with the calculated query as the data source so i just get the answers, but a cant get them to record in a table. Any help is greatly appreciated..

[Bulzie]

Hard to say without knowing the structure of your tables and fields. Can you list out the tables, fields and any linking.

To update a field in a table, once you select a new ingrediant, you could maybe use an update query in the AfterUpdate event of the ingrediant field. Something like:

DoCmd.RunSQL "Update YourTable set Ingrediant= " & Me.NewIngrediant & " where RecipeID=" & Me.RecipeID
Forms!YourForm.Requery

But again, not sure if that is what you need. Would be nice to just bind the ingrediants fields on form and just change them all on the form as needed.

[John_G]

Do you want to create new records in the table, or to update (change) existing records?

If you want to add new records, then you need an Append query, not an Update.

Can you clarify, please.

[Alexs66]

Thank You for the responses. I'm wanting to update existing fields and not create new records. What i have at the moment is a query based on the current open form frm_Recipes (qry_1STCALC) which calculates the quantity by the value in the nutrient field. (nutrient field being fat. saturated fat etc) then i have a second query (qry_2NDCALC) With qry_1 being the data source. This gives me a total value for say carbohydrates in a given recipe and divides it by the amount of servings. So what i end up with is a list of values which essentially is the full nutrient value (theoretically of course) of an entire meal on a plate. This is what im trying to achieve and its these values that i wish to update to tbl_recipes into their corresponding field. When i run qry_2NDCALC i get the data Im looking for. I have tried several ways but have got no where. I can create a report and pull the data up and type it into the fields not a problem but there must be a way of automating this.

Thanks again..

[Bulzie]

Not sure what 2NDCALC looks like but can you maybe create a 3rd Update query based on that second query and also bring in tbl_recipe and link by RecipeID. Remember to have a good backup of the data when using Update queries.