How to read a multi valued checkbox field in VBA?

[Daniel Baumann]

Hello!
I am working on an Access 2007 database which contains a combo box with checkboxes for a multi value data field (I dont like this of course but I cannot change now).
I want to read out the values one after the other in VBA.

So I have this code:

knowledge() As Variant = Me!Knowledge.Form![personal experience].Value
For y = 0 To UBound(knowlege)
    something = knowledge(y)
Next y

But the problem is: If no checkbox is checked, the UBound() function replies with an type mismatch error.

How can I check whether no checkbox is checked?

If I write something like

If Not Me!Knowledge.Form![personal experience].Value = False Then

this will not work if there are checkboxes checked.

[beacon]

Hi Daniel,

Welcome to Bytes!

I don't use 2007, but I did find an Access 2007 VBA (Hennig, Cooper, Griffith, Stein 2007) book at work that has a section on navigating recordsets with multi-value lookup fields. I don't know if it will help with what you're trying to do, but I figured I would try to provide you with something.

The code in the book is not mine and I'm providing this entirely for educational purposes (legal notice). The book's example talks about have a classes table and a students table with a multi-value field to store the classes:

Sub PrintStudentsAndClasses()

     Dim dbs As DAO.Database
     Dim rsStudents As DAO.Recordset2
     Dim rsClasses As DAO.Recordset2
     Dim fld As DAO.Field2

     Set dbs = CurrentDb

     Set rsStudents = dbs.OpenRecordset("tblStudents")

     Do While Not rsStudents.EOF
          Set fld = rsStudents("Classes")
          
          If fld.IsComplex Then
               Set rsClasses = fld.Value
          End If

          If Not (rsClasses.BOF And rsClasses.EOF) Then
               rsClasses.MoveLast
               rsClasses.MoveFirst
          End If

          Debug.Print rsStudents("FirstName") & " " & rsStudents("LastName"), _
               "Number of Classes: " & rsClasses.RecordCount

          Do While Not rsClasses.EOF
               Debug.Print , rsClasses("Value")
               rsClasses.MoveNext
          Loop

          rsClasses.Close
          rs.Students.MoveNext

     Loop

     rsStudents.Close

     Set fld = Nothing
     Set rsStudents = Nothing
     Set dbs = Nothing

End Sub

The book doesn't mention anything about whether or not it handles blank values, but I imagine that since the multi-value field is, in itself, a recordset, that it would contain non-Null and Null values.

Hope this helps and good luck,
beacon

[Daniel Baumann]

Yes, I find the best way in your answer: To get the data out of the Database with an SQL request. The other way (reading the data from the form) is too complicated for me.

[Megalog]

If you're trying to read it off the form (using the current record), you can bring back the full value of the multivalue field using the 'text' property.

For example:

[Forms]![frmTest]![MValues].Text

You could then just test to see if that string is empty and move on.

Edit:
I forgot, that this method requires the form control to have the active focus. Probably why I dont use this method. The code Beacon posted shows the correct way to step through the children records of a multivalue field (which is the method I usually resort to).