Code to add multiple records

[rachello89]

I have three related tables. Enrollments, EnrollmentProducts and Payments. I also have a form that allows users to enroll students. The form has a subform where they select the product the students are purchasing. I want to have a code that applies twelve payments to an enrollment product, preferably in the AfterUpdate section of the EnrollmentProduct field. The first payment is equal to the start date; the following payments will be the first day of the following months. Below is a list of the tables and their fields. I would really appreciate help on this as I am very new to VBA code and programming. Thanks!



Enrollments
EnrollmentID EnrollmentDate EnrollmentEndDate EnrollmentName

EnrollmentProduct
EnProdID StartDate EndDate EnrollmentProduct EnrollmentID

Payments
PaymentID EnProdID PaymentDate

[Rod]

Hi,

Here is some code with my comments following.

Option Compare Database
Option Explicit

Private Sub EnrollmentProduct_AfterUpdate()

    Dim rstPayments As DAO.Recordset
    Dim i As Integer
    Dim dtePayment As Date
    
    'Can't do anything if the start date has not been entered
    If IsNull(Me.StartDate) Then Exit Sub
    
    'Can't do anything if EnProdID is null.  This could happen
    'you are creating a new EnrollmentProduct record.  It
    'depends upon your set-up.
    If IsNull(Me.EnProdID) Then Exit Sub
    
    Set rstPayments = CurrentDb.OpenRecordset("Payments")
    With rstPayments
        .AddNew     'Add first payment date
            !EnProdID = Me.EnProdID
            !PaymentDate = Me.StartDate
        .Update
        dtePayment = Me.StartDate
        For i = 1 To 11     'Add the subsequent 11 payment dates
            .AddNew
                !EnProdID = Me.EnProdID
                dtePayment = FirstDayOfNextMonth(dtePayment)
                !PaymentDate = dtePayment
            .Update
        Next
    End With
    Set rstPayments = Nothing

End Sub

Private Function FirstDayOfNextMonth(RvarDate As Variant) As Date
    Dim intMonth As Integer
    Dim intYear As Integer

    If IsDate(RvarDate) Then
        intYear = DatePart("yyyy", DateValue(RvarDate))
        intMonth = DatePart("m", DateValue(RvarDate))
    Else
        Exit Function       'Wrong type of attribute
    End If
    intMonth = intMonth + 1
    If intMonth > 12 Then
        intMonth = 1
        intYear = intYear + 1
    End If
    FirstDayOfMonth = DateSerial(intYear, intMonth, 1)
    
End Function

• I have assumed all your primary keys are long integer autoincrements.
• There is no error handling in my code.
• Before you attempt to add records to the Payments table you must ensure your data is sensible. I have indicated a couple of rules (e.g. the enrollment product id must be present) but have not handled these conditions except for exiting the procedure.
• You may run into trouble if Access has not written (saved) the enrollment product record when you add the payment records. If you have defined db relationships and forced referential integrity then you will be trying to link the payment records to a - as yet - non existent enrollment product.
• Advancing the date has been separated into a private function.
• The private function may not be the most elegant, others may suggest a slicker solution.

Let us know how you get on.