changing values in a table through code MSAccess XP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to update values in a field in a table on a quarterly basis.
Currently staff are doing this manually. I'd like to automate this process
for them by supplying a button on the switchboard that when clicked would
update all the records. I have tried a hundred combinations of if statements
and case statements but as yet have not found the correct sequence of code.
Basically if [mytable!Myfield]= 4 then update to 5. If 5 update to 6
etcetera. The field is a combobox based on another table of values.
4,5,6...are the bound fields. The tables reside in an SQL database and are
linked.

Am I going about this in the right way? Maybe I should create a macro or a
query that would run through the button. I'm lost. Hope someone can help.
 
There are probably several ways to do this. But one way is to create an
update query, where the "Update To:" value for this field would be:

[mytable!Myfield] + 1

You may then want to create a macro that does two things:

1. Backs up the table to be modified
2. Runs the update query

Then you can create a menu option on the switchboard that runs the macro.

Danny
 
I need to update values in a field in a table on a quarterly basis.
Currently staff are doing this manually. I'd like to automate this process
for them by supplying a button on the switchboard that when clicked would
update all the records. I have tried a hundred combinations of if statements
and case statements but as yet have not found the correct sequence of code.
Basically if [mytable!Myfield]= 4 then update to 5. If 5 update to 6
etcetera. The field is a combobox based on another table of values.
4,5,6...are the bound fields. The tables reside in an SQL database and are
linked.

Am I going about this in the right way? Maybe I should create a macro or a
query that would run through the button. I'm lost. Hope someone can help.
Click to expand...

The need to do this at ALL suggests that your table design is not
ideal. What do these numbers mean?

A couple of comments:

- A field IS NOT AND NEVER CAN BE a combo box. A combo box is a
display tool, not a data value! What you *see* in the table datasheet
might be a combo box, but only if you have fallen victim to
Microsoft's abominable Lookup misfeature:

http://www.mvps.org/access/lookupfields.htm

- An Update query based on your table updating the field to

[MyField] + 1

*may* be the way you want to do this. It will require caution; as
written this would update every single record in the table,
incrementing the value of MyField whatever that might be; and it
assumes that MyField actually *contains* the number that you see,
which it will NOT if this is a lookup field.

Do note that neither If nor Case statements, nor any code, are needed
- just an Update query. But it should be the *right* update query;
please post back with more details about the table structure before
trying this!

John W. Vinson[MVP]
 
Back
Top