Is there something like Enum that will hold Text values?

  • Thread starter Thread starter MikeC
  • Start date Start date
M

MikeC

The below function returns a value list for a set of combo boxes that share
the same data source. The function returns records ("people") who satisfy
the strWhere condition passed to the function, plus whatever OldValue may
already exist in the combo box. This last part is important because the
people normally appear in the list based on their assigned roles, but their
roles can change over time, so the function adds the existing *.OldValue if
one exists. This way, someone who was a "GIS" person back in 1999 will
still appear in a combo box for an older record even if the person has
changed roles to "CADD" or whatever.

To make the function easier to use, I developed a set of constants to pass
the pre-defined "Where" criteria to the function. These constants are
listed below the function in this post. An example of a function call is at
the bottom.

So far, the code works fine in testing.

Now for my question, I had wanted to make my list of constants display in
the function's intellisense text as a *dropdown list*, just as Enum type
lists do. However, the Enum statement is designed for *enumeration* and
does *not* accept strings. Does an equivalent exist for strings?

P.S. I'm cross-posting to microsoft.public.access.formscoding,
microsoft.public.access.modulesdaovba AND
microsoft.public.access.modulesdaovba.ado

Public Function fnGetComboValueList(strWhere As String, Optional
varOldValue) As String
On Error GoTo Err_fnGetComboValueList

Dim strSQL As String
Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset

'The " OR PER_ID = " is used to add an existing related record to the
value list.
'This functionality is needed in cases where a person has changed roles
since the
'record was originally assigned the existing old value. The value must
continue
'to display because it is historically valid.
If Not IsNull(varOldValue) Then
'Verifying value is numeric just in case something else gets passed
to the function.
If IsNumeric(varOldValue) Then
'NOTE: strOrWhere variable *includes* the "OR"
strWhere = strWhere & " OR PER_ID = " & CInt(varOldValue)
End If
End If

'Create the SQL string to select the lutPersonLocal records.
strSQL = "SELECT PER_ID, PER_LAST_NAME, PER_FIRST_NAME" _
& " FROM lutPersonLocal WHERE " & strWhere _
& " ORDER BY PER_LAST_NAME, PER_FIRST_NAME"

Set cnn1 = CurrentProject.Connection

Set rst = New ADODB.Recordset

'Set cursor location to client so that provider will support RecordCount
property.
rst.CursorLocation = adUseClient

'Open the recordset.
rst.Open strSQL, cnn1, adOpenForwardOnly, adLockOptimistic, adCmdText

'Build the value list.
With rst
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
fnGetComboValueList = fnGetComboValueList _
& "'" & !PER_ID & "';'" & !PER_LAST_NAME
& ", " _
& !PER_FIRST_NAME & "';"
.MoveNext
Loop
End If
End With

Exit_fnGetComboValueList:
On Error Resume Next
rst.Close
Set rst = Nothing
cnn1.Close
Set cnn1 = Nothing
strSQL = vbNullString
Exit Function

Err_fnGetComboValueList:
If Err.Number <> 2501 Then
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
End If
Resume Exit_fnGetComboValueList

End Function


The below constants have been defined in the declarations section of a
standard code module.

Public Const conCADDYes = "PER_CADD = Yes"
Public Const conChiefYes = "PER_CHIEF = Yes"
Public Const conConstructionYes = "PER_CONSTRUCTION = Yes"
Public Const conGISYes = "PER_GIS = Yes"
Public Const conGPSPrelimYes = "PER_GPS = Yes AND PER_PRELIM = Yes"
Public Const conGPSYes = "PER_GPS = Yes"
Public Const conPrelimYes = "PER_PRELIM = Yes"
Public Const conRequestorYes = "PER_REQUESTOR = Yes"
Public Const conResearchYes = "PER_RESEARCH = Yes"
Public Const conROWYes = "PER_ROW = Yes"
Public Const conSupervisorYes = "PER_SUPERVISOR = Yes"


Below is an example of a line that is executed by the On Current event to
populate a combo box:

Me!cmbRequestor.RowSource = fnGetComboValueList(conRequestorYes,
Me!cmbRequestor.OldValue)
 
I found and corrected a problem with the function where names were being
incorrectly delimited due to apostrophe's contained in some people's names.
Next thing you know, people will start putting double quotes and semi colons
in their names! Updated code is below.

