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)
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)