Type Mismatch Error: SELECT and additem data into ComboBox [SOLVED]

[inddon]

Hello There,

I have the below sql query which retrieves data from another excel file. It gives error messages as
Run-time error '-2147352571 (80020005): could not set the list property type mismatch

for the below query. This happens only for certain records, it seems some of the columns are empty in the source excel file from where it picks up the data from.

Could you please advise on:

1. How to overcome this or any other problems relating to selection and populating the data in the combobox?
2. I am not able to add another extra columns using the command
.List(i, 8) = rst![Extra].... Is there any restriction and naming of the column headers?


Looking forward to hearing from you


Thanks & regards
Don

  strQuery = "SELECT * FROM [Sheet1$A:R] WHERE [Party Name]='" & CBuyerName & _
  "' AND  [VAT Number]='" & CBuyerVATNumber & "';"
  MsgBox strQuery
  Set rst = New ADODB.Recordset
  rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
  
MsgBox ("count is: " & rst.RecordCount)

  Dim i As Integer
  rst.MoveFirst
  i = 0

  
  With ActiveSheet.ComboBoxDisplayBurger
  .Clear
  Do
  .AddItem
  'MsgBox (Len(rst![Party Name]))
  .List(i, 0) = rst![Party Name] '& " - " & rst![Country] & " - " & rst![City]
  '.List(i, 1) = rst![VAT number]
  '.List(i, 1) = rst![Country] & rst![City]
  '.List(i, 2) = rst![City]
  .List(i, 1) = rst![Pick-up]
  .List(i, 2) = rst![Mode]
  .List(i, 3) = rst![Empty Depot]
  .List(i, 4) = rst![Local THC Rotterdam]
  .List(i, 5) = rst![Gross Tonnage]
  .List(i, 6) = rst![Terminal]
  .List(i, 7) = rst![Transport]


  i = i + 1
  rst.MoveNext
  Loop Until .EOF
  .Activate
  .DropDown
  End With

  rst.Close
  Set rst = Nothing
  Set cn = Nothing

 

[inddon]

Hello There,

I have the below sql query which retrieves data from another excel file. It gives error messages as
Run-time error '-2147352571 (80020005): could not set the list property type mismatch

for the below query. This happens only for certain records, it seems some of the columns are empty in the source excel file from where it picks up the data from.

Could you please advise on:

1. How to overcome this or any other problems relating to selection and populating the data in the combobox?
2. I am not able to add another extra columns using the command
.List(i, 8) = rst![Extra].... Is there any restriction and naming of the column headers?


Looking forward to hearing from you


Thanks & regards
Don

  strQuery = "SELECT * FROM [Sheet1$A:R] WHERE [Party Name]='" & CBuyerName & _
  "' AND  [VAT Number]='" & CBuyerVATNumber & "';"
  MsgBox strQuery
  Set rst = New ADODB.Recordset
  rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
 
MsgBox ("count is: " & rst.RecordCount)

  Dim i As Integer
  rst.MoveFirst
  i = 0

 
  With ActiveSheet.ComboBoxDisplayBurger
  .Clear
  Do
  .AddItem
  'MsgBox (Len(rst![Party Name]))
  .List(i, 0) = rst![Party Name] '& " - " & rst![Country] & " - " & rst![City]
  '.List(i, 1) = rst![VAT number]
  '.List(i, 1) = rst![Country] & rst![City]
  '.List(i, 2) = rst![City]
  .List(i, 1) = rst![Pick-up]
  .List(i, 2) = rst![Mode]
  .List(i, 3) = rst![Empty Depot]
  .List(i, 4) = rst![Local THC Rotterdam]
  .List(i, 5) = rst![Gross Tonnage]
  .List(i, 6) = rst![Terminal]
  .List(i, 7) = rst![Transport]


  i = i + 1
  rst.MoveNext
  Loop Until .EOF
  .Activate
  .DropDown
  End With

  rst.Close
  Set rst = Nothing
  Set cn = Nothing

Problem solved:

Instead of this entire code I used the following statement:

ActiveSheet.ComboBoxDisplayBurger.Column = rst.GetRows

Regards
Don