Sounds like you should be using a form/sub form arrangement.
In any case, Access doesn't allow changing fields in a "Totals" qeury to prevent exactly what you want to do. Several options are:
1) Use recordsets to make the changes through code.
2) Use the DoCmd.RunSQL statement to execute an Update Query (that you can contruct via code to match your exact circumstance).
If what you want is to change the value of a field in all records where that field = some other value, I would go with option 2 - it's the quickest and easiest.
Thanks for the info tcace - I tried it and it sort of works....but :
I am using a continuous form - if I have an unbound box for the user to enter the data to be updated to all the relevant records, that textbox in each row of the form fills with the same info - as normal for an unbound control in a coninuous form!
Do you (or anyone!) know of a work-around?
Also, the RunSQL command causes a pop-up box telling you what it is doing - can this be disabled?
Getting rid of the pop-up is easy: insert DoCmd.SetWarning False before the RunSQL statement and DoCmd.SetWarnings True directly after it. This causes Access to assume the default answer to each message box instead of popping them up.
When disabling the warnings, make sure you have an error trap setup to re-enable warnings and display the error. "Warnings" is an application level setting - so if you break code with warnings set false, NO message boxes will pop up (such as the "Save" dialog).
As for the continuous form, can you utilize the header or footer? You can also setup the "continuous" part of the form in a sub form and make it look like a single form. Just some easy to impliment ideas.
I have had a similar problem with a continuous form where I needed to be able to change calculated values in individual records (before I found this forum I might add!).
The solution I adapted was pretty complicated for a beginner (or someone who had just thought of the concept !). That is to use temporary table defined in the database, populate with an append query to calculate the required values when (before!) the form opens. The form is based a query using the temporary table (and any others required). The draw back is that this may produced a recordset that could not be updated (union query in my case), therefore, this had to be done using a popup form (double_click event of control/textbox to be changed) to change the value in the temporary table, and the form required when the popup is closed to update the new value.
The changes can then be written to the 'real table(s)' with an append and/or update queries.
If you are using it on a network/multi-users it get even more complicated because you may need to put the temporary table on C: drive(s) and link it (to stop other people changing your value(s) before you commit them) You also my be need to use transactions if you are appending records with generated primary key values (like the next invoice number) as others may try to commit the same information, or you are updating multiple tables to ensure everything stays synchronised one update fails.
The data in the temporary table(s) was deleted when the form was closed as this was not necessarily up to date after the ‘real tables’ were updated.
To disable warnings use
execute SQLs where
Trust these rambling give you some idea !
I will be more than interested in any other solutions because I feel there should be an easier way.