VBA: Error in populating bulk records from array into combobox

[inddon]

Hello There,

I am encountering an error for the below code. The error is as follows:

Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

When I say 'Debug', the control rests on rst.MoveFirst For some records it works fine and the records are fetched into the combobox

Could you please advise on how to overcome this error?


Thanks & regards
Don

Sub PopulateBurgerComboBox(CBuyerName As String, CBuyerVATNumber As String, Target As Range)

Dim tb As ListObject, pth As String, ColumnHeadingsCount As Integer
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strCon As String, Header As Boolean, MasterSheet As Worksheet, BurgerFileName As String


Set cn = New ADODB.Connection
pth = ActiveWorkbook.Path
Set MasterSheet = Worksheets("Master Data")


If IsEmpty(CBuyerName) Then Exit Sub
  With MasterSheet
  BurgerFileName = .Range("T6").Value ' picks up the value of Path and File name of Burge reference
  End With

  If BurgerFileName = "" Then
  MsgBox "Please fill in the name of the Burger File including its extension in Worksheet 'Master Data'", vbOKOnly + vbCritical, "Missing Burger Rates File Name"
  Exit Sub
  End If
   
   
  strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
  "Data Source=" & pth & "\" & BurgerFileName & ";" & _
  "Extended Properties=""Excel 12.0;HDR=Yes"";"

  cn.Open strCon

  strQuery = "SELECT [Party Name],[Country],[City],[Pick-up],[Mode],[Empty Depot], [Local THC Rotterdam], [Gross Tonnage], [Terminal], [Transport], [WHS xdock], [FTL] FROM [Sheet1$A:R] WHERE [Party Name]='" & CBuyerName & _
  "' AND  [VAT Number]='" & CBuyerVATNumber & "';"
   
  ', [Pick-up] & "" - "" & [Mode] & "" - "" & [Empty Depot] As [Type]
  'strQuery = "SELECT [Party Name] & "" - """ & [Country] & "" - """ [City] As [PCC], [Local THC Rotterdam], [Gross Tonnage], [Terminal], [Transport], [WHS xdock], [FTL] FROM [Sheet1$A:R] WHERE [Party Name]='" & CBuyerName & _
  '  "' AND  [VAT Number]='" & CBuyerVATNumber & "';"
   

  Set rst = New ADODB.Recordset
  rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
   

  rst.MoveFirst
  ActiveSheet.ComboBoxDisplayBurger.Column = rst.GetRows


  rst.Close
  Set rst = Nothing
  Set cn = Nothing
End Sub

 

[Deepak]

Loop here!!

http://bytes.com/topic/access/insights/794117-retrieving-data-ado-recordset-using-getrows

http://forum.chandoo.org/threads/vb...ords-in-combobox-from-another-workbook.19294/