Northwind DomainFunctionWrappers (1 Viewer)

[Dreamweaver]

I'm creating my own version of nothwind like I have been doing with my templates except I will be using my version to cover problems I see in that area of design.

I'm currently going over the old and current version of northwind to create a schema, ERD and looking for useful items and came across the module below I haven't got the tools to test it for speed etc, Although it looks very useful to me what I know is limited so thought I would ask to see if anybody can see enything that might need correcting or improving or give me there thoughts on weather it's worth using within my example.

thanks mick

Option Compare Database
Option Explicit

Private Enum DomainFunctionWrapperEnum
    DLookup_Wrapper
    DCount_Wrapper
    DSum_Wrapper
    DMax_Wrapper
    DMin_Wrapper
    DAvg_Wrapper
End Enum

Private Function DomainFunctionWrapper(DomainFunction As DomainFunctionWrapperEnum, _
                                    Expr As String, _
                                    Domain As String, _
                                    Optional Criteria As String) As Variant
    On Error GoTo ErrorHandler
   
    Select Case DomainFunction
    Case DLookup_Wrapper
        DomainFunctionWrapper = DLookup(Expr, Domain, Criteria)
    Case DCount_Wrapper
        DomainFunctionWrapper = DCount(Expr, Domain, Criteria)
    Case DSum_Wrapper
        DomainFunctionWrapper = DSum(Expr, Domain, Criteria)
    Case DMax_Wrapper
        DomainFunctionWrapper = DMax(Expr, Domain, Criteria)
    Case DMin_Wrapper
        DomainFunctionWrapper = DMin(Expr, Domain, Criteria)
    Case DSum_Wrapper
        DomainFunctionWrapper = DSum(Expr, Domain, Criteria)
    Case DAvg_Wrapper
        DomainFunctionWrapper = DAvg(Expr, Domain, Criteria)
    Case Else
        ' Unexpected DomainFunction argument
        Debug.Assert False
    End Select

Done:
    Exit Function
ErrorHandler:
    Debug.Print Err.Number & " - " & Err.Description
   
    ' Resume statement will be hit when debugging
    If eh.LogError("DomainFunctionWrapper", _
                   "DomainFunction = " & DomainFunction, _
                   "Expr = " & Expr, _
                   "Domain = " & Domain, _
                   "Criteria = '" & Criteria & "'") Then Resume
End Function


'--------------------------------------------------------
' DLookupWrapper is just like DLookup only it will trap errors.
'--------------------------------------------------------
Public Function DLookupWrapper(Expr As String, Domain As String, Optional Criteria As String) As Variant
    DLookupWrapper = DomainFunctionWrapper(DLookup_Wrapper, Expr, Domain, Criteria)
End Function


'--------------------------------------------------------
' DLookupStringWrapper is just like DLookup wrapped in an Nz
' This will always return a String.
'--------------------------------------------------------
Public Function DLookupStringWrapper(Expr As String, Domain As String, Optional Criteria As String, Optional ValueIfNull As String = "") As String
    DLookupStringWrapper = Nz(DLookupWrapper(Expr, Domain, Criteria), ValueIfNull)
End Function


'--------------------------------------------------------
' DLookupNumberWrapper is just like DLookup wrapped in
' an Nz that defaults to 0.
'--------------------------------------------------------
Public Function DLookupNumberWrapper(Expr As String, Domain As String, Optional Criteria As String, Optional ValueIfNull = 0) As Variant
    DLookupNumberWrapper = Nz(DLookupWrapper(Expr, Domain, Criteria), ValueIfNull)
End Function


'--------------------------------------------------------
' DCountWrapper is just like DCount only it will trap errors.
'--------------------------------------------------------
Public Function DCountWrapper(Expr As String, Domain As String, Optional Criteria As String) As Long
    DCountWrapper = DomainFunctionWrapper(DCount_Wrapper, Expr, Domain, Criteria)
End Function


'--------------------------------------------------------
' DMaxWrapper is just like DMax only it will trap errors.
'--------------------------------------------------------
Public Function DMaxWrapper(Expr As String, Domain As String, Optional Criteria As String) As Long
    DMaxWrapper = DomainFunctionWrapper(DMax_Wrapper, Expr, Domain, Criteria)
End Function


'--------------------------------------------------------
' DMinWrapper is just like DMin only it will trap errors.
'--------------------------------------------------------
Public Function DMinWrapper(Expr As String, Domain As String, Optional Criteria As String) As Long
    DMinWrapper = DomainFunctionWrapper(DMin_Wrapper, Expr, Domain, Criteria)
End Function


'--------------------------------------------------------
' DSumWrapper is just like DSum only it will trap errors.
'--------------------------------------------------------
Public Function DSumWrapper(Expr As String, Domain As String, Optional Criteria As String) As Long
    DSumWrapper = DomainFunctionWrapper(DSum_Wrapper, Expr, Domain, Criteria)
End Function


'--------------------------------------------------------
' DAvgWrapper is just like DAvg only it will trap errors.
'--------------------------------------------------------
Public Function DAvgWrapper(Expr As String, Domain As String, Optional Criteria As String) As Long
    DAvgWrapper = DomainFunctionWrapper(DAvg_Wrapper, Expr, Domain, Criteria)
End Function

 

[Galaxiom]

