468,272 Members | 2,167 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

Duplicating records in subform

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
Mar 12 '08 #1
7 1703
Stewart Ross
2,545 Expert Mod 2GB
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
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 12 '08 #2
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
Expand|Select|Wrap|Line Numbers
  1. 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 ?
Mar 12 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
Hi. In my last post I should also have spotted that your combo column references were invalid (column(n, var)). Replace these with
Expand|Select|Wrap|Line Numbers
  1. rst!Scheme = CPG3_subform.Column(2)
  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:
Expand|Select|Wrap|Line Numbers
  1. Me![subformname].form![control name].Column(2)
Mar 12 '08 #4
Hi. In my last post I should also have spotted that your combo column references were invalid (column(n, var)). Replace these with
Expand|Select|Wrap|Line Numbers
  1. rst!Scheme = CPG3_subform.Column(2)
  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:
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 12 '08 #5
Stewart Ross
2,545 Expert Mod 2GB
...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
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 12 '08 #6
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
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 12 '08 #7
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:

Expand|Select|Wrap|Line Numbers
  1. Set rst = CurrentDb.OpenRecordset("CPG3", dbOpenDynaset)
  2. For Each var In Me.CPG3_subform
  3. rst.AddNew
  4. rst!CPGID = Me!CPG_NoteID.Value
  5. rst!Scheme = Me![CPG3_subform].Form![Scheme]
  6. rst!Description = Me![CPG3_subform].Form![Description]
  7. rst.Update
  8. Next
  9. rst.Close
  10. Set rst = Nothing
Mar 13 '08 #8

Post your reply

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

Similar topics

1 post views Thread by pwys | last post: by
8 posts views Thread by Josetta | last post: by
3 posts views Thread by paquer | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.