Duplicating records in subform

[Albennett]

Hello.

I’m new to writing VBA code but getting there thanks, for the most part, to the excellent advice on this site.

I trying to create the code which will allow all records from a subform to be copied and duplicated back in the original underlying table for that subform.

To give a bit more background, the main form contains records of individual ‘reports’ (held in table called CPG1) and the subform (CPG3_Subform) identifies the ‘milestones’ for each report (the data for which is held in a table called (CPG3). Each milestone (there could be several for each report) is referenced back to corresponding report via its unique ID .The datadase has a command button allowing users to copy a report, in order to an updated report based on it. And I would like this copy process to include the ‘milestones’ held against that ‘report’.

The code I have patched together so far is as follows (line 6-17 are simply included for reference purposes):

1. Private Sub Command28_Click()
2. On Error GoTo Err_Command28_Click

3. Dim var As Variant
4. Dim rst As Recordset
5. Dim PreRPT As Integer

6. 'This section duplicates the latest report held, setting rpt ref and current date.
7. Me.MaxCPG1_subform.Requery
8. PreRPT = [MaxCPG1_subform]![MaxOfRPTRef]
9. DoCmd.RunCommand acCmdSelectRecord
10. DoCmd.RunCommand acCmdCopy
11. DoCmd.RunCommand acCmdRecordsGoToNew
12. DoCmd.RunCommand acCmdSelectRecord
13. DoCmd.RunCommand acCmdPaste
14. ReportDate.Value = Now()
15. RPTRef.Value = PreRPT + 1
16. Me!Combo8.Requery
17. Me.Combo8 = Me.CPG_NoteID

18. 'This section duplicates the milestones



19. Set rst = CurrentDb.OpenRecordset("CPG3", dbOpenDynaset)
20. For Each var In Me.CPG3_subform
21. rst.AddNew
22. rst!CPGID = CPG_NoteID.Value
23. rst!Scheme = CPG3_subform.Column(2, var)
24. rst!Description = CPG3_subform.Column(3, var)
25. rst.Update
26. Next
27. rst.Close
28. Set rst = Nothing


29. Exit_Command28_Click:
30. Exit Sub

31. Err_Command28_Click:
32. MsgBox Err.Description
33. Resume Exit_Command28_Click

34. End Sub

Lines 19-28 are the bits causing the problem (the previous lines work fine to simply copy the report). I’m getting an error message "Method or data member not found".

If this has already been type of query has already been addressed, would mind please pointing me in the right direction. I appreciate I may be going off entirely down the wrong track. I would be most grateful for any suggestions

[Stewart Ross]

Hi. The error message about the method or member not found is because there is more than one type of recordset, and you need to use the DAO object library version. Change your Dim rst to

Dim rst As DAO.Recordset

If you now get a compilation error you will need to add the DAO object library in your project references - from the VB editor select Tools, References, then tick the entry for the DAO x.x object library, where x.x is the highest one of the choices that may be available to you in the list.

I hope you find that your code works as expected after you select the correct recordset type.

-Stewart

[Albennett]

Hi. The error message about the method or member not found is because there is more than one type of recordset, and you need to use the DAO object library version. Change your Dim rst to

Dim rst As DAO.Recordset

If you now get a compilation error you will need to add the DAO object library in your project references - from the VB editor select Tools, References, then tick the entry for the DAO x.x object library, where x.x is the highest one of the choices that may be available to you in the list.

I hope you find that your code works as expected after you select the correct recordset type.

-Stewart

I've added the DAO 3.6 Object Library, but unfortuately I'm still getting the same compliation error: 'method or data member not found'. Error appears to be occuring at line 23.

Regards

Alistair

Any further suggestions please ?

[Stewart Ross]

Hi. In my last post I should also have spotted that your combo column references were invalid (column(n, var)). Replace these with

rst!Scheme = CPG3_subform.Column(2)
rst!Description = CPG3_subform.Column(3)

-Stewart
ps I'm assuming thet these two items are combo columns and not references to controls on a subform. If it is the latter you would have to refer to them like this:

Me![subformname].form![control name].Column(2)

[Albennett]

Hi. In my last post I should also have spotted that your combo column references were invalid (column(n, var)). Replace these with

rst!Scheme = CPG3_subform.Column(2)
rst!Description = CPG3_subform.Column(3)

-Stewart
ps I'm assuming thet these two items are combo columns and not references to controls on a subform. If it is the latter you would have to refer to them like this:

Me![subformname].form![control name].Column(2)

Stewart

Sorry about this.

The latter two are fields within the subform (please excuse my ignorance about the coding). I've made the amendments you outlined above; changing the references for these fields and the column references.

But I'm now getting 'Object doesn't support this property or method' error, and no new records have been added. I tried removing the ".Column(2)"; thinking that I may not need this since I was naming the fields specifically...but that did work either.

Alistair

[Stewart Ross]

...The latter two are fields within the subform (please excuse my ignorance about the coding). I've made the amendments you outlined above; changing the references for these fields and the column references. But I'm now getting 'Object doesn't support this property or method' error, and no new records have been added. I tried removing the ".Column(2)"; thinking that I may not need this since I was naming the fields specifically...but that did work either.

Hi Alistair. Not sure why you need to refer to items on your subform at the same time as items on your main form; this is not normal practice. It begs the question of what you are really trying to do, and I'm somewhat puzzled by it.

Subforms generally reflect one-to-many relationships between the mainform table and the subform table - in referring to the subform which many-side record would you be referring to? Also, the mainform is linked to the subform by common parent/child field links - why is it necessary to refer to the subform at all?

Having said all that, there must be some kind of error in your reference to the subform controls if the 'method...' error arises on those lines. For text boxes and the like you do not need the Column property, as you suspected, and the reference just becomes

me![subformname].form![control name]

As to how to resolve all this, firstly, and most importantly, could consider why you need to refer to a subform value whilst referring to the mainform elswhere in your processing, and, secondly, if you continue to experience errors after you alter names of controls etc could you post the revised code back on your next reply so I can check it?

Thanks

-Stewart

[Albennett]

Hi Alistair. Not sure why you need to refer to items on your subform at the same time as items on your main form; this is not normal practice. It begs the question of what you are really trying to do, and I'm somewhat puzzled by it.

Subforms generally reflect one-to-many relationships between the mainform table and the subform table - in referring to the subform which many-side record would you be referring to? Also, the mainform is linked to the subform by common parent/child field links - why is it necessary to refer to the subform at all?

Having said all that, there must be some kind of error in your reference to the subform controls if the 'method...' error arises on those lines. For text boxes and the like you do not need the Column property, as you suspected, and the reference just becomes

me![subformname].form![control name]

As to how to resolve all this, firstly, and most importantly, could consider why you need to refer to a subform value whilst referring to the mainform elswhere in your processing, and, secondly, if you continue to experience errors after you alter names of controls etc could you post the revised code back on your next reply so I can check it?

Thanks

-Stewart

Hi Stewart

I’ll try to explain what it is I’m trying to achieve. I’ve probably missed something blatantly obvious here and tried to over complicate things!

The part of the database I’m working on captures monitoring information (in the form of a series of ‘reports’) for a programme of projects. Several ‘reports’ will be produced for each project. So, as well as obviously at table of projects, I have a table of ‘reports’ (called CPG1). I have designed a form, based on the CPG1 table to facilitate the compilation of these reports.

A large proportion of the information required for each report will have already been recorded on an earlier report for that scheme. I have therefore given users the option of using that info as the basis of their new report, by allowing them to create a duplicate of that earlier report.

Each report also includes a series of milestones. So I have a separate table of ‘Milestones’(called CPG3). There may be several milestones per report. Perhaps strangely, even though these milestones may be largely the same from report to report, the users would like the milestones to be specific to each report. So at the same time as the report is duplicated I would also like to duplicate the individual milestones attached to that report. Obviously, each report contains its own uniqueID [CPG_NoteID], so as each milestone is copied I need to update the reference to the new version of the report (hence the need for line 22 in my code).

So in summary, there are a series of reports, each of which will have a series of milestones attached. I am trying to allow users to duplicate a report and it’s associated milestones.

Hope this helps

Many thanks for your time and patience so far.

Alistair

[Albennett]

Hi Stewart

I’ll try to explain what it is I’m trying to achieve. I’ve probably missed something blatantly obvious here and tried to over complicate things!

The part of the database I’m working on captures monitoring information (in the form of a series of ‘reports’) for a programme of projects. Several ‘reports’ will be produced for each project. So, as well as obviously at table of projects, I have a table of ‘reports’ (called CPG1). I have designed a form, based on the CPG1 table to facilitate the compilation of these reports.

A large proportion of the information required for each report will have already been recorded on an earlier report for that scheme. I have therefore given users the option of using that info as the basis of their new report, by allowing them to create a duplicate of that earlier report.

Each report also includes a series of milestones. So I have a separate table of ‘Milestones’(called CPG3). There may be several milestones per report. Perhaps strangely, even though these milestones may be largely the same from report to report, the users would like the milestones to be specific to each report. So at the same time as the report is duplicated I would also like to duplicate the individual milestones attached to that report. Obviously, each report contains its own uniqueID [CPG_NoteID], so as each milestone is copied I need to update the reference to the new version of the report (hence the need for line 22 in my code).

So in summary, there are a series of reports, each of which will have a series of milestones attached. I am trying to allow users to duplicate a report and it’s associated milestones.

Hope this helps

Many thanks for your time and patience so far.

Alistair

Code currently stands as follows:

Set rst = CurrentDb.OpenRecordset("CPG3", dbOpenDynaset)
For Each var In Me.CPG3_subform
rst.AddNew
rst!CPGID = Me!CPG_NoteID.Value
rst!Scheme = Me![CPG3_subform].Form![Scheme]
rst!Description = Me![CPG3_subform].Form![Description]
rst.Update
Next
rst.Close
Set rst = Nothing