The question still remains open regarding whether an Enum-like equivalent
exists for storing strings.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Public Function fnGetComboValueList(strWhere As String, Optional
varOldValue) As String
On Error GoTo Err_fnGetComboValueList

Dim strSQL As String
Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset

'The " OR PER_ID = " is used to add an *existing* related record to the
value list.
'This functionality is needed in cases where a person has changed roles
since the
'record was originally assigned the existing old value. The value must
continue
'to display because it is historically valid.
If Not IsNull(varOldValue) Then
'Verifying value is numeric just in case something else gets passed
to the function.
If IsNumeric(varOldValue) Then
'Append additional "OR" criteria if OldValue exists and is
numeric.
strWhere = strWhere & " OR PER_ID = " & CInt(varOldValue)
End If
End If

'Create the SQL string to select the lutPersonLocal records.
strSQL = "SELECT PER_ID, PER_LAST_NAME & ', ' & PER_FIRST_NAME AS Name"
_
& " FROM lutPersonLocal WHERE " & strWhere _
& " ORDER BY PER_LAST_NAME & ', ' & PER_FIRST_NAME;"

'Initialize the connection variable.
Set cnn1 = CurrentProject.Connection

'Initialize the recordset variable.
Set rst = New ADODB.Recordset

With rst
'Set cursor location to client so that provider will support
RecordCount property.
.CursorLocation = adUseClient

'Open the recordset.
.Open strSQL, cnn1, adOpenStatic, adLockOptimistic, adCmdText

'Build the value list.
If .RecordCount > 0 Then
fnGetComboValueList = .GetString(adClipString, , """;""",
""";""")
End If
End With

Exit_fnGetComboValueList:
On Error Resume Next
rst.Close
Set rst = Nothing
cnn1.Close
Set cnn1 = Nothing
strSQL = vbNullString
Exit Function

Err_fnGetComboValueList:
If Err.Number <> 2501 Then
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
End If
Resume Exit_fnGetComboValueList

End Function
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 
Mike
You could use a class (if you reckon this OOP stuff will catch on<g> )

See www.papwalker.com/public/enumclass.zip for a less readerwrap destroyed
idea.

It displays intellisense. You could always modify the idea for non class
code.
anyway basically you end up with...
Me!cmbRequestor.RowSource = _
class.GetComboValueList(conRequestorYes, _
Me!cmbRequestor.OldValue)

If you see the article on advanced classes on my site you can use property
get lets and then set the GetComboValueList return member to be default so
....

Dim rsc As New Class1
rsc.Where = ChiefYes 'shows intellisense
'rsc.OldVal = whatever 'optional anyway
Me!cmbRequestor.RowSource = rsc


The sample code in the demo...
'Module Code
Sub test()
Dim c As New Class1
Dim x As String
x = c.ComboValueList(ChiefYes)
Debug.Print x
End Sub

'class code...
Option Compare Database
Option Explicit
Public Enum WhereType
CADDYes = 1
ChiefYes = 2
ConstructionYes = 3
GISYes = 4
GPSPrelimYes = 5
GPSYes = 6
PrelimYes = 7
RequestorYes = 8
ResearchYes = 9
ROWYes = 10
SupervisorYes = 11
End Enum

Public Function ComboValueList(where As WhereType) As String
Rem this is where you put all the other code
Rem I return the string value from the enum as an example
ComboValueList = EnumStr(where)
End Function

Private Function EnumStr(where As WhereType)
Select Case where
Case 1
EnumStr = "PER_CADD = Yes"
Case 2
EnumStr = "PER_CHIEF = Yes"
Case 3
EnumStr = "PER_CONSTRUCTION = Yes"
Case 4
EnumStr = "PER_GIS = Yes"
Case 5
EnumStr = "PER_GPS = Yes AND PER_PRELIM = Yes"
Case 6
EnumStr = "PER_GPS = Yes"
Case 7
EnumStr = "PER_PRELIM = Yes"
Case 8
EnumStr = "PER_REQUESTOR = Yes"
Case 9
EnumStr = "PER_RESEARCH = Yes"
Case 10
EnumStr = "PER_ROW = Yes"
Case 11
EnumStr = "PER_SUPERVISOR = Yes"
End Select
End Function

peter walker

[]
To make the function easier to use, I developed a set of constants to pass
the pre-defined "Where" criteria to the function. These constants are
listed below the function in this post. An example of a function call is at
the bottom.
Click to expand...
[]> The below constants have been defined in the declarations section of a
standard code module.

