Class property not assigning

[breakoutfoo]

Hi,

I have been trying to adapt a class that has been working perfectly fine until I added a new write only property - please see the following:

Private ReturnMode As String
 
Public Property Let Mode(ByVal value As String)
    ReturnMode = value
End Property

No matter what I do I cannot seem to get the propertry to initiate and take in my value resulting in a null string being passed every time. I am calling my class with the following code.

Sub GetRegions()
 
    Dim RegionData As ADOBDataFetch
    Set RegionData = New ADOBDataFetch
 
    RegionData.DbConnection = StructureDbConnection
    RegionData.Sheet = Sheet4_
    RegionData.SQL_String = "SELECT * FROM Region ORDER BY Region"
    RegionData.TargetListBox = Region_lb_
    RegionData.Mode = "XXX"
 
    RegionData.FetchData
 
End Sub

If I put a break point on the property it ignores it entirely and does not break, suggesting that the code of just this property is not even called.

Does anyone have any ideas about what I might be doing to achieve such a problem? The rest of my class is working perfectly. Its driving me insane. The full code for my class can be found below

Cheers

Andy

Public Enum SheetNameEnum
    Sheet4_ = 1
    Sheet2_ = 2
End Enum
'-------------------------------------------------
Public Enum ListBoxNameEnum
    Region_lb_ = 13
    TBM_lb_ = 14
    RD_lb_ = 15
    TAM_lb_ = 16
    Accs_lb_ = 17
    ListBox1_ = 1
End Enum
'-------------------------------------------------
Public Enum ReturnModeEnum
    Sheet_ = 888
    Control_ = 999
End Enum
'-------------------------------------------------
Private cnn As ADODB.Connection
Private rstRecordset As ADODB.Recordset
Private cmdCommand As ADODB.Command
Private TargetSheet As Long, ListBoxNameLng As Long
Private recCount As Integer
Private StructureDbConnection As String, ReturnMode As String
Private SQLString As Variant, ListArray As Variant
 
Private ReturnMode As String
Public Property Get Mode() As String
    Mode = ReturnMode
End Property
Public Property Let Mode(ByVal value As String)
    ReturnMode = value
End Property
Public Property Let Sheet(ByVal value As SheetNameEnum)
    TargetSheet = value
End Property
Public Property Let TargetListBox(ByVal value As ListBoxNameEnum)
    ListBoxNameLng = value
End Property
Public Property Let SQL_String(value As Variant)
    SQLString = value
End Property
Public Property Get DbConnection() As String
    Name = StructureDbConnection
End Property
Public Property Let DbConnection(value As String)
    StructureDbConnection = value
End Property
 
'**********************************************************************************************************************************
'INFORMATION **
'
'Sub routine to query an external data source and return it to either a control or a location on a worksheet
'**********************************************************************************************************************************
Public Sub FetchData()
    MsgBox ReturnMode
    '------------------------------------------------------------------------------------------------------------
    'Open the connection.
 
    Set cnn = New ADODB.Connection
    cnn.Open StructureDbConnection
 
    '------------------------------------------------------------------------------------------------------------
    'Set the command text.
 
    Set cmdCommand = New ADODB.Command
    Set cmdCommand.ActiveConnection = cnn
 
    With cmdCommand
            .CommandText = CStr(SQLString)
            .CommandType = adCmdText
            .Execute
    End With
 
    '------------------------------------------------------------------------------------------------------------
    'Open the recordset.
 
    Set rstRecordset = New ADODB.Recordset
    Set rstRecordset.ActiveConnection = cnn
    rstRecordset.Open cmdCommand
 
    '------------------------------------------------------------------------------------------------------------
    'Put recordset result into an array so we can loop through them
 
    On Error Resume Next
 
    ListArray = rstRecordset.GetRows
    recCount = UBound(ListArray, 2)
 
    On Error GoTo 0
 
    ReturnMode = 1
 
    '----------------------------------------------------------------------------------------------------------------
    ' If the user wishes to return data to a sheet
 
    If ReturnMode = 0 Then
 
    '************************************
    'Return data to sheet code goes here
    '************************************
 
    '----------------------------------------------------------------------------------------------------------------
    ' If the user wishes to return data to a control
 
    ElseIf ReturnMode = 1 Then
 
        '------------------------------------------------------------------------------------------------------------
        'Clear items currently held and if there are any records put the new ones in
 
        Sheets(TargetSheet).OLEObjects(ListBoxNameLng).Object.clear
 
        If recCount <> 0 Then
 
 
            For counter = 0 To recCount
 
                Sheets(TargetSheet).OLEObjects
 
                With Sheets(TargetSheet).OLEObjects(ListBoxNameLng).Object
                    .AddItem (ListArray(1, counter))
                    .List(counter, 1) = ListArray(0, counter)
                    .ListIndex = -1
                End With
 
            Next counter
 
        End If
 
    End If
 
    '------------------------------------------------------------------------------------------------------------
    'Close the connections and clean up.
 
    cnn.Close
    Set cmdCommand = Nothing
    Set rstRecordset = Nothing
    Set cnn = Nothing
End Sub

 

[Andrew Poulsom]

At first I thought it may be the name of the propert, but this worked for me:

'Class module named Class1:
 
Private ReturnMode As String
 
Public Property Get Mode() As String
    Mode = ReturnMode
End Property
 
Public Property Let Mode(ByVal value As String)
    ReturnMode = value
End Property
 
Public Sub FetchData()
    MsgBox "Class " & ReturnMode
End Sub
 
'General module
 
Sub Test()
    Dim x As Class1
    Set x = New Class1
    x.Mode = "XXX"
    MsgBox "Test " & x.Mode
    x.FetchData
End Sub

So I don't know why it isn't working for you.