CODEWITHSUNDEEP
ms-accessvba
Chemistpp
Chemistpp

Reputation: 2056

VBA Access Object Type Returned For Combobox Field

How can I iterate a record set that returns a field of type field2? Is there a way to tell how many objects are in the field2 type?

Let me describe the relevant aspects of my table:

enter image description here

The table fields has field NMR which contains a list of possible options a user can select in another table. In the Experiments table, the field NMR is a combobox with populates the options from the other table.

enter image description here

The way I do this is in the Experiments table design, I have set the field this way:

enter image description here

Now in one of my forms, I need to read the value in Experiments!NMR which can be a multiple selections allowed combobox. The recordset rs!NMR is of type Field2.

To get the values, you iterate using an integer (i.e. rs!NMR(0) would return the first selected option). The problem is I don't know how to get the field count and calling !NMR(i) where i is greater than the number of elements will invoke a Run time error '3265', Object doesn't exist in this collection.

Their exist a size method only returns the field width size (4?) and the documentation states this is the size of the data type within the field2 object.

There doesn't seem to be a count method associated with field2 as using !NMR.Count invokes runtime error 438, Object doesn't support this method.

Code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qry As String

qry = "SELECT * FROM Experiments"

Set db = CurrentDb
Set rs = db.OpenRecordset(qry, dbOpenSnapshot)

With rs
    Do While Not .EOF
        Dim i As Integer
        For i = 0 to !NMR.Count ' or SOMETHING - this is the problem
            ' this is irrelevant, I need to know how to iterate the list               
        Next i
        .MoveNext
    Loop

End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

I've also tried logic control such as

Do While(!NMR(i) <> vbNullString) since the individual components are strings, but no luck. This issues the same 3265: Item isn't found error. Same for a loop with this check Do While Not IsNull(!NMR(i))

Is there a way to tell how many objects are in the Field !NMR?

Upvotes: 0

Views: 273

Answers (1)

Kostas K.
Kostas K.

Reputation: 8508

You need to assign the complex Field2 to a Recordset2 object and loop through it.

Sub Whatever()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsComplex As DAO.Recordset2
    Dim qry As String

    qry = "SELECT * FROM Experiments"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(qry, dbOpenSnapshot)

    Do While Not rs.EOF

        Set rsComplex = rs("NMR").Value
            rsComplex.MoveLast
            rsComplex.MoveFirst

        Dim i As Integer
        For i = 0 To rsComplex.RecordCount - 1 ' Asker modified
            Debug.Print rsComplex(0)
            rsComplex.MoveNext
        Next i

        rsComplex.Close
        Set rsComplex = Nothing
        rs.MoveNext
    Loop

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Upvotes: 2

Related Questions