Public Const conCADDYes = "PER_CADD = Yes"
Public Const conChiefYes = "PER_CHIEF = Yes"
Public Const conConstructionYes = "PER_CONSTRUCTION = Yes"
Public Const conGISYes = "PER_GIS = Yes"
Public Const conGPSPrelimYes = "PER_GPS = Yes AND PER_PRELIM = Yes"
Public Const conGPSYes = "PER_GPS = Yes"
Public Const conPrelimYes = "PER_PRELIM = Yes"
Public Const conRequestorYes = "PER_REQUESTOR = Yes"
Public Const conResearchYes = "PER_RESEARCH = Yes"
Public Const conROWYes = "PER_ROW = Yes"
Public Const conSupervisorYes = "PER_SUPERVISOR = Yes"
Click to expand...
[]
 
Peter,

Thanks for the info.

Believe it or not, I had a similar idea, but wanted to see if VBA already a
built-in method or statement that I could substitute for the Enum statement
before expending effort on writing a new procedure or module. Looks like
new code is the answer. Thanks again.


peter walker said:
Mike
You could use a class (if you reckon this OOP stuff will catch on<g> )

See www.papwalker.com/public/enumclass.zip for a less readerwrap
destroyed
idea.

It displays intellisense. You could always modify the idea for non class
code.
anyway basically you end up with...
Me!cmbRequestor.RowSource = _
class.GetComboValueList(conRequestorYes, _
Me!cmbRequestor.OldValue)

If you see the article on advanced classes on my site you can use property
get lets and then set the GetComboValueList return member to be default so
...

Dim rsc As New Class1
rsc.Where = ChiefYes 'shows intellisense
'rsc.OldVal = whatever 'optional anyway
Me!cmbRequestor.RowSource = rsc


The sample code in the demo...
'Module Code
Sub test()
Dim c As New Class1
Dim x As String
x = c.ComboValueList(ChiefYes)
Debug.Print x
End Sub

'class code...
Option Compare Database
Option Explicit
Public Enum WhereType
CADDYes = 1
ChiefYes = 2
ConstructionYes = 3
GISYes = 4
GPSPrelimYes = 5
GPSYes = 6
PrelimYes = 7
RequestorYes = 8
ResearchYes = 9
ROWYes = 10
SupervisorYes = 11
End Enum

Public Function ComboValueList(where As WhereType) As String
Rem this is where you put all the other code
Rem I return the string value from the enum as an example
ComboValueList = EnumStr(where)
End Function

Private Function EnumStr(where As WhereType)
Select Case where
Case 1
EnumStr = "PER_CADD = Yes"
Case 2
EnumStr = "PER_CHIEF = Yes"
Case 3
EnumStr = "PER_CONSTRUCTION = Yes"
Case 4
EnumStr = "PER_GIS = Yes"
Case 5
EnumStr = "PER_GPS = Yes AND PER_PRELIM = Yes"
Case 6
EnumStr = "PER_GPS = Yes"
Case 7
EnumStr = "PER_PRELIM = Yes"
Case 8
EnumStr = "PER_REQUESTOR = Yes"
Case 9
EnumStr = "PER_RESEARCH = Yes"
Case 10
EnumStr = "PER_ROW = Yes"
Case 11
EnumStr = "PER_SUPERVISOR = Yes"
End Select
End Function

peter walker

[]
To make the function easier to use, I developed a set of constants to
pass
the pre-defined "Where" criteria to the function. These constants are
listed below the function in this post. An example of a function call is at
the bottom.
Click to expand...
[]> The below constants have been defined in the declarations section of a
standard code module.

Public Const conCADDYes = "PER_CADD = Yes"
Public Const conChiefYes = "PER_CHIEF = Yes"
Public Const conConstructionYes = "PER_CONSTRUCTION = Yes"
Public Const conGISYes = "PER_GIS = Yes"
Public Const conGPSPrelimYes = "PER_GPS = Yes AND PER_PRELIM = Yes"
Public Const conGPSYes = "PER_GPS = Yes"
Public Const conPrelimYes = "PER_PRELIM = Yes"
Public Const conRequestorYes = "PER_REQUESTOR = Yes"
Public Const conResearchYes = "PER_RESEARCH = Yes"
Public Const conROWYes = "PER_ROW = Yes"
Public Const conSupervisorYes = "PER_SUPERVISOR = Yes"
Click to expand...
[]
Click to expand...
 
Back
Top