Access VBA - Type Mismatch when working with Combo Boxes (Multi Valued Fields)

Question

I have a form that displays a single record at a time, and allows the record to be edited by displaying it all in Text Boxes and Combo Boxes. Some of these are ComboBoxes based off of lookup fields, where the values are pulled from a preset list (multi-valued fields).

After that, I have a class module with a property defined for each field in the record (a FirstName Property, a LastName Property, an Address Property... you get the idea). I have a function that creates an object from the class, and then takes the values from the form and assigns them to the corresponding property. This works fine for most of the fields, but as soon as it gets to the first Combo Box (multiple selection), it throws a Type Mismatch error. Code I'm using is:

If Me.Issue <> vbNullString Then
ProfileObj.Issue = Me.Issue
End If
'Me.Issue is the combobox on the form - this is in the forms module
'ProfileObj is the class instance

In case you wanted to see the Property in the class module for the ProfileObj object:

Private ProfileIssue As String
'... other variable declarations
Property Get Issue() As String
    Issue = ProfileIssue
End Property
Property Let Issue(rData As String)
    ProfileIssue = rData
End Property

I've also tried using Me.Issue.Value, Me.Issue.Text, and Me.Issue.Column(0) to refer to the Combo Box, but none of these have worked either. I even tried using CStr(Me.Issue), to no avail. How can I take whatever's displayed int the combo box and assign it to a String variable?

A string variable should accept any combination of alpha-numeric characters. It will not accept Null. I have never used a custom class. — June7
I have an If statement checks for Null before it assigns anything so I'm pretty sure that's not the problem — Psychoceramics
I just realized you say comboboxes are bound to multi-value fields. This is likely cause of problem. MVF requires special handling when pulling data out of those fields and comboboxes don't act like normal comboboxes. I NEVER use MVF. — June7

Psychoceramics Psychoceramics · Accepted Answer · 2020-09-01T22:39:47

I figured it out...

I needed to read the text from each Combo Box with each box's .Text property. I had tried that inside of the If statements, but not for the actual comparison that the If statement was built on. The working version of the code now reads:

        Me.Issue.SetFocus 'You have to set focus in order to read the text, dont ask me why
        If Me.Issue.Text <> vbNullString Then 'This is where my code wasn't working
        .Issue = Me.Issue.Text 'I had tried it here before, but the code never got there since the line before failed
        End If

Access VBA - Type Mismatch when working with Combo Boxes (Multi Valued Fields)

1 Answers