Update Query - uses selected record to make calculation and uses this to update all!

[DSLNVL]

Dear All,

This is my first post and I hope someone will be able to help as I've searched as best I can and can't find the answer to my question in any existing post - though sometimes finding the right way to ask the question is all google needs to throw up the answer - so maybe the answer's there and I've missed it.

I have created an update query in access and filtering to get the records listed I want updated is no problem. I then have entered a calculation based on fields and text boxes in the continuous form I'm aiming to update in the Update To section. I also have a button on the continuous form at the top that runs the update query.

My problem is that depending on which record in the continuous form is selected at the time I click on the button, the update query will happily do the calculation for that record but rather than doing the same calculation for each of the other records it just uses the answer to the calculation of the selected record???

If anybody can help me with this I would be most grateful as I have spent the past few nights searching posts, trying different things but with no success.

 

[Beetle]

First, are you trying to store calculated values in a table? If so, be aware that in most cases this is not good practice. If any of the underlying values that the original calculation was based on get changed, the calculated values will not be automatically updated, and you will have invalid data in your table. Even if you are using A2010, which is supposedly designed to handle calculated fields in tables, it can still be problematic. For more on the subject see this link.

Second, if you can post the code for your command button and the SQL of the update query, someone should be able to point you in the right direction. It may just be that you have the SQL outside a loop, where it needs to be inside, or something like that.

 

[DSLNVL]

Thank you for your quick reply, I use Access 2007 but the programme I'm trying to create is in 2003 format. I am storing the results of the calculation in a field as I need to go on and use it later repeatedly at various points, I'm OK with it not updating the vales automatically, in fact I specifically want to keep manual control over exactly when the fields are updated. My code is below I hope you can help:

Button to run the query:

Private Sub Command107_Click()
DoCmd.OpenQuery "Query1"
Me.Requery
End Sub

Query1 SQL:

UPDATE CVC SET CVC.OA = [Forms]![CV]![CVC].[Form]![CB]*([Forms]![CV]![CVSA].[Form]![AA]-[Forms]![CV]![CVC].[Form]![Text102])/[Forms]![CV]![CVC].[Form]![Text100]
WHERE (((CVC.Reference)=[Forms]![CV]![CVC].[Form]![Reference]) AND ((CVC.Locked)<>"L"))
WITH OWNERACCESS OPTION;


Maybe what you mention about looping is what is needed, at first before finding out about update queries I tried to find out how to use VBA to fill a field with a value on all records in a continuous form but all the posts seemed to refer the people to an update query.

I've cancelled down the names of the fields/columns/forms etc to their initials to keep the code looking relatively neat and tidy.

 

[DSLNVL]

I should point out that I have no idea how to make it loop through the records, I've looked around on the net and found things about a loop being a code thing and SQL to do with queries and other things about integer tables, not really sure which if any is the direction I need to go to get the query updating all the records selected individually? Anyone who can point me in the right direction that would be brilliant.