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".
@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.
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 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 : - 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. - 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).
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
-
@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.
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
Here is the Revision which hopefully contains your requested changes.
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
Please clarify indicating exactly what is NOT to be duplicated.
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
If I understand you correctly, then:
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
Glad to help, good luck with your Project.
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: 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"....
|
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...
|
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...
|
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...
|
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 =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
| |