Refer to Column(3) of a combo box in a Form.RecordSetClone

[DevelopV]

I need to write data to a table from a sub form, so I using a Form.RecordSetClone.
On the subform I have a combo box called "cboInvoiceId" and I need the data in column 4
This is what I have

Dim db As DAO.Database
Dim intCustomerReturnsShipmentsId As Long
Dim rst As DAO.Recordset
Dim rstProductsToReturn As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select CustomerReturnsShipmentsId from tblCustomerReturnsShipments where CustomerReturnsShipmentsDirection = 2 and CustomerReturnsShipmentsClosed = False and SupplierId = " & Me.cboCustomerReturnsSupplierId)
                    
If rst.RecordCount = 0 Then 'Create New Shipment
    With rst
        .AddNew
        !SupplierId = Me.cboCustomerReturnsSupplierId
        !CustomerReturnsShipmentsDirection = 2
        intCustomerReturnsShipmentsId = rst!CustomerReturnsShipmentsId
        .Update
    End With
    Else
    intCustomerReturnsShipmentsId = rst!CustomerReturnsShipmentsId
End If

Set rst = db.OpenRecordset("Select * from tblCustomerReturnsShipmentsProducts where 1 =0")
Set rstProductsToReturn = Me.frmCustomerProductReturnProducts.Form.RecordsetClone
    With rstProductsToReturn
        .MoveLast
        .MoveFirst
        Do Until .EOF
            With rst
                .AddNew
                !CustomerReturnsShipmentsId = intCustomerReturnsShipmentsId
                !CustomerReturnsProductsId = rstProductsToReturn!CustomerReturnsProductsId
                '!CustomerReturnsId = rstProductsToReturn!CustomerReturnsId
                !CustomerReturnsShipmentsProductsQuantity = rstProductsToReturn!CustomerReturnsProductsQuantity
                !SupplierReference = rstProductsToReturn!cboInvoiceId.Column(3)
                .Update
            End With
            .MoveNext
        Loop
    End With

!SupplierReference = rstProductsToReturn!cboInvoiceId.Column(3) returns the run time error 3265 - Item not found in this collection.

Is the syntax wrong?
How do I refer to cboInvoiceId.Column(3)?

Many thanks

 

[dhookom]

You didn't mention anything in your posting that you understand the column count begins with 0 not 1. I assume this is your error.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Replace this:
Set rstProductsToReturn = Me.frmCustomerProductReturnProducts.Form.RecordsetClone
with this:
Set rstProductsToReturn = Me.frmCustomerProductReturnProducts.Form.Recordset

And this:
!SupplierReference = rstProductsToReturn!cboInvoiceId.Column(3)
with this:
!SupplierReference = Me.frmCustomerProductReturnProducts.Form!cboInvoiceId.Column(3)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DevelopV]

And this:
!SupplierReference = rstProductsToReturn!cboInvoiceId.Column(3)
with this:
!SupplierReference = Me.frmCustomerProductReturnProducts.Form!cboInvoiceId.Column(3)

I am not sure that I understand this!
cboInvoiceId appears on each record in the form (should have mentioned that it is a continuous form)
So I need the value of column(3) on each record.

 

[PHV]

Notice I used Recordset instead of RecordsetClone ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886