By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,056 Members | 1,320 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,056 IT Pros & Developers. It's quick & easy.

Adding all rows record in other subform

P: 1
I have two sub forms, I want to add rows record from one subform to another sub form plz help me.

Attached Images
File Type: jpg Untitled2.jpg (126.2 KB, 232 views)
3 Weeks Ago #1
Share this Question
Share on Google+
3 Replies

P: 18
Use the RecordsetClone of each subform. Something like this:

Expand|Select|Wrap|Line Numbers
  1. Public Sub CopyRecords()
  3.   Dim rstSource   As DAO.Recordset
  4.   Dim rstInsert   As DAO.Recordset
  5.   Dim fld         As DAO.Field
  6.   Dim lngLoop     As Long
  7.   Dim lngCount    As Long
  9.   Set rstInsert = Me!NameOfSubformCONTROL1.From.RecordsetClone
  10.   Set rstSource = Me!NameOfSubformCONTROL2.From.RecordsetClone
  11.   With rstSource
  12.     lngCount = .RecordCount
  13.     For lngLoop = 1 To lngCount
  14.       With rstInsert
  15.         .AddNew
  16.           For Each fld In rstSource.Fields
  17.             With fld
  18.               If .Attributes And dbAutoIncrField Then
  19.                 ' Skip Autonumber or GUID field.
  20.               ElseIf .Name = "SomeSpecialField" Then
  21.                 ' Some fields may require a custom value.
  22.                 rstInsert.Fields(.Name).Value = 0 ' Some special value.
  23.               Else
  24.                 ' Copy field content.
  25.                 rstInsert.Fields(.Name).Value = .Value
  26.               End If
  27.             End With
  28.           Next
  29.         .Update
  30.       End With
  31.       .MoveNext
  32.     Next
  33.     rstInsert.Close
  34.     .Close
  35.   End With
  37.   Set rstInsert = Nothing
  38.   Set rstSource = Nothing
  40. End Sub
2 Weeks Ago #2

Expert Mod 15k+
P: 31,494
I should add that it's very rare that such an approach is the most appropriate one for what it looks like you're doing there. Gustav (CactusData & Access MVP) has shown you how to do it if you want to but do consider how best to handle storage of your data. Database Normalisation and Table Structures would be well worth a read for you I suspect.

Hi Gustav.

Great to see you back posting. I never realised you'd been registered here since less than a year after I started here myself :-) Hope all's well in Denmark.

Cheers -Ade.
2 Weeks Ago #3

P: 18
Thanks - and all is well.
I must admit, that I had forgot about until you mentioned it in the Access MVP group.
2 Weeks Ago #4

Post your reply

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