What is the best approach to updating numbers in a subform without changing the data

[MSG56]

I'm creating a recipe database. I have code associated with the "Make This Today?" button that updates the number of # of Times Made and the Last Time Made fields. I also want to allow the user to modify the number of servings, which will require me to update the quantities in the Recipe Ingredients subform. However, I don't want to update the quantities in the Recipe table. In otherwords, the next time the user opens this recipe I want them to see the original quantities.


What is this best approach to do this? Would it just be eaiser to go ahead and update the form/table and then use the same multiplying factor to divide the updated values back to the original valuse when leaving the form?

I'm a novice at this so I thought I would ask the pros before I go too far.

FYI - I'm also thinking about redoing my star rating scheme to get rid of the rest button and have the green stars trigger a lower rating if clicked. I've got to give that some more thought.

[amrut]

However, I don't want to update the quantities in the Recipe table.

Your approach is correct. No need to update the table data only for display.
I think it may be better to used a query as recordsource for the sub-form and refresh the sub-form whenever the number of servings are changed. The only glitch I think is the default value of number of servings should not change as is the case with ingredients. Is there a field in your table to save no. of servings? You will need to use a unbound text box to manage this part.

[MSG56]

Yes, I do have a field in the Recipes table for the number of servings. Thanks for the sound advice.

[MSG56]

I just solved this issue myself and thought I would include it to share with other Access rookies like myself -->(I've created a query to use as the subform listing the ingredients in my Recipes form. In order for the current record to display the proper ingredients I must include the RecipeID field in the query, but I don't want the RecipeID field visible in the subform. I tried unchecking the "show box" for the RecipeID field in the query, but that seems to cause Access to drop it altogether when the query is used as a subform. How do I keep from showing the RecipeID in the query when used as a subform?)

Solution: Before sending the above post I had the thought of setting the visibility property to "NO" of the RecipeID field while I had the Recipes form in design mode. To my surprise this had no effect - the RecipeID field was still displayed. Next, I tried simply deleting the RecipeID field (in the query subform) while I had the Recipes form in design mode. And this is what worked!

[June7]

Do you mean you deleted textbox that was bound to RecipeID field?

[MSG56]

Yes, I deleted the RecipeID field in the subform. I needed to include it in the query so the current record in the Recipes form would know which ingredients to include, but I didn't need to display it in the subform. This is what it looked like before I deleted it. When I tried not selecting the show button in the query design I received an error message when dragging the query into the Recipes form. Is there a better approach to what I'm trying to do?

[June7]

What you are doing is correct. The field is certainly needed in the query but not necessary to have textbox bound to the field although could set the textbox not visible.

Reports, however, can behave a little different. Might be necessary to have the field in a textbox.

[MSG56]

although could set the textbox not visible.

Actually, to my surprise when I changed the textbox to not visible, nothing happen. The field was still visible. I'm still scratching my head on this one.

[June7]

Sorry, Visible No does not work in Datasheet View.

[MSG56]

Sorry, Visible No does not work in Datasheet View.

Thanks for that info. I had no idea, but I'm glad to know that it wasn't a bug in Access.

[MSG56]

Your approach is correct. No need to update the table data only for display.
I think it may be better to used a query as recordsource for the sub-form and refresh the sub-form whenever the number of servings are changed. The only glitch I think is the default value of number of servings should not change as is the case with ingredients. Is there a field in your table to save no. of servings? You will need to use a unbound text box to manage this part.

Okay, I've changed the subform to a query, but now I'm stuck. If I update the values in the subform it will still modify the values in the table, won't it? (If I work directly with the query and change values it updates the values in my table, so I'm assuming that if I update values in the subform it will also update the table.)

I'm wondering if it might not be better that when a user wants to temporarily update quatities when they are about to make the recipe (or preparing a shopping list) that instead of updating the form, I present them with a report with calculated fields with the updated quantities. Then I need to figure out how to pull up a report (which doesn't exist yet) and only have it show the open record. That's probably not difficult, but I've never tried it before.

Just to provide a little more background: When someone selects the "Make This Today?" button, I present them with a YesNo box explaining that if they select "Yes" that the db will be updated (e.g. # of Times Made, Last Time Made date). This gives them a chace to back out if they hit the button by accident. If they select yes I present them with another YesNo box asking if they want to modify the number of servings. If Yes, then I open the "Multiply Dialog" form. The Mutiply Dialog has a text box where I capture the factor to be used to either increase (mutiply) or decrease (divide) the ingrediate quantities. (These are the vaules that I want to temporarily update in the subform without updating the table values.)

[June7]

You can have the user enter a number of servings value in unbound textbox on form and have the revised quantities calculate but nothing is saved anywhere. If you want to output a report then keep the form open and report refers to the unbound textbox in calcs on report.

Are the ingredient quantities for one serving or for the default recipe serving?

However, I expect that changing the number of servings would not increase all ingredients by the same factor.

Have you looked at the MS Recipes database template http://office.microsoft.com/en-us/te...001018635.aspx

Also http://office.microsoft.com/en-us/te...ai:TC010094830|

[MSG56]

The ingredient quantities are for the default recipe serving, so I'm giving the option to increase or decrease the quantities by multiplying by an entered factor. I would like to use this factor to update the quantities in the subform without updating the table values. I would also like to use this factor to update the number of servings, again only for display purposes while the user is at this record. I've already taken care of this part by adding another text box (unbounded) in top of the # of Servings text box. I simply change the visibility settings after the user enters the multiplying factor, and then I change them back when leaving this record. I thought I could possibly use this same trick for the quantities in the subform, but because the number of ingredients is different for each recipe it isn't so simple a task. I'm beginning to think the best approach is to simply use a report to display updated quantities when someone wants to make a particular recipe.

Yes, I have looked at the MS Recipes database. When they update the quantities they also update the tables, which I don't want to do.

[June7]

I am not sure what the issue is. The user's quantity factor can be use to calculate recipe adjustment on form without impacting any table records. Just don't allow edits of the recipe records in the subform.

[MSG56]

Normally, the Quantity field can be edited by the user. Such is the case when they first enter a new recipe. So, if I now want to temprarily update what is displayed in the Quantity field using a calculated value, I have to display this vaule somewhere. Well, if I display it in the Quntity field, won't that data be stored in the Recipes table? You are saying it won't impact any table records. How is that the case? Please keep in mind I'm new at this stuff and I'm trying to learn, so I really appreciate your input.