Can't see the point of the wrapper. Errors with Domain functions would be the result of gross mistakes. Mainly it would obfuscate your code.

 

[Dreamweaver]

Thank @Galaxiom

 

[Uncle Gizmo]

When I saw this DomainFunctionWrapper in the Northwind database I didn't see how it could be of much benefit to my own databases. It seemed to me to be adding an unnecessary level of complexity. I assume it is there as an example of how to use wrappers.

The main use of the domain function wrapper appears mainly to be for trapping errors. I favour eliminating the possibility of errors, and if an error was possible, I would plan my code to cater for that particular error.

For Example:-
Let's assume you have code in your main form, which depends on information residing in another form. When you attempt to extract that information, you get an error because the other form is not open. You could write code to detect the error number and take action then, however my preference is to look first to see if the form is open and then stop the code execution without relying on an error. I use the form code example because I couldn't think of a domain example!

 

[Dreamweaver]

For Example:-
Let's assume you have code in your main form, which depends on information residing in another form. When you attempt to extract that information, you get an error because the other form is not open. You could write code to detect the error number and take action then, however my preference is to look first to see if the form is open and then stop the code execution without relying on an error. I use the form code example because I couldn't think of a domain example!

I'm the same there I would only use an error trap if I couldn't find another way

I did think of another reason not to use it in my example that is as you said it's adding complexity to something I should be makeing as simple as possible to understand.

Thanks

 

[Uncle Gizmo]

I would only use an error trap if I couldn't find another way

I used to be absolutely opposed to using error trapping to control program flow. However I ran into a problem with my Class Module for controlling "Calling Called" Forms

CallCalled Class Module - Nifty Access

CallCalled Class Module I will be adding information about my "CallCalled" Class Module here, as it becomes available. The most recent example of it is on Access World Forums here:-VBA to reuse data entry popup form Class Module - Overview Class Module - Overview Video 1 (01:09) You can see how...

www.niftyaccess.com

The problem was I wanted to create a generic solution, that is a solution that would work in any situation. However the problem was, the code was dependent on there being a particular function "fPassBackRun" in the Calling form. This function allows you to simulate the after update event of a Control. For example, you have a text box for displaying a date, you have a command button that calls a form to select the date. When the date is selected, you want to use that date to calculate the age of a person automatically. I could find no way of doing this, however I do suspect that MajP may have a solution, although I don't recall seeing an example.

My solution is to add a public function "fPassBackRun" in the Calling form. The class module has access to this function and can use it to simulate the after update event of a Control. However if the user does not place that function in their calling form, (and I have no control over this situation) I need to check to see if that function exists or not, otherwise the "Call Called Class" will fail.

I tried everything I could to detect whether this function was present or not, but I couldn't find a method that would work in all situations. The only method I knew that would work is to check to see if it was there (Call on it) and if it wasn't there an error would occur. Just what I didn't want to do!

Then I saw some Allen Browne code where he did exactly that, his code relied on an error. So I thought if it's good enough for Allen it's good enough for me! I minimised any adverse reaction and my solution as you can see from the "Call Called Class" here,

            Select Case Err.Number
                Case 2465 'Application-defined or object-defined error
                    'UnCheck This Message for Testing
                    'MsgBox "Error Caused because - fPassBackRun does not exist" & vbCrLf & vbCrLf & strErrFrom & strErrInfo, , conAppName
                    prpFlgHASfPassBackRun = False   'Flag that the Function fPassBackRun does not exist
                    Err.Number = 0                  'An Expected Error, - so Reset
                    Resume Next                     'Resume as if no Error Occurred
                Case Else

when the class module is loading it does the check right at the beginning where the effect of any failure would be insignificant. In fact, can't foresee the possibility of anything going wrong with it... "Famous last words!"

Edit:-
I'm always working on the "Call Called" Class module. My next task is to update "Developer Mode" to report whether the the function "fPassBackRun" exists or not...
I would also like to remove the developer mode selection Flag out of the class module and into the form.

 

[Dreamweaver]

I knew that would work is to check to see if it was there (Call on it) and if it wasn't there an error would occur.

I would have done the same, I've got a function that if it detects a perticular error it has a resume next so if that error happens the code has been designed to handle it I'll see If I can find it Think it's in something I built in the last year truble is thats a lot of code LOL

 

[Uncle Gizmo]

Then I saw some Allen Browne code where he did exactly that, his code relied on an error.

I'm pretty sure it was this example from Allen Browne:-

Microsoft Access tips: Assign default values from the last record

How to have form in a Microsoft Access database automatically offer to the values of the last record, to avoid typing similar data again.

allenbrowne.com

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose: Return true if the object has the property.
    Dim varDummy As Variant

    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

 

[Dreamweaver]

While Working on the products I just came across something I got help with when I first joined the site

So Have decided to include it as It Would be of use.

I remember I needed to get the current price for a product from a table as below.

tblProductPricing
ProductPriceID PK
ProductID FK to products
EffectiveDate Date
SalePrice

What I did was use this in the order details product Combo to get current price for a product to display in the list.

 

[Guus2005]

Search the site for TLOOKUP. It is a replacement for all domain functions. Not a wrapper but a much faster alternative.

I would use a wrapper for a function with a complex set of arguments, like a Paramarray.

 

[Dreamweaver]

Thanks @Guus2005 will look at it