0

I am quite new to VBA and SQL, and I am trying to populate a checkbox on a userform in Word's VBA with the column names of a SQL database. I have managed to verify the connection and list the elements in a table separately, now I just wanted to have them in the checkbox. The initializaiton has the following code:

Option Explicit

Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection

Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = 0 To rst.Fields.Count
        Me.cboParameter.AddItem rst.Fields(i).Name
    Next i

End Sub

When I try to run the private sub I get the run-time error 91 without any lines being highlighted. Any tips on what the problem might be?

xvpnkr
  • 35
  • 1
  • 6
  • 1
    My bad, I added the remaining lines before the private sub. I broke line 3, and when I hover the rst.Fields.Count I get the – xvpnkr Sep 13 '21 at 09:54
  • Yep, that's what error 91 means (Object variable or With block variable not set). Glad it helped. – ed2 Sep 13 '21 at 09:58
  • `without any lines being highlighted` - https://stackoverflow.com/q/12687105/11683 – GSerg Sep 13 '21 at 10:02
  • You never initialize `conn` or `rst`. Naturally you get error 91. – GSerg Sep 13 '21 at 10:03
  • So I need to define my connection string and string of mySQL code to query the table in each private sub? – xvpnkr Sep 13 '21 at 10:05
  • If you insist on storing a shared connection instance in a global variable, then no, but you need to initialize it once, which you are not doing anywhere. What you should do instead is to have a function that returns a connection, and call that from each sub, instead of storing a cached object. – GSerg Sep 13 '21 at 10:12
  • By copying the initializing connection and querying the database parts before wanting to get fields count and names i get Run Time Error 3265 – Item not found in this collection. If I break the code before Next i I can see it gets the values for the Fields.Count, and even the name of the first item in Fields(i).Name – xvpnkr Sep 13 '21 at 10:14
  • Because you are counting to `rst.Fields.Count` and should be counting to `rst.Fields.Count - 1`? – GSerg Sep 13 '21 at 10:17
  • Ah indeed, I don't know where I lost that from the initial working code. It works! I will try to make it prettier by trying to make a function to reference for the connection. Thanks a lot for your patience, it is quite hard to juggle 2 new programming languages and finding solutions :) – xvpnkr Sep 13 '21 at 10:23

0 Answers0