472,805 Members | 926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 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

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 32920
32,534 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
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
  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, 61 views)
May 27 '22 #3
32,534 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
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
  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
Jun 1 '22 #5
8,834 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, 64 views)
Jun 3 '22 #6
6 Nibble

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.

Sep 7 '22 #7
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, 54 views)
Sep 18 '22 #8
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?

Sep 21 '22 #9
8,834 Expert 8TB
Please clarify indicating exactly what is NOT to be duplicated.
Sep 21 '22 #10
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.

Sep 21 '22 #11
8,834 Expert 8TB
If I understand you correctly, then:
Attached Files
File Type: zip Training Records_Revision_3.zip (86.6 KB, 50 views)
Sep 22 '22 #12
6 Nibble

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

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

Sep 23 '22 #13
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

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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

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.