Duplicate Main form & Sub form "Run-time error '3022'

[Mick3911]

At the request of ADezii,

I wish to duplicate some fields from the Main Form and some fields from the Subform into a new Main & Sub Form

I have tried using Allen Browne's code but get an error message of "Run-time error 3022".

[NeoPa]

Hi Mick.

I suspect ADezii suggested you post your problem on here and reminded you to include the error message as well as just the number. I imagine he also would have suggested that you include what you'd tried so far.

Let me add that using a description of what you're looking to do that makes clear sense would also be a great thing to include.

You have another chance at this. The first attempt didn't really score on any of the objectives, but I hope what you need to do to make a question that makes sense is clearer now. When you post the full error message then it will be a great help, but without knowing what design you have or what code you are running it will not be easy to interpret the error message and advise what might need to be done differently.

Writing questions that make sense is not as easy as some might assume, but it certainly makes the chances of receiving helpful advice exponentially greater. Hopefully you now have a clearer basis to continue from at least. It's up to you now to give us something from which we can help you.

Good luck.

[Mick3911]

Okay, I will start at the beginning hoping that it will make it easier for you to help me.

I have a DB that contains employees training records. Training Items are allocated to employees and when training has been completed, the trainer updates the relevant record with the completion date etc.

Individual Training Items records contain (amongst other things) A TrainingItemID, TrainingCodeSOPNo, TrainingDescription, Issue Number and RefresherFrequency.

When a Training Item is amended it is treated as a new record and is allocated a new Issue Number (manually).

A Main Form has been created that consists of TrainingItemID (Autonumber), TrainingCodeSOPNo (Short Text), TrainingDescription (Short Text), Issue Number (Number) and RefresherFrequency (Number) which are contained in the tblTrainingItems table.

The Main Form has a subform (subfrmAddAmendedTrainingItem) which consists of TrainingID (Autonumber), TrainingItemID (Number), FullName (Short Text) and Location (ShortText) with data held in tblTrainingHistory table.

What I am trying to achieve is that when a Training Item is amended, the user opens the relevant current Training Item form, clicks on a button which would open a new form, transfer TrainingCodeSOPNo, TrainingDescription and RefresherFrequency and allocated a new TrainingItemID (Autonumber) into the new Main Form and transfer TrainingItemID (Number (same as the new Main Form)), FullName (Short Text) and Location (ShortText) and allocate a new TrainingID (Autonumber).

