• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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.
  • 3
  • 3
1 Solution
Donald MaloneyConsultantCommented:
I would do it with a TAble with Fields "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.

Donald MaloneyConsultantCommented:
See attached MDB  for an example
bobby6055Author Commented:
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.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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.
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

bobby6055Author Commented:
Thanks for the idea. I'll give it a trial. If I have additional question, I'll post a related post.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now