Table/Form Setup

[Uvuriel03]

Alright, so here's my layout. I'm working on creating an inventory database with forms to enter new data and edit that data.

I already have a table for the new data, and a form to enter the new data into that table. Each new entry has it's own unique ID.

Now here's the tricky part.

What I would like to do is this:

Part 1
I want to create a sub-table of any EDITED entries. Each of these entries would be tied to it's original entry but it would have it's own unique ID number. So, for example, if Part 1 comes in, it would get an ID number like 134. Each time Part 1 is edited, those new entries would get ID numbers like 134 A, 134 B, 134 C, etc.

Part 2
I want to create a form based on a query that will search based upon part number, and once the entry is found that needs to be edited, it will do one of two things. If there are no edited entries yet, it will use the original entry as a base and create an edited entry based upon it. OR, if there IS an edited entry, it will pull up the most recently edited entry (by date) and use THAT as a base for another new edited entry.

It would then save the NEW edited entry to the Edited Entries table.

So, if Part 1 hasn't ever been edited, the form would pull up Part 1 and store any edits to it in the Edited Entries table with ID 134 A. Now, if Part 1 was edited AGAIN, it would pull up the edited entry 134 A instead of the original. It would then save any changes to that entry as 134 B.

I know what I want to do, and I'm pretty sure it can be done--I'm just at a loss as to how to structure it.

 

[pbaldy]

It sounds like you're trying to track changes made to the original record. If so, search on "audit" or "audit trail". There are two common ways of handling audit trails. One is a memo field in the main record that contains the changes made to the record (each change is appended to the end of the field). The second is a separate audit table that would contain fields for the record number (your 134), the field changed, the values before and after, and sometimes who made the change and when they made it. My preference is the second method, but the first is very common too.

 

[Uvuriel03]

Excellent, this has helped out perfectly!! Thank you!! If I would have known what such a system was called, I'd have looked for it myself! ^_^

 

[pbaldy]

Often times it's just knowing what to search for. Glad it helped you out.