473,287 Members | 1,960 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

6 Nibble
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

✓ answered by 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.

13 33041
NeoPa
32,554 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
Mick3911
6 Nibble
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
  7.  
  8.   Set MyDB = CurrentDb
  9.   Set rstDup = MyDB.OpenRecordset("tblTrainingItems", dbOpenDynaset)    
  10.  
  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
  21.  
  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
  25.  
  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
  34.  
  35.   rstDup.Close
  36.   Set rstDup = Nothing
  37.  
  38.   Me.Requery                         
  39.   DoCmd.GoToRecord , , acLast         
  40.  
  41. End Sub
  42.  
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, 62 views)
May 27 '22 #3
NeoPa
32,554 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).
May 31 '22 #4
ADezii
8,834 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
  6.  
  7. Set MyDB = CurrentDb
  8. Set rstDup = MyDB.OpenRecordset("tblTrainingItems", dbOpenDynaset)    'Ref #1
  9.  
  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
  25.  
  26. MsgBox "The New TrainingItemID is: " & lngNewPK, vbInformation, "New ID"
  27.  
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
  5.  
Jun 1 '22 #5
ADezii
8,834 Expert 8TB
@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.
Attached Files
File Type: zip Training Records_Revised_Demo1.zip (169.6 KB, 65 views)
Jun 3 '22 #6
Mick3911
6 Nibble
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
Sep 7 '22 #7
ADezii
8,834 Expert 8TB
Here is the Revision which hopefully contains your requested changes.
Attached Files
File Type: zip Training Records_Revision_2.zip (86.6 KB, 55 views)
Sep 18 '22 #8
Mick3911
6 Nibble
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
Sep 21 '22 #9
ADezii
8,834 Expert 8TB
Please clarify indicating exactly what is NOT to be duplicated.
Sep 21 '22 #10
Mick3911
6 Nibble
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
Sep 21 '22 #11
ADezii
8,834 Expert 8TB
If I understand you correctly, then:
Attached Files
File Type: zip Training Records_Revision_3.zip (86.6 KB, 51 views)
Sep 22 '22 #12
Mick3911
6 Nibble
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
Sep 23 '22 #13
ADezii
8,834 Expert 8TB
Glad to help, good luck with your Project.
Sep 23 '22 #14

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

Similar topics

4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
30
by: Shannan Casteel via AccessMonster.com | last post by:
I have a subform named "sbfrmParts" with a list of parts along with the quantity and price. I have used a text box in the subform's footer and set the control source to "=Sum(*)". I set the...
2
by: Legendary Pansy | last post by:
Hello, I'm trying to accomplish the impossible by trying to do something equivalent of this example found here http://www.c-sharpcorner.com/Code/2003/Dec/DialogTutorial.as Starting with "Listing...
2
by: Reginald Bal | last post by:
Hello, I created a main form with 2 subforms. In an attempt to move to the next record (or new record) on the main form I get the error " Method 'requery' of object '_Subform' failed". The...
3
by: Richard Lewis Haggard | last post by:
I have a form that has a label. This form also has a tab control. The tab control has a user control pasted on its page. Under some conditions, the user control needs to send status strings back to...
1
by: Vladimir O¾ura | last post by:
I am building a Pocket PC application in C#. I have 5 forms (1 main and 4 sub forms). The main form must always load first. The sub forms are loaded from the main form. The application is run...
1
by: solargovind | last post by:
Hi, I have one Form in which i have one subform also which links together by one common_id(Payment_id). When i display record, I need to delete Current record in both form & Table. I used the...
1
by: edensj | last post by:
I have the main form and subform working in order to do data entry. When the last record is added in the subform and the new record is cliked, I want the cursor to return to the first field of the...
0
by: Sathishkumar K | last post by:
Hi All, I am facing problem while displaying the progress bar form on top of application main form. Let me explain bit more details. I running the main form through Application.Run(new...
2
by: Triciclo | last post by:
We have an Access 2002 Front End Database that will be used by 8 users to enter data. Each user has own copy on their own file in the shared drive. We have tried the Front End in 7 computers and...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.