470,833 Members | 1,477 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,833 developers. It's quick & easy.

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

2 2Bits
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".
May 23 '22 #1
5 16698
32,311 Expert Mod 16PB
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.
May 23 '22 #2
2 2Bits
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDupe_Click()
  2.   Dim MyDB As DAO.Database
  3.   Dim rstDup As DAO.Recordset
  4.   Dim lngNewPK As Long
  5.   Dim lngOldPK As Long
  6.   Dim strSQL As String
  8.   Set MyDB = CurrentDb
  9.   Set rstDup = MyDB.OpenRecordset("tblTrainingItems", dbOpenDynaset)    
  11.   With rstDup
  12.     .AddNew
  13.       'The Primary Key [TrainingItemID] will Auto Generate          
  14.       ![TrainingCodeSOPNo] = Me![TrainingCodeSOPNo]
  15.       ![TrainingDescription] = Me![TrainingDescription]
  16.       ![RefresherFrequency] = Me![RefresherFrequency]    
  17.     .Update
  18.     .Bookmark = .LastModified         
  19.       lngNewPK = ![TrainingItemID]
  20.   End With
  22.   'Retrieve the Value of the Old, Original Primary Key in order
  23.   'to populate the New Main Record's Sub-Form
  24.   lngOldPK = Me![TrainingItemID
  26.   If Me.[subfrmAddAmendedTrainingItem].Form.RecordsetClone.RecordCount > 0 Then
  27.     strSQL = "INSERT INTO [tblTrainingHistory] ([TrainingItemID], [FullName], [Location]) " & _
  28.              "SELECT " & lngNewPK & " As NewID,[FullName], [Location] " & _
  29.              "FROM [tblTrainingHistory] WHERE [TrainingItemID] = " & lngOldPK & ";"
  30.                  CurrentDb.Execute strSQL, dbFailOnError      
  31.   Else
  32.     MsgBox "Main record duplicated, but there were no related records."
  33.   End If
  35.   rstDup.Close
  36.   Set rstDup = Nothing
  38.   Me.Requery                         
  39.   DoCmd.GoToRecord , , acLast         
  41. End Sub
I have uploaded an extract from my DB which hopefully will make sense.
Attached Files
File Type: zip Training Records Demo1.zip (148.2 KB, 7 views)
4 Weeks Ago #3
32,311 Expert Mod 16PB
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).
4 Weeks Ago #4
8,807 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstDup As DAO.Recordset
  3. Dim lngNewPK As Long
  4. Dim lngOldPK As Long
  5. Dim strSQL As String
  7. Set MyDB = CurrentDb
  8. Set rstDup = MyDB.OpenRecordset("tblTrainingItems", dbOpenDynaset)    'Ref #1
  10. With rstDup
  11.   .AddNew
  12.     'The Primary Key [TrainingItemID] will Auto Generate          'Ref #2
  13.     ![TrainingCodeSOPNo] = Me![TrainingCodeSOPNo]
  14.     ![TrainingDescription] = Me![TrainingDescription]
  15.     ![RefresherFrequency] = Me![RefresherFrequency]
  16.     ![IssueNo] = Me![IssueNo]
  17.     ![Issue Date] = Me![Issue Date]
  18.     ![Catagory] = Me![Catagory]
  19.     ![Group] = Me![Group]
  20.     ![MachineGroup] = Me![MachineGroup]
  21.       .Update
  22.       .Bookmark = .LastModified         'Ref #3
  23.         lngNewPK = ![TrainingItemID]
  24. End With
  26. 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.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO [tblTrainingHistory] ([TrainingItemID], [FullName], [Location]) " & _
  2.          "SELECT " & lngNewPK & " As NewID,[FullName], [Location] " & _
  3.          "FROM [tblTrainingHistory] WHERE [TrainingItemID] = " & lngOldPK & ";"
  4.                  CurrentDb.Execute strSQL, dbFailOnError      'Ref #5
4 Weeks Ago #5
8,807 Expert 8TB
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.
Attached Files
File Type: zip Training Records_Revised_Demo1.zip (169.6 KB, 1 views)
3 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

30 posts views Thread by Shannan Casteel via AccessMonster.com | last post: by
3 posts views Thread by Richard Lewis Haggard | last post: by
1 post views Thread by Vladimir Oľura | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.