I have followed ADezii’s code (https://bytes.com/topic/access/answe...ubform-records) changing what I think should be changed (see below). I now get the following error message “Run-time error 3061. Too few parameters. Expected 2.” With CurrentDb.Execute strSQL, dbFailOnError highlighted.

I have ‘played around’ changing the field names in the Sql code but with no joy.

Private Sub cmdDupe_Click()
  Dim MyDB As DAO.Database
  Dim rstDup As DAO.Recordset
  Dim lngNewPK As Long
  Dim lngOldPK As Long
  Dim strSQL As String
 
  Set MyDB = CurrentDb
  Set rstDup = MyDB.OpenRecordset("tblTrainingItems", dbOpenDynaset)    
 
  With rstDup
    .AddNew
      'The Primary Key [TrainingItemID] will Auto Generate          
      ![TrainingCodeSOPNo] = Me![TrainingCodeSOPNo]
      ![TrainingDescription] = Me![TrainingDescription]
      ![RefresherFrequency] = Me![RefresherFrequency]    
    .Update
    .Bookmark = .LastModified         
      lngNewPK = ![TrainingItemID]
  End With
 
  'Retrieve the Value of the Old, Original Primary Key in order
  'to populate the New Main Record's Sub-Form
  lngOldPK = Me![TrainingItemID
 
  If Me.[subfrmAddAmendedTrainingItem].Form.RecordsetClone.RecordCount > 0 Then
    strSQL = "INSERT INTO [tblTrainingHistory] ([TrainingItemID], [FullName], [Location]) " & _
             "SELECT " & lngNewPK & " As NewID,[FullName], [Location] " & _
             "FROM [tblTrainingHistory] WHERE [TrainingItemID] = " & lngOldPK & ";"
                 CurrentDb.Execute strSQL, dbFailOnError      
  Else
    MsgBox "Main record duplicated, but there were no related records."
  End If
 
  rstDup.Close
  Set rstDup = Nothing
 
  Me.Requery                         
  DoCmd.GoToRecord , , acLast         
 
End Sub
 

I have uploaded an extract from my DB which hopefully will make sense.

[NeoPa]

Hi Mick.

This does indeed look a great deal better. It's more complicated, for sure, but when a problem is complicated it has to be explained.

A couple of points I'd start with :

1. Is a training record the same as a Training Item in your terminology?
   If so then referring to the same item differently makes it extremely difficult to follow.
   If not, then please explain the relationship. Without that we're just guessing.
2. Please don't ever upload a database unless & until you're asked to. So many people feel they can expect our experts to go to the extra effort of working out what the question should be, rather than doing that themselves. That may not be the case here as you have impressed with your actual question. Nevertheless, it's an immediate turn-off for experienced experts when members post their databases unsolicited. Many are unable to download such attachments anyway due to the network security they work within, and many simply won't due to the danger of downloading items from sources they neither know or trust.

If that all sounds disappointing and negative then believe me when I say again that your question is pretty good now. Not quite perfect but certainly many steps on the way. I thought others may step in - particularly ADezii as he suggested you post it originally - but some may think I already have it covered and ADezii, being retired now, is not as active as once he was.

I'll do what I can to help once you've clarified the table structure for me. Don't worry about the attachment; just don't assume anyone will look at it (I won't say it won't happen but don't rely on it. So far only one viewer has and the thread has clocked up 3,873 views already).

[ADezii]

Sorry for my delay in getting back to you and hello again to my old fried, NeoPa. Your situation is a little more complex than the example given by Allen Browne. The Record Source for the Sub-Form subfrmAddAmendedTrainingItem is qryEmployeeHistory which consists of three, interrelated Tables. Duplicating a Record on the Main Form and assigning it a New primary Key is a simple matter and I'll post the Code below on how this can be done. Duplicating the Records on the Sub-Form and relating them to the newly Duplicated Record from the Main Form may be a problem since two of the Tables need to be Updated, not just one, in order for the Records to be displayed in the Sub-Form. If you don't mind being a little patient, I'll get to it as soon as I can and see what I can do. In it's current state, the Code will never work.

Dim MyDB As DAO.Database
Dim rstDup As DAO.Recordset
Dim lngNewPK As Long
Dim lngOldPK As Long
Dim strSQL As String
 
Set MyDB = CurrentDb
Set rstDup = MyDB.OpenRecordset("tblTrainingItems", dbOpenDynaset)    'Ref #1
 
With rstDup
  .AddNew
    'The Primary Key [TrainingItemID] will Auto Generate          'Ref #2
    ![TrainingCodeSOPNo] = Me![TrainingCodeSOPNo]
    ![TrainingDescription] = Me![TrainingDescription]
    ![RefresherFrequency] = Me![RefresherFrequency]
    ![IssueNo] = Me![IssueNo]
    ![Issue Date] = Me![Issue Date]
    ![Catagory] = Me![Catagory]
    ![Group] = Me![Group]
    ![MachineGroup] = Me![MachineGroup]
      .Update
      .Bookmark = .LastModified         'Ref #3
        lngNewPK = ![TrainingItemID]
End With
 
MsgBox "The New TrainingItemID is: " & lngNewPK, vbInformation, "New ID"
 

NOTE: Forgot to mention that the reason that you are getting the Error (3022) is on the CurrentDB.Execute Statement listed below. The [FullName] and [Location] Fields do not exist in tblTrainingHistory. They do, however, exist in tblEmployees which is one of three Tables comprising the Record Source of the Sub-Form.

strSQL = "INSERT INTO [tblTrainingHistory] ([TrainingItemID], [FullName], [Location]) " & _
         "SELECT " & lngNewPK & " As NewID,[FullName], [Location] " & _
         "FROM [tblTrainingHistory] WHERE [TrainingItemID] = " & lngOldPK & ";"
                 CurrentDb.Execute strSQL, dbFailOnError      'Ref #5
 

[ADezii]

@Mick3911:
I have attached a Demo that should be very close to what you are looking for. Should you have any questions, feel free to ask.

[Mick3911]

ADezii

Apologies for the late reply. Have been maxed out doing other things and this took a back seat.

Many thanks for the demo that you have uploaded, that is exactly what I am looking for.

Your help is greatly appreciated.

Regards
Mick

[ADezii]

Here is the Revision which hopefully contains your requested changes.

[Mick3911]

Hi ADezii,

Many thanks for your prompt response.

I do not need the 'Date Completed' data to be duplicated.

How do I remove that from your code?

Cheers
Mick

[ADezii]

Please clarify indicating exactly what is NOT to be duplicated.

[Mick3911]

Hi ADezii,

I do not need the following duplicated on the subform;

Is Required, Date Completed, RefresherFrequency, Trained By, Status, PrevTrgCompDate and Notes.

Regards
Mick

[ADezii]

If I understand you correctly, then:

[Mick3911]

ADezii,

You are a star! That is exactly what I was after.

Your time and effort in sorting this out for me is greatly appreciated.

Regards
Mick

[ADezii]

Glad to help, good luck with your Project.