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

[TimTDP]

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

 

[Estuardo]

G'd Afternoon Tim,
I don't use DAO at all, i'm not any help there, but i'm wondering why do you have a clone of a combobox source? For what i see in your code all what you need from the combobox is the current selected item. Why to waste memory with a recordset?
Wouldn't be easier

!SupplierReference =Me.frmCustomerProductReturnProducts![cboInvoiceId].Column(3)

Or even faster:
Dim strCol3 As string
'This is out of your loop

strCol3 =Me.frmCustomerProductReturnProducts![cboInvoiceId].Column(3)

'This is inside your loop

!SupplierReference =strCol3

 

[TimTDP]

I have a recordset because I want to loop through each record in form "frmCustomerProductReturnProducts"
The value of "cboInvoiceId" is different for each record

 

[Estuardo]

I didn't notice the cbo loop .
This line
rstProductsToReturn!cboInvoiceId.Column(3)
should be something like
rstProductsToReturn(3) <-- the column by index
or
rstProductsToReturn("ColumnName")<-- the column by Name

This is a recordset clone not a form clone.

 

[gemma-the-husky]

i shouldn't think you can get at the column value of a combo box from just the "value" of the combo box, within a recordset. all the recordset knows is that the bound field is value 2, say - it does not know anything else about what value 2 represents.

if you MUST go that way, you would need a dlookup to get the appropriate value using the rstvalue.

I can't see why you would need to use a random column from a combo box, though.