Solved Class Properties Assigned From Procedures

[dalski]

I have some Properties (around 30; most are typical assignment format). Some values are assigned by procedures/ routines inside the Property/ or called. I'm probably going to get told off but these are quite intrinsic values to the class object. I would prefer to call a function to keep the Properties section clear as it's getting quite big & a little obfuscated but I can't figure out what's up.
I intermittently keep generating the error below; despite trying to follow guidance:

• Returning correct data-types
• Query returns a single value
• Having the NewValue argument in the Let procedure... ensuring there is one more parameter than in the Get procedure
• _________________________________________________________________________________________________________________________________
• Additionally tried separating simplifying & just calling a function to return result
• Have similar Property - works fine
• Aware that I don't need to open rs type as Snapshot as a union query is read-only; looking for the slight performance gain (pedantic here I think)

Public Property Let PropName(NewValue As Long)
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("UnionQueryName", dbOpenSnapshot, dbSeeChanges)
  NewValue = rs!FieldName
  mPropName = NewValue
  'mPropName = rs!FieldName  -  FAILS ALSO
  rs.Close
End Property

Public Property Get PropName()
  PropName= mPropName
End Property

 

[arnelgp]

I would change it to a Public Sub rather than a Let Property.
since you are assigning the value directly through query.
how do you supposed to assign Value from your code?

Public Sub AssignPropName()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("UnionQueryName", dbOpenSnapshot, dbSeeChanges)
  mPropName = rs!FieldName & ""
  rs.Close: Set rs = Nothing
End Sub

 

[dalski]

Thanks, yeah I had it as a sub/ function but seemed more intrinsic & seems clearer as a property.

"how do you supposed to assign Value from..." unsure what you mean here. Originally I had it as a function, would call it where needed & store it in the module var. But thought between calls hypothetically if viewing a different record (which the class is linked to) it would be misleading to have a value in the property so would set it to 0 between records & only populate it when required.

Yeah as I'm typing I see your logic. So are Properties only for real basic assignment?
What I didn't like when using it as a function is for any real implementation another temporary variable needs to be created to store the result of the function if using >1 place. The alternative is as a sub & populating the module level variable mPropName, but then the problem of a misleading value from the selection between values. Which I could wipe the module var with a property setting of 0 on each current record selection.

 

[arnelgp]

Yeah as I'm typing I see your logic. So are Properties only for real basic assignment?

 

[cheekybuddha]

Don't you have to declare the property type in the signature?

Public Property Get PropName() As Long

 

[dalski]

Thanks cheekybuddha, you're dead right. I'm perplexed as i have a near identical property that's working fine. Just can't spot what the issue is. I'm not enjoying superflating the Properties with methods & may prefer a call, but in the interest of trying to learn; can't help but play around.

 

[arnelgp]

Don't you have to declare the property type in the signature?

i think you only need to do that on another Class that you want to Implement

 

[arnelgp]

I intermittently keep generating the error below; despite trying to follow guidance:

• Returning correct data-types

 

[dalski]

i think you only need to do that on another Class that you want to Implement

No @cheekybuddha solved it, that was the issue here. Maybe when there's more going on than a basic assignment. I've gotten away with just using a basic Get most places but he's right.

 

[arnelgp]

he's dead right alright and your all wrong on your Let Property.

 

[cheekybuddha]

You just have to match to the input type to the return type:
Eg

Public Property Let PropName(NewValue As Long)
  ' ...
End Property

Public Property Get PropName() As Long
  ' ...
End Property

' OR:

Public Property Let PropName(NewValue)  ' implicit Variant
  ' ...
End Property

Public Property Get PropName()
  ' ...
End Property

Doing the database lookup in the Let() is totally unnecessary - if you *must* do it then just do it in the Get() and don't have a Let() (ie read-only property)

Otherwise, cache the result, so you don't hit the db with a lookup each time you call (Get()) the property.

But much better to leave the Let/Get's in their simple form and load all the properties from a single recordset lookup in your class initialisation.

 

[KitaYama]

I was waiting for someone to ask/explain why the data type is different:

