Child Form Breaks Link to Master Form after Row Insert (adding a new Row)

[dgerundo]

The functionality of this Form is to be able to search for an Employee and their detailed information and then make a copy of the details and be able to edit (change) any of the details. In this way all of the 'original' information is saved and can be reviewed as an audit trail.



I have a Master Form composed of Employees linked to a Child Form composed of Employee details(skills, status, Staff Title etc.) My goal is to allow the user to find an Employee in the Master Form and then display the detail information. After that the user can click on a "DUPLICATE" Button to insert a row into the database that contains the original detail information. Then the user should be able to Edit the details.

Most of this functionality works BUT after the DUPLICATE Button is clicked the Child Form moves to a NEW Record rather than the "copied record". It also seems that the Child Form has lost its link to the Master Form since I cannot navigate through the child records anymore. The 'Record" number on the bottom of the child window displays "1 of 1" . If I close the Form and then reopen it I can find the inserted child record with the copied data.

I have executed Me.Refresh, Me.Requery in my code after the SQL Insert command but these methods do not work. I am using MS Access 2013 and the data is an Access database as well. It seems that after the new record is inserted into the access table the linking between the Master and Child Forms is broken. I do not know what I must execute to re-establish the links between these forms again.

thanks,
Don

[Bulzie]

Might I suggest doing this a different way. Create a new table called tblDetailHistory that is exactly like the Detail one. When a Detail record is changed, write the before record and after(changed) record to tblDetailHistory (so will have 2 records for each time it is changed) including the DateTimeChanged and the UserID/Name of who made the change. You can either use temp fields to store the current values or lookat BeforeUpdate and AfterUpdate Events). This way it keeps your Main and Detail tables clean and your forms that the users sees will always have the latest info.

[Micron]

When you append the detail records, you likely have created a details record for which there is no corresponding master link. If that linking field is, for example, an autonumber, there is no master id that matches the new child id. I would assume basic employee data (names, addresses, etc) is in one table and volatile details are in another. As long as you're allowing duplicates on the details table foreign key (which holds the primary or indexed-no dupes field values from the master) you should be able to do this as long as the details form is not being put into Data Entry mode. The trick would be to let Access know which of the multiple details records you want it to load IF the details form is not set up to cycle through related records. If it is, the history should be viewable by moving through the records with the navigation buttons. If it is not, you probably would need a date stamp in the details table, although I don't see the point in restricting the view to one record if you're interested in history. I would open the main and child completely locked for editing in this process regardless of which path you take.

Another approach would be to load all the data into a temp main and temp child table when this form opens. Unlock the form on the button click, then allow the edits. When complete, you write the new child record to the details table and re-lock or close the form. When the form closes, wipe the temps. I would probably have a way to do minor edits, such as spelling correction without having to create a new record - unless you want to go to the nth degree of capturing every edit. Again, the schema has to support a one to many relationship between main and details, and you still have to decide on what details records to show when the form opens - the latest, or all. I think the temp tables would be your easiest route. Having said that, I presume this is not a db where there can be concurrent users doing the same thing, sharing the same front end (most people don't do this) otherwise you need a plan to segregate who is working on what temp records.

If all that doesn't help, I think more info about the underlying schema is needed if you want to keep what you already have, but get it working.

[dgerundo]

Thank you for the detailed replies. I have made some progress by putting the DUPLICATE Button on the Main Form. Now after i press the Duplicate Button the child form goes to the first record of the dataset. I can then click on the Employee Name List Box in the main form again and the child form moves to the correct employee. It's not perfect but not a show stopper. Ideally I would like to child form to automatically move to the appropriate employee after i click the Duplicate button. I do have some "Find code" in the AfterUpdate subroutine of the Duplicate button but it does not seem to work ..... yet!!!

FYI.... the problem here was not a missing master-child key since after the duplicate I could always exit out of the form and then go back in and find the new duplicated record.

thanks again

don

I believe that when the detailed record is created it DOES contain the 'linked' master-child key. This is proven since if I exit the form and then go back in and access the same employee I can see the new record that was previously created.

The application has a combo box