I am trying to write a macro in which I'd like to fill a combobox on a Userform with the results of an SQL query. The query returns two columns: a list of device IDs (integers) and the device names in a column named description. I have several issues:

1. I have module with the code to connect to the database, which was working fine for a while. I am trying to test the code by using the "Step Into" tool on the debug section of VBA. Previously when my macro got to the Application.Run ... row it jumped over to the window of the module without an issue. Now it gives me various runtime errors (the most recent one is '-2147352571 (80020005)': Type mismatch). If I put a break on the code after the Application.Run ... it executes the connection without an issue. Same if I use the step into on the connection module's code.
2. I put some expressions in the Watches window to try to figure out how to adjust my code. I am tryin to populate the combobox with a for loop. The rst.Getrows returns a drop-down expression, when expanded it shows GetRows(0) and GetRows(1). Each are filled with the device ID numbers and descriptions respectively, with each element having an additional parameter, eg. GetRows(0,2). Sometimes the GetRows expression returns the error in the value: <Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record>, and every additional specified version of it gets an Obejct required or Operation is not allowed in this context error too. This happens when I run the code with a break in it (initially showing the expressions with proper values), and drag a new expression in the watches window without any action on the code itself.

The first issue is mainly complicating my troubleshooting. When the step into method was still working, I could see that the GetRows expression filled up with the correct values after the rst.Open strMysql, conn line, then changed to the BOF/EOF error line once the code continued into the For loop, even though I had no further manipulations on the opened recordset.

Even in a state when the expressions are correctly displayed, I cannot manage to figure out how to formulate the parameters to retrieve one description value. I've been trying with the rst.Getrows(1,i,1).Value with the intent of returning 1 result on the i-th row of the second field. My goal would be to use this in the for loop after the Me.combobox.Additem command.

Could anyone help with either the weird code break issues, or the formulation of the code to fill in the combobox?

CodePudding user response：

Here is a template for how to fill a ComboBox from a RecordSet:

Private Sub Test()
   Set Results = "some SQL query"
   Combo.Clear
   
   Do While Not (Results.BOF Or Results.EOF)
      Combo.AddItem Results.Fields(1).Value
      Results.MoveNext
   Loop
   
   Combo.ListIndex = 0
End Sub