If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Duplicating reocords from main form and subform receiving Runtime Error 3022
I am trying to develop a command button that will duplicate the main form and
the subform information and save it to another record within the same tables. I used Allen Browne's program and modified it to fit my tables. At first it would copy the main form and not the subform data so I made some changes and now I am getting the Runtime error 3022. I have worked on this off and on for about 2 weeks. Any help on getting this to run will be greatly appreciated. Below is my code: Private Sub Command78_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main form record and related records in the subform. Dim strSql As String 'SQL statement. Dim lngID As Long 'Primary key value of the new record. 'Save and edits first If Me.Dirty Then Me.Dirty = False End If 'Make sure there is a record to duplicate. If Me.NewRecord Then MsgBox "Select the record to duplicate." Else 'Duplicate the main record: add to form's clone. With Me.RecordsetClone .AddNew ![Group Name] = Me.[Group Name] ![effective date] = Me.[effective date] ![AEID] = Me.[AEID] ![TypeID] = Me.[TypeID] ![Market SegmentID] = Me.[Market SegmentID] ![UWID] = Me.[UWID] ![#subs] = Me.[#subs] ![Assigned] = Me.[Assigned] '![SAID] = Me.SAID ![EZApps] = Me.EZApps ![expected due date] = Me.[expected due date] ![Comments] = Me.[Comments] '![Completed] = Me.Completed ![Rush] = Me.Rush ![date created] = Me.[date created] ![created by] = Me.[created by] ![date updated] = Me.[date updated] ![updated by] = Me.[updated by] .Update 'Save the primary key value, to use as the foreign key for the related records. .Bookmark = .LastModified lngID = !RecordID 'Duplicate the related records: append query. If Me.[F_Entry Status Subform].Form.RecordsetClone.RecordCount 0 Then strSql = "INSERT INTO [T_Entry Status Subform] ( [EntryRecordID], [Created By], [Date], [Hold StatusId], [Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By] ) " & "Select " & lngID & " As EntryRecordID, [Created By], [Date], [Hold StatusID], [Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By]" & "FROM [T_Entry status subform] WHERE EntryrecordID = " & Me. RecordID & ";" Debug.Print strSql DBEngine(0)(0).Execute strSql, dbFailOnError Else MsgBox "Main record duplicated, but there were no related records." End If 'Display the new duplicate. Me.Bookmark = .LastModified End With End If Set db = Nothing Exit_Handler: Exit Sub Err_Handler: MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click" Resume Exit_Handler End Sub |
#2
|
|||
|
|||
Duplicating reocords from main form and subform receiving Runtime Error 3022
I got it to work. Thanks
lroberson wrote: I am trying to develop a command button that will duplicate the main form and the subform information and save it to another record within the same tables. I used Allen Browne's program and modified it to fit my tables. At first it would copy the main form and not the subform data so I made some changes and now I am getting the Runtime error 3022. I have worked on this off and on for about 2 weeks. Any help on getting this to run will be greatly appreciated. Below is my code: Private Sub Command78_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main form record and related records in the subform. Dim strSql As String 'SQL statement. Dim lngID As Long 'Primary key value of the new record. 'Save and edits first If Me.Dirty Then Me.Dirty = False End If 'Make sure there is a record to duplicate. If Me.NewRecord Then MsgBox "Select the record to duplicate." Else 'Duplicate the main record: add to form's clone. With Me.RecordsetClone .AddNew ![Group Name] = Me.[Group Name] ![effective date] = Me.[effective date] ![AEID] = Me.[AEID] ![TypeID] = Me.[TypeID] ![Market SegmentID] = Me.[Market SegmentID] ![UWID] = Me.[UWID] ![#subs] = Me.[#subs] ![Assigned] = Me.[Assigned] '![SAID] = Me.SAID ![EZApps] = Me.EZApps ![expected due date] = Me.[expected due date] ![Comments] = Me.[Comments] '![Completed] = Me.Completed ![Rush] = Me.Rush ![date created] = Me.[date created] ![created by] = Me.[created by] ![date updated] = Me.[date updated] ![updated by] = Me.[updated by] .Update 'Save the primary key value, to use as the foreign key for the related records. .Bookmark = .LastModified lngID = !RecordID 'Duplicate the related records: append query. If Me.[F_Entry Status Subform].Form.RecordsetClone.RecordCount 0 Then strSql = "INSERT INTO [T_Entry Status Subform] ( [EntryRecordID], [Created By], [Date], [Hold StatusId], [Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By] ) " & "Select " & lngID & " As EntryRecordID, [Created By], [Date], [Hold StatusID], [Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By]" & "FROM [T_Entry status subform] WHERE EntryrecordID = " & Me. RecordID & ";" Debug.Print strSql DBEngine(0)(0).Execute strSql, dbFailOnError Else MsgBox "Main record duplicated, but there were no related records." End If 'Display the new duplicate. Me.Bookmark = .LastModified End With End If Set db = Nothing Exit_Handler: Exit Sub Err_Handler: MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click" Resume Exit_Handler End Sub |
#3
|
|||
|
|||
Duplicating reocords from main form and subform receiving Runtime Error 3022
You are describing a "how", as in how you want to accomplish something
(i.e., by apparently duplicating data in a table). In a well-normalized relational database, you wouldn't need to duplicate data in a table. If you'll describe a bit more specifically "what" you would be able to do if you had this duplicated data, folks here may be able to offer more specific suggestions, including approaches that could give you better use of Access' relationally-oriented features/functions. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "lroberson" u58386@uwe wrote in message news:a412e528c4ba3@uwe... I am trying to develop a command button that will duplicate the main form and the subform information and save it to another record within the same tables. I used Allen Browne's program and modified it to fit my tables. At first it would copy the main form and not the subform data so I made some changes and now I am getting the Runtime error 3022. I have worked on this off and on for about 2 weeks. Any help on getting this to run will be greatly appreciated. Below is my code: Private Sub Command78_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main form record and related records in the subform. Dim strSql As String 'SQL statement. Dim lngID As Long 'Primary key value of the new record. 'Save and edits first If Me.Dirty Then Me.Dirty = False End If 'Make sure there is a record to duplicate. If Me.NewRecord Then MsgBox "Select the record to duplicate." Else 'Duplicate the main record: add to form's clone. With Me.RecordsetClone .AddNew ![Group Name] = Me.[Group Name] ![effective date] = Me.[effective date] ![AEID] = Me.[AEID] ![TypeID] = Me.[TypeID] ![Market SegmentID] = Me.[Market SegmentID] ![UWID] = Me.[UWID] ![#subs] = Me.[#subs] ![Assigned] = Me.[Assigned] '![SAID] = Me.SAID ![EZApps] = Me.EZApps ![expected due date] = Me.[expected due date] ![Comments] = Me.[Comments] '![Completed] = Me.Completed ![Rush] = Me.Rush ![date created] = Me.[date created] ![created by] = Me.[created by] ![date updated] = Me.[date updated] ![updated by] = Me.[updated by] .Update 'Save the primary key value, to use as the foreign key for the related records. .Bookmark = .LastModified lngID = !RecordID 'Duplicate the related records: append query. If Me.[F_Entry Status Subform].Form.RecordsetClone.RecordCount 0 Then strSql = "INSERT INTO [T_Entry Status Subform] ( [EntryRecordID], [Created By], [Date], [Hold StatusId], [Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By] ) " & "Select " & lngID & " As EntryRecordID, [Created By], [Date], [Hold StatusID], [Sent to UW], [Date Sent to UW], [Comments], [RecordID], [Date Updated], [Updated By]" & "FROM [T_Entry status subform] WHERE EntryrecordID = " & Me. RecordID & ";" Debug.Print strSql DBEngine(0)(0).Execute strSql, dbFailOnError Else MsgBox "Main record duplicated, but there were no related records." End If 'Display the new duplicate. Me.Bookmark = .LastModified End With End If Set db = Nothing Exit_Handler: Exit Sub Err_Handler: MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click" Resume Exit_Handler End Sub |
Thread Tools | |
Display Modes | |
|
|