Adding New Records to Subform - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Adding New Records to Subform

Chuck Duarte

3 years ago

I'm trying to add or insert the same quantity of records to a subform based on the Value from "ConfigQty" from the parent and add the text that is shown in "RKDConfig". For example if "ConfigQty" Field has 3; then 3 new records will be created in the subform with the Text shown from the Parent field RKDConfig. In this example "ABC-1" will be shown 3 times one for each new record.

Parent Form has RKDID (PK), RKDConfig, ConfigQty (Value)
Subform (KitUpdatesFSF) has KUID (PK) RKDID (FK), RKDConfig

Note: subform record source is based on a query and has "RKDConfig" coming from RKD Table with all other data "KitUpdates" Table

Here is my current code Code 1. I also tried an "AfterUpdate" event Code:2 On Config Quantity and that didn't work either.

Record Source SQL for subform:
SELECT KitUpdates.KitUpdateID, KitUpdates.RKDID, KitUpdates.Request_ID, RKD.RKDConfig, RKD.ConfigQty, KitUpdates.Status, KitUpdates.Satisfied_On, KitUpdates.Reserved_Date, KitUpdates.Release_Status, KitUpdates.Customer_Code, KitUpdates.ACNo, KitUpdates.NeedDate, KitUpdates.DDD, KitUpdates.LPDD, KitUpdates.BOMPushDate, KitUpdates.Availability, KitUpdates.KM_Comments
FROM RKD INNER JOIN KitUpdates ON RKD.RKDID = KitUpdates.RKDID;

Code: 1
Private Sub Form_Current()
    Dim i As Integer
    Dim numRecords As Integer

    If Not IsNull(Me.Parent.ConfigQty.Value) Then
        numRecords = Me.Parent.ConfigQty.Value

        For i = 1 To numRecords
            If Not Me.NewRecord Then
                DoCmd.GoToRecord , , acNewRec
            End If
        Next i
    End If
End Sub

Code: 2

Private Sub ConfigQty_AfterUpdate()

    Dim qty As Integer
    Dim i As Integer

    qty = Me.ConfigQty.Value

    ' Clear existing records in KitUpdates subform
    ' DoCmd.RunSQL "DELETE FROM KitUpdatesFSF"

    ' Add new records to KitUpdates subform
    For i = 1 To qty
        DoCmd.RunSQL "INSERT INTO KitUpdatesFSF (RKDConfig) SELECT qryKitUpdates.RKDConfig FROM qryKitUpdates WHERE qryKitUpdates.RKDID = " & Me.RKDID.Value
    Next i

End Sub

Please help I've tried many things and nothing seems to work.
Let me know if you have any questions.

Kevin Robertson @Reply

3 years ago

First thing to do, since the Record Source for the Subform comes from multiple tables, make sure the Recordset is updateable

I did it with a button on both the Parent Form and the Subform.
If you see the blank new record in the Subform, try this:

Button on Parent Form

    Dim i As Long

  

    Me.Refresh

      

    If Not IsNull(ConfigQty) Then

        For i = 1 To ConfigQty

            KitUpdatesF.SetFocus

            DoCmd.GoToRecord , , acNewRec

            KitUpdatesF.Form!RKDID = RKDID

            KitUpdatesF.Form!RKDConfig = RKDConfig

        Next i

    End If

    

    KitUpdatesF.SetFocus

    DoCmd.GoToRecord , , acNewRec

Button on Subform

    Dim i As Long, numRecords As Long



    numRecords = Parent!ConfigQty

    If numRecords > 0 Then

        For i = 1 To numRecords

            DoCmd.GoToRecord , , acNewRec

            RKDID = Parent!RKDID

            RKDConfig = Parent!RKDConfig

        Next i

    End If



    DoCmd.GoToRecord , , acNewRec

Chuck Duarte OP @Reply

3 years ago

The button from the Parent worked perfect thank you!
One more thing is there a way to have it not allow any additions if the quantity already exists. For example if the ConfigQty is 4 the max amount of rows in the subform cannot exceed 4 with a message box saying something like "You cannot add additional records". The reason would be that there would not be an accidental duplication of records added. In this case the subform cannot be doubled to 8.

Kevin Robertson @Reply

3 years ago

Sure. Is this what you are looking to do?

Private Sub btnAdd_Click()



    Dim i As Integer, myCount As Long

  

    Me.Refresh

    

    If Not Me.NewRecord Then

        myCount = DCount("*", "KitUpdatesT", "RKDID=" & RKDID)

    End If

    

    If myCount >= ConfigQty Then

        MsgBox "Max records reached", vbInformation, "Max Records"

        Exit Sub

    End If

    

    If Not IsNull(ConfigQty) Then

        For i = 1 To ConfigQty

            myCount = myCount + 1

            KitUpdatesF.SetFocus

            DoCmd.GoToRecord , , acNewRec

            KitUpdatesF.Form!RKDID = RKDID

            KitUpdatesF.Form!RKDConfig = RKDConfig

            If myCount = ConfigQty Then Exit For

        Next i

    End If

        

    KitUpdatesF.SetFocus

    DoCmd.GoToRecord , , acNewRec

    

End Sub

You may have to change some things like the names of fields. These are what I used, based on your original post, for the prototype I built to test the code.

	Chuck Duarte OP @Reply 3 years ago
Thank you Kevin! That worked perfect exactly what I was looking for. Thanks for your expertise!!

	Kevin Robertson @Reply 3 years ago
You're welcome.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View