Adding a new record using VBA

[acmis]

Hi

I am relatively new to using VBA in Access an would appreciated a little help!

I am trying to add a new record via VBA. Actually a series of records. I want the new records to be partially auto-completed by the information on the existing form and subform. The goal is to take the number of weeks in a school year and set up the record when the child is enrolled in the program on the History Report. I want it to loop until the number of weeks in the program equals the number of records generated. My Problem is the number of weeks in a program is stored in another table. I keep hitting a wall trying to pull that information. Below is my latest attempt. I added a subform with the data I needed. Now I am trying to call the data from the subform to fill new records. There are several fields not auto-completed that will be throughout the program.

Any help would be appreciated! I hope this makes sense my brain is fried. Thanks!

Private Sub SetPaymentHistory_Click()


Dim Week
Dim Counter
Dim WeekID


WeekID = 10
Counter = 0


Do Until WeekID = Counter
Set rs = CurrentDb.OpenRecordset("Select * From PaymentHistory")


Week = Counter + 1

With rs
.AddNew
![ChildID] = [Forms]![SessionEnrollmentForm]![ChildID]
![SessionID] = [Forms]![SessionEnrollmentForm]![SessionID]
![ProgramID] = [Forms]![SessionEnrollmentForm]![Program Type]
![WeekID] = [subForms]![SessionDefine-subform]![WeekID] this was my idea but I keep getting an error message here.
![Cost of Program] = [Forms]![SessionEnrollmentForm]![Payment Amount]
![Sliding Scale] = [Forms]![SessionEnrollmentForm]![Sliding Scale]
![Sliding Amount] = [Forms]![SessionEnrollmentForm]![Sliding Amount]
![CCDF] = [Forms]![SessionEnrollmentForm]![CCDF]
![CCDF amount] = [Forms]![SessionEnrollmentForm]![CCDF amount]
.Update
.Close
End With

Counter = Week

Loop

Set rs = Nothing

DoCmd.OpenForm "PaymentHistoryForm", acFormDS, , "ChildID=" & Me.ChildID

End Sub

[pbaldy]

See if correcting the syntax works:

Forms Refer to Form and Subform properties and controls

[acmis]

Thank you!!!
This really helped understand the concept behind it!!

I was missing calling the form then the subform!

Just in case anyone is curious or runs into a similar problem here is the code I ended up with


Dim Week
Dim Counter
Dim WeekID
Dim WeekStart






WeekID = [Forms]![SessionEnrollmentForm]![SessionDefineSub]![WeekID]
Counter = 0


Do Until WeekID = Counter

Week = Counter + 1

With rs
Set rs = CurrentDb.OpenRecordset("Select * From PaymentHistory")
.AddNew
![ChildID] = [Forms]![SessionEnrollmentForm]![ChildID]
![SessionID] = [Forms]![SessionEnrollmentForm]![SessionID]
![ProgramID] = [Forms]![SessionEnrollmentForm]![Program Type]
![WeekID] = Week
![Cost of Program] = [Forms]![SessionEnrollmentForm]![Payment Amount]
![Sliding Scale] = [Forms]![SessionEnrollmentForm]![Sliding Scale]
![Sliding Amount] = [Forms]![SessionEnrollmentForm]![Sliding Amount]
![CCDF] = [Forms]![SessionEnrollmentForm]![CCDF]
![CCDF amount] = [Forms]![SessionEnrollmentForm]![CCDF amount]
.Update
.Close
End With

Counter = Week

Loop

Set rs = Nothing

[pbaldy]

Happy to help!