Item cannot be found in the collection to the requested name or ordinal

Question

Upon clicking a client from the datagrid, all the clients informaition are loaded in the right side of the form, all seems to be doing pretty well.[https://i.sstatic.net/Mv5ZL.png][1]

and whenever I click the add payment button, it will open the payment form. [https://i.sstatic.net/DmznP.png][2]

but whenever I try to choose another client, it will produce a runtime error '3265'. and when I click the debug button, the fields to be filled seemed to have all the information they needed. Why is this error showing up? [https://i.sstatic.net/YuLg5.png][3]

Here is my code in loading all the clients

Private Sub Form_Load()

Set Connect = New Class1
Set rx = New ADODB.Recordset

rx.Open "SELECT * FROM allclients ORDER by dateCreated ASC", con, 3, 3
lblLNumberRecords.Caption = Format(rx.RecordCount, "###,###,###.##")
Set DatCUSTOMERS.DataSource = rx

Me.Show

clearfields
End Sub

and here is my code when clicking a client:

Private Sub DatCUSTOMERS_RowColChange(LastRow As Variant, ByVal LastCol As Integer)

    Unload frmPaymentRecur

    lblID = rx.Fields("id")
    txtClientID = rx.Fields("clientCode")
    txtFirstName = rx.Fields("fname")
    txtLastName = rx.Fields("lname")
    txtMname = rx.Fields("mname")
    txtExtension = rx.Fields("extName")
    txtPin = rx.Fields("pinNo")
    txtDesignation = rx.Fields("designation")
    txtContactNo = rx.Fields("contactNo")
    txtAddress = rx.Fields("address")
    txtAge = rx.Fields("Age")
    txtSalary = rx.Fields("salary")
    cmbAssociation.Text = rx.Fields("fieldTypeTitle")
    cmbChoices.Text = rx.Fields("fieldNameTitle")
    cmdEdit.Enabled = True
    cmdAddLoan.Enabled = True
    cmdPayments.Enabled = True
    End Sub

here is my code when clicking the add payment button:

Private Sub cmdPayments_Click()
Dim flagClients, dateFlag, loanFlag As Integer
flagClients = 1
dateFlag = 1
loanFlag = 1

frmPaymentRecur.lbldateFlag.Caption = dateFlag
frmPaymentRecur.lblLoanFlag.Caption = loanFlag
frmPaymentRecur.txtClientID = rx.Fields("clientCode")
frmPaymentRecur.txtFolderNo = rx.Fields("folderCode")
frmPaymentRecur.txtFullName = rx.Fields("Name")
frmPaymentRecur.txtAssociation = rx.Fields("fieldNameTitle")
frmPaymentRecur.txtSalary = rx.Fields("salary")
frmPaymentRecur.CmbSelectPaymentType.Text = "All Loans"
frmPaymentRecur.lblFlag = flagClients

If rx.State = 1 Then rx.Close

rx.Open "SELECT DISTINCT(`Loan Type`) FROM paymentview WHERE `Client Code` = '" & Trim$(txtClientID.Caption) & "'", con, 3, 3

With rx
    Do While Not .EOF
        frmPaymentRecur.CmbSelectPaymentType.AddItem ![Loan Type]
        .MoveNext
    Loop
    .Close
End With

frmPaymentRecur.Show

End Sub

Answer 1

In your Add Payment code, at the end, you are closing the rx recordset. It will be empty when you try updating your controls.

You've also populated the recordset with data form a different table, paymentview, where id probably doesn't exist.

You should create a separate recordset to handle your payment code instead of reusing the rx recordset.