How to Save Data into a Combo Box Via VBA Avoiding "Data Type Conversion Error"?

Question

I have one table in Access with some combo box fields (combo box as row source). each combo box has 2 columns (1st one is integer type and 2nd one is text type). what is visible to the user is the 2nd column. 1st one is the bound column.

If I open the table and type the text related to the list, it doesn't cause any error. What I want to do is to fill the combo box via VBA but, with text, instead of integer number.

In the first combo box, for instance, there are 2 options visible to the user: "C" and "V". "C" in the bound column is 1 and "V" is 2. I need to send the character to the combo box and save it. When I try to enter the text, "Data Type Conversion Error" happens. what can I do, in this case?

Thanks in advance.

Code:

Public Sub SalvarAreaRangeNoBD(registro As DAO.Recordset, areaRange As Range)
    Dim totalLin, l As Integer
    Dim totalCol, c As Integer
    Dim deletar As Boolean

    totalLin = areaRange.Rows.Count
    totalCol = areaRange.Columns.Count
    deletar = True

    Call mdlUtil.LimparRegistro(registro)
    registro.AddNew

    For l = 1 To totalLin
        For c = 1 To totalCol
            If ((areaRange.Cells(l, c) & "") = "") Then
                registro.Fields(c - 1).Value = Empty
            Else
                registro.Fields(c - 1).Value = areaRange.Cells(l, c)

                If (deletar <> False) Then
                    deletar = False
                End If
            End If
        Next c

        If (deletar) Then
            registro.CancelUpdate
        Else
            registro.Update
        End If

        registro.AddNew
        deletar = True
    Next l
End Sub

@INOPIAE I tryed to write the same text in a separate table and had the same problem. Access is not allowing to write text but only the equivalent integer number from the bound column. eg. instead of "C", write 1. what happens is that this data comes from Excel spreadsheet, I cannot search for the equivalent integer number. — MyThorRJ
Where do you use the combobox? In a form, on a worksheet? If you talk about bound column how do you populate the combobox? — INOPIAE
@INOPIAE the combobox is in fact a table field linked to another table field. RowSourceType is "Table/Query". RowSource is a SQL. Display Control is "Combo Box". — MyThorRJ
when you open a data base, is it really necessary to close it?or VBA has a routine for doing that? how can I check how many data bases are open actually? — MyThorRJ

C. White C. White · Accepted Answer · 2016-05-15T22:32:34

Open the table to which you're trying to write the data and look at the field with the ComboBox lookup. What is the datatype?

I can almost guarantee that it is a Number-->Long Integer.

The problem you're running into is that the ComboBox makes it look like you're storing a string value, but it's really storing the integer value in column 1 of the ComboBox, but displaying the human-readable string value in column 2.

What you need to do is look up the integer values. If the ComboBox's recordsource is bound to another table, that's super easy. If it's a value list, that's a little bit more annoying, but still totally doable.

Here's an example:

Public Sub SalvarAreaRangeNoBD(registro As DAO.Recordset, areaRange As Range)
  Dim totalLin, l As Integer
  Dim totalCol, c As Integer
  Dim cellValue as Variant

  'New code. Be sure to add a reference to Microsoft Scripting Runtime:
  Dim simpleLookup as Scripting.Dictionary

  set simpleLookup=getLookup("LookupTableName","DisplayFieldName","NumberFieldName")

  totalLin = areaRange.Rows.Count
  totalCol = areaRange.Columns.Count

  Call mdlUtil.LimparRegistro(registro)
  registro.AddNew

  For l = 1 To totalLin
    For c = 1 To totalCol
      cellValue=areaRange.Cells(l, c)
      'If ((cellValue & "") = "") Then
      '  'Do nothing
      'Else
        If simpleLookup.exists (cellValue) then
          'Only add the row to the recordset if you're actually going to use it.
          registro.AddNew
          registro.Fields(c - 1).Value = simpleLookup(cellValue)
          registro.Update
        Else
          'Do nothing
        End If
      'End If
    Next c
  Next l
End Sub

Public Function getLookup(tableName as String, displayField as String, recordField as String) as Scripting.Dictionary
  dim rs as DAO.Recordset
  Set getLookup = new Scripting.Dictionary
  Set rs = CurrentDB.OpenRecordset(tableName)
  With rs
    Do Until .eof
      getLookup.Add .fields(displayField).value, .fields(recordField)
      .MoveNext
    Loop
  End With
End Function

How to Save Data into a Combo Box Via VBA Avoiding "Data Type Conversion Error"?

1 Answers