ComboBox Update

[jimmy1981]

Hi,

I have a table of Ingredients and a table of Recipes.

When I add a recipe I select an ingredient from a ComboBox which then populates a textbox using:

[Text19] = [Combo10].[Column](2)

This then returns the unitprice of the ingredient. The problem I'm having is if the Ingredient's price is updated this doesnt reflect in the recipe unless I go back in to it and re-select the ingredient from the combobox.

Is there a way around this?

 

[pr2-eugin]

Hi, your post is a bit confusing.. Could you please explain what are the controls?? What is the combo box linked to Recipe/ingredients? And could you also explain your problem with an example?

 

[jimmy1981]

Hi, Sorry.

My combobox has the following values and is bound to Column1.

SELECT Ingredients.IngredientID, Ingredients.Ingredient, Ingredients.Unit_Price FROM Ingredients;

The user selects the ComboBox and is presented with a dropdown of all the Ingredients from the Ingredients table.

When they select an Ingredient from the combobox an event runs 'onChange' with the following code

[Text19] = [Combo10].[Column](2)

So, I would choose say Butter from the combobox and the textbox would be populated with 0.256.

It then works out the total price and is saved.

My problem is if say the price of butter goes up by 10pence i would go in to the Ingredients table and update this price. But, this price increase does not show in the recipe i just added, it still shows the old price unless i re-select it from the combobox.

hard to explain, if you know somewhere i can put the database your welcome to have a look through.

 

[pr2-eugin]

I see what is happening here.. You are storing calculated values..

This is exactly the reason why you are not supposed to do so.. If the data changes in the depending column (table) it will not unlike Excel update the related columns..

There is a work around.. but it is not recommended as it will always cause instability to records breaking Normalisation Rules.
Allen Browne talks more about this here.

 

[jimmy1981]

how would you suggest I do it properly then? i.e not storing calculated values.
complete access newbie so stupidly thought it would work like excel.

 

[pr2-eugin]

Did you check out the link I provided?

Allen Browne talks more about this here.

See if the method provided will suit you..

 

[bob fitz]

Take a look at the attached db. I think this is a crude basic demo of what you are trying to do.

 

[jimmy1981]

So would this mean I wouldn't hold the Unit_Price on the form either, instead I'd pull this in to a query somehow?

 

[jimmy1981]

Take a look at the attached db. I think this is a crude basic demo of what you are trying to do.

That's exactly it, that's great. I've tried looking at your forms and I can't figure how you do it? How does it populate the text box with the price, and why does it update the information if I change the cost of an ingredient whereas mine never would?

 

[bob fitz]

Take a look a the Record Source property of the sub form. This is a SQL SELECT statement which is the kind of statement used in a query. If you click some where in the statement a button with three dots on it will appear to the right. If you click on this button you will see the statement as it appears in the query design view.

It joins the Products table and the RecipeProducts table by the ProdID field which then makes the fields from both tables available to be bound to controls on the form.

The Products combo box on the form is populated by the Products table (examine its Row Source property) but it is bound to the ProdID field of the RecipeProducts table (examine its Control Source property). The Product Cost text box is bound to the ProdCost field of the Products table, so when you change this value you are changing it in the table.

Explanations are not one of my strong points, so if this does not make sense to you post back with questions.

 

[jimmy1981]

Ah OK, I think I see how that's working.

Is there any way then of in the RecipeProducts table, there is the ProdID field, can this show the name of the product rather than the ID?

And on the form, you have the total at the bottom, can this value be written to a table or is this back to the calculated field no-no?

I'd like to be able to show a list of all recipes, the products they have and how much the total cost is.

And then if any ingredients change price, be able to run the report or query etc and this update all the recipes, is this possible?

I really appreciate your help.

 

[bob fitz]

Is there any way then of in the RecipeProducts table, there is the ProdID field, can this show the name of the product rather than the ID?

It may be possible but it is not the way relational DBs should set up. If it were done the way you sugest, the Product name field would be in more than one table. If you changed "Butter 225gms" to "Anchor Butter 225gms" in one table you would need to find a way to change it in the other table as well. Thats the point of RDBs. Save the data once in a table and then join the tables.

And on the form, you have the total at the bottom, can this value be written to a table or is this back to the calculated field no-no?

No. We don't normally save calculated data. Better to make the calculation as and when it is needed otherwise you're back to saving data in more than one place.

I'd like to be able to show a list of all recipes, the products they have and how much the total cost is.

I think a report would be the thing to use for this.

And then if any ingredients change price, be able to run the report or query etc and this update all the recipes, is this possible?

This already happens with the correct design. If you change the price of a product in the Products table, that should be reflected in any form or report.

 

[jimmy1981]

OK, this is harder than I expected, I'll have a try out with what you've told me and see what I can do.

Looks like maybe Excel would be an easier option though.

 

[bob fitz]

OK, this is harder than I expected, I'll have a try out with what you've told me and see what I can do.

Learning anything new usually involves a step learning curve and Access is probably more involved than any of the other Office programs. I don't know exactly what you are trying to achieve or who and how it would be used, but I think Access will give the best result. There are always people here willing to help.

 

[jimmy1981]

It's basically just a home-use database to be able to log all recipes and see what it costs to make them.

I have replicated your database and added some recipes, I have also created a report that shows all the recipes but i'd like to be able to add how much it costs to make, how would I go about this?

 

[bob fitz]

Put a text box on the sub form in the Footer Section. Use the Sum() function to get the total of item costs. I thought the example I gave you had this.

 

[jimmy1981]

Hi, Sorry what I meant was to add the total to the report.

I created a query which seems to do the trick. Thanks again for your help.

 

[bob fitz]

Hi, Sorry what I meant was to add the total to the report.

I created a query which seems to do the trick. Thanks again for your help.

If I've helped in some way, I'm pleased. If you need any other help post back.