One form to amend 3 tables

kealaz · External Since: Feb 23, 2009 Posts: 8

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 · External Since: Nov 04, 2004 Posts: 2342

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.

"kealaz" <kealaz.TakeThisOut@discussions.microsoft.com> wrote in message
news:435C8999-7AC8-408A-8B3F-C43AD4DF6151@microsoft.com...
>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.
>
>

Fred · External Since: Jun 26, 2006 Posts: 84

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.

"kealaz" wrote:

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