Public Property Let PropName(NewValue As Long)
  .......
  NewValue = rs!FieldName

It seems that FieldName is a string.

 

[dalski]

Thanks all, appreciate that guidance that's not how to use properties.

I was waiting for someone to ask/explain why the data type is different:

I don't think that's correct, that's been taken out of context; you've removed the apostrophe & also removed FAILS ALSO. Which was to show I had experimented so this line is not even in execution. You have no way of knowing what that datatype is but you can see I've defined it as Long; which it was. I thought I'd leave it there in case someone was to mention to watch out for implicit type conversions which I don't know whether or not that would default because of returning an object type of recordset; opposed to a Long as explicitly defined in the Let parameter.

Turns out I'm using Properties in a despicable way & not to be done like that.

 

[MajP]

I don't think that's correct, that's been taken out of context

I think it is correct. The point is that Lets/Sets and Gets have to have matching signatures and yours does not. Your let is a long and Get is a variant (no specified data type).

Public Property Let PropName(NewValue As Long)
  ' Let is a long
End Property
Public Property Get PropName()
  ' Get is a Variant since nothing specified
End Property

Keep in mind you can have gets without lets/sets and sets without gets/lets.
One thing bad about VBA you cannot really make one of these public and the other private. You can but not real clean. But in your case you do not need a public let because as @arnelgp points out there is no real way to set it externally. Or at least the user would have no idea of the value to pass in because it is not used.

You would unlikely ever have a query name inside a class. If so it becomes very hard wired. Likely you would pass that in so that you do not have to rewrite the class. Here is my guess at what you are doing, and maybe something that makes some logical sense.

I have a table of application settings and I want a quick way to get these properties. Could write a lot of functions and dlookups or Tempvars, but a class works.

Private m_ApplicationDataRecordset As DAO.Recordset

Private m_FontSize As Long
Private m_FontName As String
Private m_IconSize As Long
Public Sub Initialize(TableName As String)
  Set Me.ApplicationDataRecordset = CurrentDb.OpenRecordset(TableName)
  With Me.ApplicationDataRecordset
    m_FontName = !FontName
    m_FontSize = !FontSize
    m_IconSize = !IconSize
  End With
End Sub
Public Property Set ApplicationDataRecordset(ByVal objNewValue As DAO.Recordset)
    Set m_ApplicationDataRecordset = objNewValue
End Property

'------------------------------- Read Only Getters ----------------------------
Public Property Get ApplicationDataRecordset() As DAO.Recordset
    Set ApplicationDataRecordset = m_ApplicationDataRecordset
End Property
Public Property Get FontSize() As Long
    FontSize = m_FontSize
End Property
Public Property Get FontName() As String
    FontName = m_FontName
End Property
Public Property Get IconSize() As Long
    IconSize = m_IconSize
End Property

Option Compare Database
Option Explicit

Private AP As ApplicationProperties
Public Sub Test()
  Set AP = New ApplicationProperties
  AP.Initialize ("tblApplicationSettings")
  Debug.Print AP.FontName & " " & AP.FontSize & " " & AP.IconSize
End Sub

No real need for Letters because that is all done in the Initialize.

In something like VB.net you can have private and public accessors (and one can be private and one public) and these can have different signatures. Private allows you to use the accessors from inside the class in place of directly referencing the class variable.

 

[MajP]

In your example there appears to be more than one record unlike my example. In that case you may want a getter without any class variable.
No need to store those values because they likely change every time you are getting them.

Private m_RS_Products As DAO.Recordset

Public Sub Initialize(QueryName As String)
  Set m_RS_Products = CurrentDb.OpenRecordset(QueryName)
End Sub
'****************************************************************************************************************************************************************
'-----------------------------------------------------------------------------------   Read Only Getters   -------------------------------------------------------------
'*****************************************************************************************************************************************************************
Public Property Get ProductName() As String
    ProductName = Me.RS_Products!ProductName
End Property
Public Property Get Category() As String
    Category = Me.RS_Products!CategoryName
End Property
Public Property Get SupplierName() As String
    SupplierName = Me.RS_Products!CompanyName
