sqlexcelvbaclassmethods

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!