One form to amend 3 tables

[kealaz]

I need to create a form that will make changes to three different tables.

When updating a rev change to a drawing number, I need to amend the drawing
log, inventory and whereuse log.

I need to find the record with the part number and change the rev. For
example.

1000-0101-X needs to be changed to 1000-0101-A


I need to do a search on 4 fields in 3 tables to find the part that needs to
be updated.

table: tblDWGLOG
field: PART_NO

table: tblNVENTORY
field: PART_NO

table: tblWHEREUSE
field: PART_NO

table: tblWHEREUSE
field: USED_ON

When I find the part number, I need to make the change to update the rev.
letter, similar to a find and replace.



Can I do this with one form?

Thank you in advance for any assistance.

 

[Jeff Boyce]

In a well-normalized relational database, it is rare to need to update more
than one table.

First, the notion of changing the drawing number means you might "lose" the
history connection for anything related to the old number. A common
approach is to keep the drawing number and use a "rev" field to keep
sequenced/history pointers.

"How" depends on "what", and I don't have a very clear picture of what
data/data structure you're working with.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Fred]

My gut feel is that you should create / select / identify a number that
outlives such changes

(probably e.g "1000-0101"), make it the PK in your master table, link the
other tables to it on that number, and create a seperate RevNumber / Suffix
field in your master table. Then have those other tables access and displays
that suffix whenever it is needed.