Updating a table field using Primary key as Where Clause

I hav a large table (TableA) with a Primary Key. I want to bulk update one field  with different data using the Primary key and with a
multiple linked query from an onclick event such that the query runs in session until all updates have been applied to TableA.

For example:
(1). Docmd RUNSQL sSQL = "Update TableA set Field1 = '143' where APID = 92-19, 500, 521, 556, 623, 634, 619-649 "
(2). Docmd RUNSQL sSQL = "Update TableA set Field1 = '176' Where APID = 14, 45, 56, 66 -77, 78, 81, 83, 91-96, 97 and 100 "
(4). Docmd RUNSQL sSQL = "Update TableA set Field1 = '212' Where APID = 122-233, 324, 345, 455, 897, 10045-12456 and 23567 "
(5). Docmd RUNSQL sSQL etc...

I am opened to other suggestions as wel.
LVL 3
bobby6055Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Donald MaloneyConsultantCommented:
I would do it with a TAble with Fields "Field1" "APIDL" "APIDH"
 Field1  APIDL  APIDH
143      19             92
143      500            0     (Set default to 0
143      521            0
"
"
143     619         649  etc

Then run a module to open the recordset read each record and build an update query
strSQL = "update........
If APIDH is 0 then only match APIDL    = APID
Else do a where  APID +< and =>
DoCmd.RunSQL strsql

Then read the next record and loop through till the table was read.

BUT first back up the original table just in case there was a mis entered number.

THis way it is a simple routine that never changes    only th edat would change if you awanted to repeat it again.
Best to always use tables for data that will change so the code will always be the same.

Don
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Donald MaloneyConsultantCommented:
See attached MDB  for an example
BulkUpdate.mdb
0
bobby6055Author Commented:
Don:
I am confused. Are you suggesting that I change TableA original APID name to "OldAPID" then change the attribute from "Autonumber" to "number".

In your sample, you also suggested  a "NewAPID" field with "number" attribute. I am sorry. I did not understand your logic

Could you please build the other APID numbers such that your suggested code would update TableA.field1 with values "176" and "212"

Maybe your method would be clearer to me.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bobby6055Author Commented:
Also TableA.Field1 is expected o be updated with "143", "212" and "176" respectively. I did not see anywhere in your sample with reference to TableA.Field1.

All I saw was "OldAPID" and "NewAPID"

This makes it really confusing.
0
Donald MaloneyConsultantCommented:
NOt suggesting you change anything in your DB.

The sample DB I sent was to show you how code could work to make all the changes without "Manually" writing every SQL.
I set up TableA in my DB with old/new so you can see how it would compare and  change the field values.
Do you know how to change the code behind the button so it will work with your MDB?

My Table   UpdateTableEntries   is where you would put all the new/old values so the code will make the changes.

If you want I can just change the names in my MDB and send a new one.

All you would need to do is
Import the form    frmUpdateTable    and Table   UpdateTableEntries
Backup your DB just in case there was a problem
1. add the values to the UpdateTableEntries  TAble
2 click the button to make the changes
3 check your TableA  to make sure the changes were done

DOn
0
bobby6055Author Commented:
Don:
Thanks for the idea. I'll give it a trial. If I have additional question, I'll post a related post.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.