The number of possible values in the "name_of_field..." field will of course be limited, and this suggests an algorithm that could be used:
"SELECT DISTINCT name_of_field_to_update" ...
for each field-name returned (call it "f"):
build and execute an SQL string:
"UPDATE ... SET " & f & " = change_to_value WHERE name_of_field_to_update = '" & f & "'"
So you construct: (for example)
UPDATE .. SET balance_due = change_to_value WHERE name_of_field_to_update = 'balance_due'UPDATE .. SET total_fines = change_to_value WHERE name_of_field_to_update = 'total_fines'
Thus you would build and execute, first, a query that updates all the balance_due columns, all at once. Then a second query would update the total_fines columns, also all-at-once. Q.E.D.
Obviously the SQL-string I have constructed might be much too simple. (It is, in fact: I haven't talked about "id" columns in any way whatever.) But you get the idea: that you construct SQL strings based on data in the table.
Thank you so dearly and from the bottom of my heart!
I have considered a way similar to that, but I want the field_to_be_changed to be a drop down list from the field names of a table and that table may change and I don't want to have to add code for each added field. So what I need is a way to say (field_to_be_changed) = new value
I greatly appreciate your solution and it is a good one. I posted this issue before and got no responses at all.
Is there some function to put the contents of a field into the left of an equation or can you do this with recordsets such as rstRecordset.fields(field_to_be_changed) = new value ???
Re: Thank you so dearly and from the bottom of my heart!
I have considered a way similar to that, but I want the field_to_be_changed to be a drop down list from the field names of a table and that table may change and I don't want to have to add code for each added field. So what I need is a way to say (field_to_be_changed) = new value..
Kindly notice that you wouldn't have to "add new code for each field." If you store the exact name of the field to be changed (whether you derive it from a combo-box or whatever), then the logic I described earlier will work in principle no matter what the field name is.
Carefully re-read the post. The logic queries the database to obtain the list of field-names on the fly, then it constructs a query for each field-name found.
You do have to be careful when designing such a program's user-interface: you probably don't want an id field to be eligible for example. And you have to be mindful of data-types and so on. So my reply was only intended to be an illustration, but it does loosely describe the concept of a workable design that will not require code modifications for each new field.
Ahhhhh I see Thank you for beating it into my head. I think I got it, I have mostly been using VB code to do the data work. I don't know sql very well. But If I understand correctly, you build a set of queries on the fly and then update the one that applies.
I truly appreciate your help. I hope I can implement it.