Question Enum equivalent in Access?

[NotSoRandomOne]

I'm trying to record visits to patients, and there will be five or so visit types. Right now I have a table of these visit types, and it works, but it is difficult to use. For instance, in code, to take action based upon a visit type requires the following, because I refuse to use the ID as a hard-coded value for debugging purposes:

If (tvlt_rs![VisitType] = DLookup("ID", "VisitTypesTable", "VisitType='Regular Visit'")) Then numRegVisitsPerformed = numRegVisitsPerformed + 1

It would be much simpler to use an enumeration, such as is possible in C++. Can that be done? Is it a better approach than the table-based one? How do I do it? In the common module, as a global (which are frowned upon)? Or should I break down and use the ID of the corresponding visit type, and ignore the fact that the person doing debugging no longer has meaningful information at their disposal?

Thank you for any feedback.

David

 

[Banana]

I would probably write a function to retrieve the ID. If you want intellisense as well, you could use Enum, but you would then have two places to maintain the list. Not too bad if you only have a handful of values that doesn't change too often.

 

[NotSoRandomOne]

Thank you, Banana

If I create a wrapper function, such as 'isRegularVisit()', the code will be much prettier - that is a good idea.

A question regarding this is that even doing it that way, a DLookup will be at the heart of the function, so in cases where I am toggling through all the cases, Access will be performing a DLookup for each case. I was thinking it would be better to use an enum to eliminate some of the DLookup overhead. There are only five cases of visit types (and I would be surprised if this ever changed), so maybe that DLookup overhead isn't worth bothering with?

David

 

[Banana]

I wouldn't know. I'm inclined to say it's best to not drop a table because that's the only way to have referential integrity and protect from entering invalid data, even if it's going to be just five-rows lookup table.

Even so, one option we can look into is whether we can use static variables so we only need to do DLookup once and save it in memory. It may not work if the return could be different every time, though.

Alternatively, just hard-code the values using Enum with the understanding that this leaves you with two places to keep the set of values consistent (e.g. in the table and in the Enum declared in the VBA).

HTH.

 

[NotSoRandomOne]

...Alternatively, just hard-code the values using Enum with the understanding that this leaves you with two places to keep the set of values consistent (e.g. in the table and in the Enum declared in the VBA).

I'm moving forward with this approach. After consideration, it seems the best option.

HTH.

Yes, it did. Thanks!

 

[Banana]

Please forgive me if I'm beating it over your head, but just wanted to be sure that VBA does support enumeration as much as C++ does, and you can declare an Enum in VBA then use it as a parameter.

Public Enum MyEnum
   AValue = 1
   SomeValue = 2
   OtherValue = 4
End Enum

Public Function GetIt(Input As MyEnum) As String

Select Case Input
    Case 1
       GetIt = "A Value"
    Case 2 
       GetIt = "Some Value"
    Case 3
       GetIt = "Other Value"
End Select

End Function

If nothing else, at least it may clear up for other novices who may not be aware of that functionality in VBA and employing the intellisense with the enumeration.

 

[NotSoRandomOne]

Since your post left me questioning, and others may also, it appears that for even more clarity you can refer to the enum in the case statements themselves:

Public Function GetIt(theInput As MyEnum) As String
    Select Case theInput
        Case MyEnum.AValue
           GetIt = "A Value"
        Case MyEnum.OtherValue
           GetIt = "Some Value"
        Case MyEnum.SomeValue
           GetIt = "Other Value"
        End Select
    End Function

Thanks again,
David

 

[Banana]

Indeed. That is actually better than using literal value (which is also legal, except that Case 3 should be Case 4) in case we want to change the literal value associated with the member of an enum.

Good catch & good luck!

 

[NotSoRandomOne]

What would be the VBA equivalent of throwing an exception as a default case, to alert tired programmers that they missed something somewhere? Could you post a quick code snippet for the previous example?

Thanks.

 

[Banana]

Don't get me started on the missing Try/Catch....


In VBA, we use something like this:

<Procedure Header>

On Error GoTo ErrHandler

<regular code here...>

SomePoint:

<some more regular code>

ExitSub:
Exit Sub

ErrHandler:

Select Case Err
   Case 1
       Resume Next
   Case 2
       If intRetry > 3 Then
          intRetry = intRetry + 1
          Resume
       End If
   Case 3
       Resume SomePoint
   Case Else
       Msgbox Err.Number & vbCrLf & Err.Description
       GoTo ExitSub
End Select

End <procedure>

You don't need all Resume, Resume Next; only define what you need to catch/handle in a given procedure.

 

[NotSoRandomOne]

So the full function would look like this?

Public Function GetIt(theInput As MyEnum) As String
    On Error GoTo ErrHandler
    Select Case theInput
        Case MyEnum.AValue
           GetIt = "A Value"
        Case MyEnum.OtherValue
           GetIt = "Some Value"
        Case MyEnum.SomeValue
           GetIt = "Other Value"
        Case Else
            Error 1
        End Select

ExitSub:
    Exit Function

ErrHandler:
     Select Case Err
       Case 1
           MsgBox ("Invalid input - Aborting")
           Exit Function
       Case Else
           MsgBox Err.Number & vbCrLf & Err.Description
           GoTo ExitSub
    End Select

    End Function

I haven't ran it, but I question whether I can use the 'Exit Function' statement in the ErrHandler as I have, or if it must be through a GoTo as yours indicates.

And, per your 'Try/Catch' implication, exceptions as in C++ don't really exist? So everything must be handled in the ErrHandler routines for the function/subroutines being developed?

Thanks

 

[Banana]

Whoops, I left out some details.

1) The reason for the "Goto ExitSub" is because as you suspected, there is no "Finally" block, so we need to make sure we keep our clean-up code in one place and that's usually accomplished by putting it between the label and the actual exit point. That way when an error actually occurred and we decide to quit the routine, we make sure to go to the label and process all cleanup code before actually quitting. For this particular routine, there's no need for "Finally" so it doesn't really matter if the error handler has an Exit Function instead. But there's issue of keeping things consistent. What if function was later modified to use something that required cleanup? You could get weird bugs if the error handler abruptly exited the function without cleaning up because someone had forgotten to change Exit Function to Goto <label>.

2) I forgot to clarify that the "Case 1" should be replaced with whatever error numbers you want to trap. For example, if we have a division by zero error, the Err.Number will be 11, so the Case would be Case 11 and we can handle the division by zero accordingly. So you'd have to use literal values for specific error numbers you anticapticate for a given routine. For a generic handler, use Case Else to catch all kind of errors. But there's no class or categories of Error, unforunately.

3) I also should point out that the error handling works much like in C++; an unhandled error in a function will "bubble" up to the calling procedure, until the caller has an error handler of its own, so you don't have to put error handler in all routines if you want to allow the errors to bubble up to the callers.

HTH.

 

[Banana]

Addendum:

I learned something new. I never had seen the "Error 1" before, and looked at the help file. Looks like a nice way to raise error. My usual method has been to do "Err.Raise -1, "Blah blah", "Blah blah" You may also want to be aware that Error seems to be supported for backward compatibility only and Err.Raise is preferred (if the help file is to be believed).

Another point. You don't actually have to use an error handler to reject invalid input. You could equally well do this:

Case Else
   MsgBox ("Invalid Input - Aborting")

I tend to find myself writing more "preventive code" as opposed to using error handlers to catch bad/invalid inputs or other "expected" errors.

 

[NotSoRandomOne]

Thank you. That was quite informative. Now I'm off to put it all together.

Best wishes,
David