Custom Events and Class Modules 2

[MelissaKT]

Hello. I have several questions regarding Custom Events and Class Modules. First of all, I have started designing a database that will calculate the assessment value of property. The database has numerous tables - tables to keep track of Property Info, Improvement Calcs and then 11 other tables to keep track of the type of improvements done on or to a property. It will have a lot of calculations - and will have calculations based on those calculations. Currently, we are using calculated text boxes on the forms but I'd really like to have these set up as functions. I have created a bound form that I'd like to use, however I'd like to have an event fire when a value is changed. If I understand correctly, I probably need to create classes/ collections. Is it necessary to use classes? Is that the only way that I can make a custom evernt fire? I understand that they (classes) are better in the long run but since I've never used them before I have alot of questions.

Question 1: Do I need to declare a variable / public property for each of the fields within the tables?

Question 2: Since these tables have a one to many relationship, do I need to create collections? Can the collections be in the same class module as the public properties?

Question 3: How do I create the relationships in the classes? Or do I have to?

I do have several other questions but this should last me for today. I'd appreciate any help or comments! Thank you in advance.

 

[Golom]

Without trying to write a primer on OO development ...

Do I need to declare a variable / public property for each of the fields within the tables?
No. If you build a class that pulls information from the database then the various properties of the class will return those values. If for example you have a database field called "AssessmentDate" then you would save that in a class property of the same name and manipulate it with references like
Prop.AssessmentDate = Now() OR
MyDate = Prop.AssessmentDate where "Prop" is an instance of the "TheProperty" class.
The whole point (or at least one of them) of classes is data hiding. You don't want public variables all over the place that are open to unrestricted changes.

Since these tables have a one to many relationship, do I need to create collections? Can the collections be in the same class module as the public properties?
Collections are one way and yes, they can (and should) be contained within the class. Note however that, for consistency and ease of reference, they should be collections of other classes ... not collections of atomic values unless their intended purpose is strictly internal to the class. A Public variable created within a class is Public only to its containing class(es) ... not to the whole application.

How do I create the relationships in the classes? Or do I have to?
"Relationship" is an RDBMS term and doesn't have a direct translation to classes. That said, you can retrieve information from the underlying database using whatever SQL is appropriate and the constraints defined by the DBMS relations will be enforced (if you are in fact enforcing them at that level.)

 

[MelissaKT]

You said that collections were one way. What is another way? If it were you, how would you implement this?

I understand alot of the principles of OOP. I just can't seem to get my mind around the Class Module / Class Collection.

 

[MajP]

I do a lot of OOP and database development, but from just what you said I do not see the need. Could you explain a little more.

Currently, we are using calculated text boxes on the forms but I'd really like to have these set up as functions.

Build some custom functions

I have created a bound form that I'd like to use, however I'd like to have an event fire when a value is changed. If I understand correctly, I probably need to create classes/ collections.

OK, this may require a custom class if you want to trap changes that are done through code not by user events. You may not need a custom collection unless you have a lot of controls that will be in this class.

I have a simple example of both a custom class and collection that traps control events. This should be a close example

thread702-1193103

But give some more details, you may be making this harder than is needed. Often strong programmers look for coding solutions where there may be a much simpler SQL or built in solutions.

 

[MelissaKT]

I really appreciate your response. I looked at the thread you suggested. That did explain part of what I was asking. However, if I can get away from collections/classes, then I love it. Let me get your take on this:

Part of the problem is that I'm reworking an old database. The old database has fields in tables that are calculated values. I've removed these fields in the new database. The users will want to see the total appraised value change as they add/ remove items from the form. Since I've removed some of the old fields, I won't be able to use the old calculated textboxes that we have in the old database. Let me show you part of the structure - I know that this is alot of information:

tblPropertyInfo
PropertyNo (key)
OwnerID
TaxCode
etc

tblImprovementCalcs
ImpCalcID (key)
PropertyNumber(foreign key to tblPropertyInfo)
HLClassScale
ClassCode (foreign key to tblClassCodes)
Index
BaseArea
BaseRate
AdjArea
PhysCond
AdjCond
StructCode

tblClassCodes
Class(key)
ClassUnits


tblExteriorWall - (there are several tables like this one that show different types of improvements)
ExtWallCode (key)
ExtWallDesc
ExtWallValue

tblExteriorWallCalcs - (There are several tables like this one to show different types of improvements - same relationships!)
ExtWallCalcID(key)
ImpCalcID(foreign key to tblImprovementCalcs)
ExtWallCode(foreign key to tblExtWall)
ExtWallUnit - % of structure with this type of ext wall

I need to be able to go into all of the different types of improvements and find the totalvalue of each improvement - then I need to add them all together to find TotalConstructionUnits. I've already got a function written, just don't know how to make a text box update immediately following a change to a value. It has to update immediately - no choice. (These calculations are part of other calculations) If you tell me that I can do this without classes, you will be my hero forever!!

 

[Golom]

OK. Here are some fake classes to illustrate the approach

[COLOR=Blue]Improvements Class[/color]
Private mvarImprovementValue        As Currency
Property Let ImprovementValue(vData As Currency)
   mvarImprovementValue= vData
End Property
Property Get ImprovementValue() As Currency
   ImprovementValue = mvarImprovementValue
