Northwind DomainFunctionWrappers

[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

 

[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

 

[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

 

[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