VBA To Parse Userform Name In Method

I am trying to write less code by learning how to use OOP method. Its been a bit rough for me and have stuck. I would like to know how i will be able to parse the string name of my Userform. I have the following codes in my module called md_AddCountry.

`Sub Select_AddDonor_Country()
 Dim showsql As New cls_DBConPath

 With showsql
     .colname = "country_Name"
     .sqlst = "Select country_Name From ccf_country;"
     .formname = "frmAddDonor.cmbAddDonr_Country"
     .DBConPath

Set showsql = Nothing
End With
End Sub`

i have the following codes in my class module called cls_DBConPath

`Option Explicit
 Private psqlSt As String
 Private pcolumnName As String
 Private pform As Object
 Private pformName As String

 Public Property Get colname() As String
 colname = pcolumnName
 End Property

 Public Property Let colname(Value As String)
 pcolumnName = Value
 End Property

 Public Property Get sqlst() As String
 sqlst = psqlSt
 End Property

 Public Property Let sqlst(Value As String)
 psqlSt = Value
 End Property

 Public Property Get formname() As String
 formname = pformName
 End Property

 Public Property Let formname(Value As String)
 pformName = Value
 End Property

 Public Property Get form() As Object
 form = pform
 End Property

 Public Property Let form(Value As Object)
 pform = Value
 End Property`

`Sub DBConPath()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbPath As String
Dim fName As Object

Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

'On Error GoTo ErrHandler

dbPath = frmCCFDashboard.DBAddress.Caption

Set con = New ADODB.Connection
con.Open dbPath

Set rs = con.Execute(sqlst)

While rs.EOF = False

UserForms.Add(formname).AddItem rs.Fields(colname).Value
UserForms.Add(formname).AddItem rs.Fields(colname).Value
rs.MoveNext

Wend

End Sub  `

I keep on getting an error message of "Object Required" whenever i try parsing the name of my userform in the method i have created above. I will be grateful if you guys can help me on how to parse my userform in the method.

Please forgive me if you find my codes weird. I am trying to learn how to use class and its a whole mouth full for me. I am still grasping the concept

Solution

I'm not sure if I'm entirely understanding the end goal of your code.

From what I'm reading in your md_AddCountry Select_AddDonor_Country subroutine, the goal is to pass string references to a class with those properties and then execute a sql query method using those properties and display the results to a userforms' controls with other properties defined in the objects construction.

It seems you also want the object to be instantiated and cleaned up by the with statement and you have some form object property which I assume is meant to be set as a form to display the content to. I also want to assume you're making this module public to run from the macros button on the excel developer ribbon.

The good news is you're not far off! Classes can be very powerful once mastered and trying OOP in VBA is very useful. This is my first SA answer! We're all doing something new. Hopefully, I use this answer section correctly.

'md_AddCountry

Sub Select_AddDonor_Country()

 With New cls_DBConPath 'you don't need to new the class if you are disposing it
     .colname = "country_Name"
     .sqlst = "Select country_Name From ccf_country;"
     .formname = "frmAddDonor.cmbAddDonr_Country"
     set .form = new frmAddDonor 'if you want to pass the userform to your class
     .DBConPath
     .form.show 'if you want to show your new prepopulated form
 End With

End Sub

'cls_DBConPath

Option Explicit
Private psqlSt As String
Private pcolumnName As String
Private pform As Object
Private pformName As String

Public Property Get colname() As String
 colname = pcolumnName
End Property

Public Property Let colname(Value As String)
 pcolumnName = Value
End Property

Public Property Get sqlst() As String
 sqlst = psqlSt
End Property

Public Property Let sqlst(Value As String)
 psqlSt = Value
End Property

Public Property Get formname() As String
 formname = pformName
End Property

Public Property Let formname(Value As String)
 pformName = Value
End Property

Public Property Get form() As Object 'objects need to be set'
 set form = pform
End Property

Public Property Set form(Value As Object) 'objects need to be set
 set pform = Value
End Property

Sub DBConPath()
  Dim con As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim dbPath As String
  Dim fName As Object

  'Set con = CreateObject("ADODB.Connection") 'you don't need to set here
  Set rs = CreateObject("ADODB.Recordset")

  'On Error GoTo ErrHandler

  dbPath = frmCCFDashboard.DBAddress.Caption

  Set con = New ADODB.Connection 'if you set down here
  con.Open dbPath

  Set rs = con.Execute(sqlst)
  
  Dim control_name as string
  control_name = split(formname, ".")(1) 'split takes a delimiter and creates an
                                         'array; (1) takes the 2nd item of the
                                         'zero based array; (0) would get the
                                         'formname
  While rs.EOF = False
    
    form.Controls(control_name).AddItem rs.Fields(colname).Value '*
    'UserForms.Add(formname).AddItem rs.Fields(colname).Value 'not sure why the
    rs.MoveNext                                               'second one

  Wend

End Sub

*I replaced Userforms.Add, (which I think is used to dynamically add a userform. If that's your goal the above is not the right answer) , with form.Controls. This uses the newly instantiated userform previously passed into the class, that has yet to been shown but exists in memory. Then pass the control_name into the Controls collection and add that fields value!