Insert Record Obstacle

[fenhow]

Hi, I am trying to insert a new record using VB.
It works great however the issue I am having is when I have more than one ID when I run this code it always updates ID1 not ID2, or ID3 and so on.
The table I am updating is a subform on a main form joined by ID. Is there a trick to use the code below but when it is executed it uses the ID of the record I am on?

Many thanks.


Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from ScheduleT")
rec.AddNew
rec("LoanID") = Forms!LoanF!LoanID
rec("PaymentNumber") = DMax("PaymentNumber", "ScheduleT") + 1 ' Use Dmax to find last payment Number Fen Code this
rec("Residual") = Forms!LoanF!Residual
rec("PType") = "Residual"
rec("DueDate") = Forms!LoanF!EndDate ' Dmax here as well for last payment date +?
rec("Principal") = Forms!LoanF!PaymentAmount
rec("Interest") = rec("Residual") - (DMax("Principal", "ScheduleT") + DMin("Interest", "ScheduleT"))


rec.Update
Set rec = Nothing
Set db = Nothing

Forms!LoanF.Requery

 

[The_Doc_Man]

It is not clear where you are attempting this action. Since a subform is involved, I'm going to take a wild-eyed guess. Are you looking at a case where there is a parent form with a sub-form having many child entries? And are you attempting (hoping) to add all of the child entries at once?

The catch is that the parent form's recordset is always focused on a single record. So is the child form's recordset. The fact that multiple records can be displayed is immaterial. Only one record is ever active at a time in a recordset. There is a technical equivocation regarding cloned recordsets, but there is a separate (cloned) recordset variable for each clone, so I stand by the statement. This means that if you are asking for an entry of an particular record, it is fine. But if you are asking for an entry of MORE than one record, you need some code to step through the recordset of the child form.

Now, having said that, I have to say that your question was a bit vague and I took a big inference in analyzing what you asked. If I answered the wrong question, that is a sign that your question was more vague then you might have thought - in which case further clarification is needed.