Adding multiple subrecords to another table

twinfair

New member
Local time
Today, 23:24
Joined
Oct 12, 2014
Messages
5
I am self-taught in Access so am not always familiar with technical terms but this is what I have and want to achieve.

I have 4 tables: Table1, Table2, TableA and TableB. Table1 is effectively a master table with Table2 being a sub-table (one to many) with an appropriate linking field (lets call it 'MasterID'). TableA and Table B have the same relationship.

I have a form with Table1 as the source showing a subform listing all related records in Table2. There is a button on the master form that uses AddNew to create a record in TableA based on the data in Table1. I also want the button to add the appropriate number of records to TableB based on the linked records in Table2.

I cannot work out how to select the appropriate records only and add them to the table.
 
Hello,
As you do with the Table 1 and the Table A :
1- You create a Recordset from the Table 2 filtering with the MasterID selectionned.
2- You loop the records and add them with a AddNew method in the Table B.
It's the simpliest method I think.
Have a good continuation.
 
Hello,
As you do with the Table 1 and the Table A :
1- You create a Recordset from the Table 2 filtering with the MasterID selectionned.
2- You loop the records and add them with a AddNew method in the Table B.
It's the simpliest method I think.
Have a good continuation.

Thank you, that is what I hoped to be able to do but my problem is I don't know how to do this. I am not sure what the syntax is of the command to create the filtered recordset and then I am not sure how I loop to add the new records.
 
Can you post your code to copy record form the Table 1 to the Table A and the structures of the Table 2 and Table B ? It can serve as a beginning.
 
This is the code for the button on the Table1 form.

Table2 is ID (Autonumber key field), Master, Detail and Value. TableB is the same plus some more fields. The Master field is the link to the other tables and in TableB will need to reflect the newly created record in TableA.


Private Sub btnAddRecords_Click()

Dim dbs As DAO.Database, rst As DAO.Recordset

' Return Database variable pointing to current database and new recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TableA", dbOpenDynaset)

tmpNewID = DMax("TableA_ID", "TableA") + 1

' Add new record to end of Recordset object.
With rst
.AddNew
!TableA_ID = tmpNewID
!TableA_Name = Table1_Name
!TableA_Memo = Table1_Memo
!TableA_Date = Now
.Update ' Save changes.


' .Move 0, .LastModified
End With

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Need to insert code here to create multiple records in TableB of all
' appropriate records from Table2
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
It is not very clear for me. Can you confirm.
You have following tables with fields :
Table1 : MasterId - Table1_Name - Table1_Memo - ...
Table2 : Table2_Id - MasterId_FK - Detail2 - Value2 - ..which link to Table1 with fields MasterId and MasterId_FK with a 1 to many relation.
TableA : A_Id - TableA_Name - TableA_Memo - TableA_Date - ...
TableB : TableB_Id - A_Id_FK - DetailB - ValueB - which link to TableB with fields A_ID and A_Id_FK with a 1 to many relation.

Theses words Table1_Name, Table1_Memo in your code indicate controls in your form? What is the field that contain the MasterId of the Table1 ? Let's consider it as the control MasterId. Your code will be :
Code:
Private Sub btnAddRecords_Click()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rstB As DAO.Recordset
Dim ssql As String
' Return Database variable pointing to current database and new recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TableA", dbOpenDynaset)
tmpNewId = DMax("TableA_ID", "TableA") + 1
' Add new record to end of Recordset object.
With rst
    .AddNew
    !TableA_ID = tmpNewId
    !TableA_Name = Table1_Name
    !TableA_Memo = Table1_Memo
    !TableA_Date = Now
    .Update ' Save changes.
End With
ssql = "SELECT * FROM Table2 WHERE [MasterId_FK]=" & Me.MasterId
Set rst = dbs.OpenRecordset(ssql, dbOpenDynaset)
Set rstB = dbs.OpenRecordset("TableB", dbOpenDynaset)
With rst 'loop across records of Table2 with the corresponding MasterId
    .MoveFirst
    While Not .EOF
        rstB.AddNew
        rstB![A_Id_FK] = tmpNewId
        rstB![DetailB] = rst![Detail]
        rstB![MemoB] = rst![Memo]
        '...
        rstB.Update
    Wend
End With
rstB.Close: Set rstB = Nothing
rst.Close: Set rst = Nothing
End Sub
 
Thank you for your time. Unfortunately it didn't work at first. I ran it after amending a few field names which I may have confused you over and it seemed to hang. I killed Access and when I went back to it, it had added 716,043 records all identical ie a copy of the first record.

I did an online search and think that the missing statement was an rst.MoveNext before the Wend in order to advance the record. It worked! So for completeness if anyone else is trying to do this my code is now:

Code:
ssql = "SELECT * FROM Table2 WHERE [Table2_Master]=" & Me.Table1_ID
Set rst = dbs.OpenRecordset(ssql, dbOpenDynaset)
Set rstB = dbs.OpenRecordset("TableB", dbOpenDynaset)
With rst 'loop across records of Table2 with the corresponding MasterId
    rst.MoveFirst
    While Not .EOF
        rstB.AddNew
        rstB![TableB_Master] = tmpNewId
        rstB![TableB_Details] = rst![Table2_Details]
        rstB![TableB_Value] = rst![Table2_Value]
'       ...
        rstB.Update

        rst.MoveNext

    Wend
End With
rstB.Close: Set rstB = Nothing
rst.Close: Set rst = Nothing
Note the .movefirst falls over if there are no sub-records associated with the master record so you have to have a trap to check this somewhere or remove the .movefirst which seems to work unless anyone can tell me why it is important (eg is there any time when this sort of select will not start at the first record automatically?).
 
Sorry, if there'is some mistakes in the code because I write it on the fly without test. It's good if you find the solution that is correct.
 
Its fine and why I was doing it all in a test database :). Thanks for your help.
 

Users who are viewing this thread

Back
Top Bottom