End Property
[COLOR=blue]... and many more attributes of an improvement ...[/color]

Sub LoadImprovement (PropertyCode As String,ImprovementNumber As Long)
Dim rs As Recordset
Set rs = db.OpenRecordset ( _
"Select * From ImprovementsTable " & _
"Where PropertyCode = '" & PropertyCode & "' AND " & _
"      ImprovementNumber = " & ImprovementNumber 
Me.ImprovementValue = rs![ImprovementValue]
End Sub

Now the "Property" Class

Dim mCol As Collection
Private Sub Class_Initialize()
    Set mCol = New Collection
End Sub

Private Sub Class_Terminate()
    Set mCol = Nothing
End Sub

Public Sub Add(Improvement As Improvement, Optional sKey As String = "")


    If Len(sKey) = 0 Then
        mCol.Add Improvement 
    Else
        mCol.Add Improvement , sKey
    End If

End Sub

Public Sub LoadImprovements
Dim rs As Recordset, Imp As Improvement
Set rs = db.OpenRecordset ( _
"Select ImprovementNumbers From Improvements " & _
"Where PropertyCode = Me.PropertyCode & "'")
Do until RS.Eof
   Set Imp = New Improvement
   Imp.ImprovementNumber = rs![ImprovementNumber]
   Imp.LoadImprovement
   Add Imp
   rs.MoveNext
Loop
End Sub

Public Function PropertyValue() As Currency
Dim Imp As Improvement, PV As Currency
For Each Improvement In mCol
   PV = PV + Improvement.ImprovementValue
Next Improvement
PropertyValue = PV
End Function

Clearly I've left a lot of stuff out but that should give you some idea about how to build a collection inside a a class and use it to derive results.

As you may notice, you could just as well compute the "PropertyValue" directly with an SQL statement and bypass the collection route. i.e. something like

Public Function PropertyValue() As Currency
Dim rs As Recordset
Set rs = "Select SUM(ImprovementValue} As ImpVal " & _
"Where PropertyCode = '" & Me.PropertyCode & "'")
if IsNull(Rs.ImpVal) Then
   PropertyValue = 0
Else
   PropertyValue = rs![ImpVal]
End If
End Function

Exactly which approach you take are issues associated with the business objectives. There really isn't any "one-size-fits-all" answer to how these things are resolved.

 

[MajP]

It is hard to answer that question with only that much information, but here are some gerneral ideas.

1. I rarely have long complex calculated fields on a form. There is only so much flexibility. Instead I build a lot of custom functions and then use them in forms, reports, and queries. Example:
in an unbound text box

=getConstructionCost([BaseArea],[BaseRate],...[StrucCode])

in a public function

public function getConstructionCost(theBaseArea as double, theBaseRate as double, ...theStrucCode as string) as double
....
lots of complex clode code
....
getConstructionCost = ...
end function

2. I am guessing that your are a programmer becoming a database developer.

I need to be able to go into all of the different types of improvements and find the totalvalue of each improvement - then I need to add them all together to find TotalConstructionUnits. I've already got a function written

Often when someone says they need help looping trough tables to find totals, I ask why. Usually the reason is that they do not realize the power of SQL queries. I would not be suprised if you could not do all of these totals in a query. You just can not code more efficiently than SQL. Then Example

in an unbound text box
=dlookup("totalCost","qryConstructionCost", "propertyID = "& me.propertyID)

Either of these strategies would immediately happen with changes to the underlying data.

3.

The users will want to see the total appraised value change as they add/ remove items from the form.

If the user is initiating events that impact the total, then trap these to see if they change the total.

Private txtBoxRate_afterUpdate()
call subCheckTotalCost
end sub


I am a average SQL writer, but if you post some information in the Queries forums there are some really smart people who can do some amazing things. I helped a guy the last couple of weeks get pages of code and very complex functions down to a relatively short (somewhat logically complex)query. What took nearly an hour in code executed in under 30 seconds. The point is when I hear that you are writing functions to do totals (could be the right approach), it may not be the easiest most efficient way.

Anyways, nothing that I heard you say suggests that this is the path to go down. I would not try to build a Swiss Watch from scratch if I can go to WalMart and get the 17 dollar Timex.

 

[MelissaKT]

Ya'll are great! I think I'm going to "ponder" my options this weekend. I'd like to get away from the collections, if I can - simply because of the learning curve. Thanks again for your help!

 

[MajP]

Melissa,
Here are a couple of other things to read up on in the help file.
Recalc
Requery
Repaint

Here is an example. I have two text boxes on a form. Addend1 and Addend 2. The third box sums these up with a custom function. Control source
=fncAdd()

But in order to refresh the value in the calculated control, I sink the events from the other addend controls. I use two different methods for demonstration

Private Sub txtBxAddend1_AfterUpdate()
Me.Recalc 'Recalculate all controls on the form
End Sub

Private Sub txtBxAddend2_AfterUpdate()
Me.txtBxSum.Requery 'Specifically requery the sum control
End Sub

Public Function fncAdd() As Variant
fncAdd = Me.txtBxAddend1 + Me.txtBxAddend2
End Function

Now "me.requery" would requery the whole form to include the control source of the form. Often this is may end up taking you off of the current record. Often it is better to requery specific controls.