add n number of records to child tables

[jclnewbie]

Hi All
Several years ago I had one of your experts help me with the following procedure and it worked great. It took the primary key from the parent table and populated it "n number of times" into the 2nd table related table. When I tabbed out of the NumRecords and re-queried the form/subform, I was able to see the additional records in the 2nd table
Private Sub NumRecords_LostFocus()
Dim indx As Integer, Nkey As Integer, returnOkay As String
indx = Me.[NumRecords]
Nkey = Me.Batch
returnOkay = AddRecord(indx, Nkey)
End Sub

Function AddRecord(indx As Integer, Nkey As Integer) As String
Dim rmst As Recordset, indx1 As Integer
Set rmst = CurrentDb.OpenRecordset("tblSamplePLM", dbOpenDynaset, dbSeeChanges)
For indx1 = 1 To indx
rmst.AddNew
rmst![Batch] = Nkey
rmst.Update
Next indx1
rmst.Close
AddRecord = "Aokay"
End Function

I want to do something similar but find that the old procedure will work on only one sub table\subform at a time. Is there a way to populate the 5 child tables with only one record using both the primary key and the 2nd field StructureIdent from the parent table data entry form selection?


Here is a screen capture my present setup and relationships.
Another issue I have, and this could be because of my lack of knowledge in access but any query based on all five child tables is not updatable. Is this done with union queries?
Hope I've given you all the details you need and thank you in advance.

 

[kjv1611]

First off, your attachments didn't work the way you think they did. Unless something has changed to which I'm unaware, you'll need to upload any attachments to an online file storage site where you can easily share via link.

Secondly, I've not seen the code (if you can find the link easily, it wouldn't hurt pasting a link of it here), but sounds like you just need to slightly modify the code so that one procedure calls another or else you run the append/writing portion within another loop which loops through tables. For instance:

Sub DoSomething
  Dim db as DAO.Database
  Dim tdf AS DAO.TableRef
  Set db = CurrentDb

  For Each tdf in db.TableDefs
    If left(tdf.name,1) = "~" Then
      [green]'Do Nothing[/green]
    ElseIf Left(tdf.name,4) = "msys" Then
      [green]'Do Nothing[/green]
    Else
      [green][/green]'Do stuff
    End If
  Next tdf

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[TheAceMan1]

How are ya jclnewbie . . .

The following in centered around an Insert query and a list of the table names which are inserted into the SQL of the query. If the records are to be synchronized the foreignkeys of your child table should have matching primarykeys in the parent table.This means you'll be inserting the PK into the FK. The function that follows needs to reside in the module of the parent table. You could setup a button to call it. Note that the number of records to be inserted has to be supplied to the function.

I don't quite understand your inserting records this way ... but if you must ...

[blue]Public Function InsertRecs(RowMax As Long)
   Dim db As DAO.Database, SQL As String
   Dim x As Integer, Row As Long, tblName As String
   
   Set db = CurrentDb
   
   For x = 1 To 5
      tblName = Choose(x, "tbl1", "tbl2", "tbl3", "tbl4", "tbl5")
      SQL = "INSERT INTO " & tblName & " (ClientID) " & _
            "VALUES (" & Me.ClientID & ");"
      
      For Row = 1 To RowMax
         db.Execute SQL, dbFailOnError
         DoEvents
      Next
      
      Debug.Print SQL
   Next
   
   Set db = Nothing
   
End Function[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!