Insert n number of records in another Table based on number entered in Textbox of Form

[codehunter]

I have a Table (Rake) in which I am having Fields – RakeID, Track, Section and Hole. Based on this Table I have a Form in which I have to enter data. There is another Table Goods which is also having same Fields -- RakeID, Track and Section and now comes more sub Fields based on Hole Field in Rake Table. The subfields will be Hole Diameter, Hole Height, Holelength. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have placed a Button just in front of Hole Field (Textbox) in Form having data source from Rake Table. Now, what I want is that suppose if put “5” in Hole Field Text Box and when I click the Button, it should insert a new single or 5 records at once in Goods Table which should reflect in the Form attached with Goods table. The information contained in the common fields of Rake Table should automatically come in Goods Table on the click of the button. Now the subfields related with Hole field will be filled up. When all the 5 records are filled up and again when the user enters another number in Hole Field (Text Box) of form it should again do the same process as above copying the common field records in Goods table/Form.
This may seem little difficult, but a try/idea from this forum is expected.

 

[codehunter]

OK. Let me more simplify the question. Please tell me how to insert n number of records in another table based on value entered in text box of another/main table. Any help.

 

[Kreszch68]

I still didn’t figure out why you want this (you don’t really need to have fields twice in your database, and you can link the two tables as a parent-child) , but what you need is a simple insert statement. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The code below is the absolute most simple form, but I think it will do looking at the data you’re processing.<o></o>
To avoid errors, set the input mask of the HoleField to 9 or 99 and the default value to 0 (zero).<o></o>

Private Sub btn_InsertNRecords_Click()
 
Dim iNToInsert As Integer
 
DoCmd.SetWarnings False
If Me.HoleField.Value > 0 Then
    If Not IsNull(Me.RakeID.Value) Then 'check if there is a current record
        For iNToInsert = 1 To Me.HoleField.Value
            DoCmd.RunSQL (SQL_InsertFromRake(Me.RakeID.Value))
        Next iNToInsert
    End If
End If
DoCmd.SetWarnings True
End Sub

Public Function SQL_InsertFromRake(ByVal lRecID As Long) As String
SQL_InsertFromRake = "Insert Into Goods (RakeID, Track, Section, Hole) " _
                   & "Select RakeID, Track, Section, Hole From Rake " _
                   & "Where RakeID = " & lRecID
End Function

Of course if you have a button with another name, copy the code into the click event of your button.