I have an Access database with 5 tables: Report_Start holds PK on [Report_ID] CommSummary holds FK on [Report_ID] MRPCSummary holds FK on [Report_ID] SQESummary holds FK on [Report_ID] MEPSummary holds FK on [Report_ID] Report_ID links tables 2-4 to Table 1. User enters new report info on a user form (Report Date, Vendor, buyer, planner, etc), which is stored in Report_Start

Duplicate records in all related Access tables with new PK/FK assigned at button click event

I have an Access database with 5 tables:

Report_Start holds PK on [Report_ID]
CommSummary holds FK on [Report_ID]
MRPCSummary holds FK on [Report_ID]
SQESummary holds FK on [Report_ID]
MEPSummary holds FK on [Report_ID]

Report_ID links tables 2-4 to Table 1.

User enters new report info on a user form (Report Date, Vendor, buyer, planner, etc), which is stored in Report_Start table. At different times, different users use different forms to populate tables 2-4, and all data is linked by [Report_ID], which is the report date and vendor name concatenated together.

I added a button on the start-up form that needs to allow the user to duplicate existing data from all 5 tables and assign a new Report_ID and new Report_Date. This is needed because the users only have one or two fields to update each time we report out on the vendor status. They want to copy the old record to a new one and then make their one or two updates. (I already have forms and code that allows the existing records to be updated from each table.)

The new Report_ID and Report_Date will be generated by the user selecting from a dialog window:

a) Existing report date (Me.Cbo_OldDate)

b) Vendor (Me.Cbo_Vendor)

c) New report date (Me.Txt_NewDate)

After several days of research, I found a SQL statement that was close to what I wanted to accomplish, but I am getting syntax errors. And I am only trying to duplicate in the #1 table above to prove out my code before attacking the relational tables 2-5.

My code so far:

Start-up Form (user clicks on COPY RECORD button):

Private Sub Cmd_Copy_Click()

DoCmd.OpenForm "New_Data", acNormal, , , , acDialog

End Sub

New_Data form where user selects existing report info and new report date:

Private Sub Cmd_OK_Click()

Dim OldID As String
Dim NewID As String
Dim NewDate As Date
Dim strSQL As String


    OldID = Me.Cbo_OldDate & Me.Cbo_Vendor
    NewID = Me.Txt_NewDate & Me.Cbo_Vendor

    NewDate = Me.Txt_NewDate

    Me.Txt_Old_ID = OldID
    Me.Txt_New_ID = NewID

strSQL = "SELECT r.Report_ID, r.Report_Date, r.Vendor, r.Buyer, r.MRPC, r.SQE, r.MEP WHERE (((r.Report_ID) = ('" & OldID & "') INTO Report_Start as r WHERE (((r.Report_ID) = '" & NewID & "' and ((r.Report_Date) = (#" & NewDate & "#)));"

CurrentDb.Execute strSQL, dbFailOnError

End Sub

The error I am getting is:

If someone can help me determine where the error is, and then give me some guidance on how to duplicate the information in the relational tables and assign the new FK equal to the new PK, I would GREATLY appreciate it! I do not want to have each user open their individual form to duplicate only their data.

Lastly, due to the relationships between the tables, I want one button to duplicate all, and then each user can look up their info by date and provide their updates.

Answer

You can perform and insert into with a select and where like this:

strSQL = "INSERT INTO Report_Start " & _
         " SELECT " & newID & ", #" & newDate & "#, r.Vendor, r.Buyer, r.MRPC, r.SQE, r.MEP" & _
         " FROM  Report Start as r " & _
         " WHERE r.Report_ID = " & oldID

Advertisement

Answer