Avatar of richardlh

asked on 

Add and maintain records in a second table to show chronology of changes to project using sub form in main form

I want to be able to select a project record in table one and copy it to table two and then make changes to the project information stored in table two (all fields are the same in the two tables with different names to distinguish between original project information and changed project information). Then I want the user to be able to select the same project in table one becuase there is another change to be made and have Access check the second table for the same project number. If the project number is found, then check the number in a second field for the number of revisions and copy the original project record into the second table as the next revision number. The intent is to be able to show all the revisions made to the original project keeping track of the sequence of the revisions.

Example: Original project number "AA1001" in table one is copied to table two as "AA1001" in one field and revision "1"  in second field to be displayed as "AA1001.1" using concatenation in textbox on form. Then when the next change to the project is necessary, Access checks for "AA001" in table two and if found then finds the last revision number in second field and adds the project as "AA1001.2" keeping revision .1 and .2 in table two to review the sequence of the changes.

I have used the findfirst_nomatch routine in Access help and can get close to a solution, but having a hard time getting to the point of finding last revision of the project and adding "1" to the revision number and storing the project in table two so I can display it in the subform under the main form to make the changes.

Any suggestions?
Microsoft Access

Avatar of undefined
Last Comment
Eric Flamm

8/22/2022 - Mon