End Property
Public Property Get RS_Products() As DAO.Recordset
    Set RS_Products = m_RS_Products
End Property

Test

Private PP As New ProductProperties
Public Sub testProp()
  PP.Initialize ("qryProducts")
  Debug.Print PP.Category & " " & PP.ProductName & " " & PP.SupplierName
  PP.RS_Products.MoveNext
  Debug.Print PP.Category & " " & PP.ProductName & " " & PP.SupplierName
End Sub

 

[MajP]

One more thing. A get without a set/let can be written as a function and I know of no difference. You end up calling it the same way.

Private m_RS_Products As DAO.Recordset
Public Sub Initialize(QueryName As String)
  Set m_RS_Products = CurrentDb.OpenRecordset(QueryName)
End Sub
'****************************************************************************************************************************************************************
'-----------------------------------------------------------------------------------   Read Only Getters   -------------------------------------------------------------
'*****************************************************************************************************************************************************************
Public Function ProductName() As String
    ProductName = Me.RS_Products!ProductName
End Function
Public Function Category() As String
    Category = Me.RS_Products!CategoryName
End Function
Public Function SupplierName() As String
    SupplierName = Me.RS_Products!CompanyName
End Function
Public Property Get RS_Products() As DAO.Recordset
    Set RS_Products = m_RS_Products
End Property

Public Sub testProp()
  PP.Initialize ("qryProducts")
  Debug.Print PP.Category & " " & PP.ProductName & " " & PP.SupplierName
  PP.RS_Products.MoveNext
  Debug.Print PP.Category & " " & PP.ProductName & " " & PP.SupplierName
End Sub

 

[dalski]

Thanks, sure I was not aware of the Get type needed to be defined as the default VBA insert procedure uses variant so that made me completely unaware that when not using the Variant type. But I cannot see how one came to the assumption it being a String datatype when it was defined as long & I read it as was referring to string from reading the rs!FieldName. If the point being made was the return types then I would've thought the single line quotation would be the case & note the apostrophe removal & - FAILS ALSO. Then yes absolutely correct in that statement.

If I've misinterpreted it apologies @KitaYama. Frivolous insecurities aside - as usual you're pretty right in your assumption MajP. I'm using a single predeclared class for the benefit of encapsulating relevant methods/ properties. Without classes there will be so many methods & properties it will be very difficult to keep track of things.

I will have another slightly different one serving a similar purpose & it's just a matter of changing a parameter or two in the other class & a few letters in a query or two. Then depending on the changes made updating a temporary table, or updating main source records. I'd probably be better using what MarkK refers to as a 'Service' which bearing in mind I can't write a property properly is too much for me atm. Baby steps for my limited capabilities.

 

[MajP]

In VBA, if a function's / property's return type is not explicitly declared using the As datatype clause, VBA implicitly assigns it the Variant data type.

But you understand why this makes no sense?

Public Property Let PropName(NewValue As Long)
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("UnionQueryName", dbOpenSnapshot, dbSeeChanges)
  mPropName = rs!FieldName
  rs.Close
End Property

You have a property with a required parameter that is not used for anything. Regardless of what the user would pass in (20,100, 1000000) it will return 2.l75 or whatever value is in the field. The user would be required to pass in a value, have no idea what to pass in, and it would not matter anyways because it is not used.

The other version you tried makes less sense

Public Property Let PropName(NewValue As Long)
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("UnionQueryName", dbOpenSnapshot, dbSeeChanges)
  NewValue = rs!FieldName
  mPropName = NewValue
  rs.Close
End Property

The user guesses some number and Passes in 10. Then 10 gets changed to 2.175, and then mPropName gets set to your now changed input parameter.

I think @KitaYama saw "PropName" and assumed you were returning a property name and thus a string. I had to reread that too and got that is just a generic placeholder where the accessors would be names of properties (fields) and would return the value in those fields and not the name of those fields.

 

[dalski]

Thanks MajP, yes I get the issue & thanks for clarifying so I grasp it. I just play around as much as I can creating all sorts of monstrosities trying to learn everything I can, and very often I embarrass myself - an area where I show great talent.