MS Access: An Ordinal Curiosity (1 Viewer)

[Uncle Gizmo]

**MS Access: An Ordinal Curiosity**

I recently stumbled upon an intriguing problem on the Access World Forums that led me down a rabbit hole of discovery. Combo boxes in MS Access have always fascinated me with their power and flexibility. They are versatile tools that, with a little VBA code, can do wonders. My journey with VBA began by manipulating combo boxes, which is why this discovery piqued my interest.

**The Ordinal Curiosity**

So, I encountered this peculiar feature of combo boxes—something I now call the "ordinal curiosity." The idea is that combo boxes can return ordinal positions (0, 1, 2) instead of the usual text values. My initial reaction was, "Wow, this is interesting! I can definitely make something out of this." And I did. But as I delved deeper, I realized I was essentially reinventing the wheel. There were far more straightforward and effective ways to achieve the same result.

**A Novel but Quirky Discovery**

Despite my realization, I couldn't shake off the curiosity. This novel feature I had accidentally discovered deserved at least a blog post and some discussion on the forum. While I couldn't find a practical use case for it, I thought it would be interesting to share my journey and the quirks of this ordinal feature. After all, even if it doesn't revolutionize your workflow, it might stretch your cerebral muscles a bit!

**The Original Problem**

search form by field yes / no
The forum question that started it all was about using a combo box to filter data, particularly Boolean fields in a subform. My initial attempts to use this ordinal feature to solve the problem were met with challenges. When I selected "all" in the combo box, the display vanished, showing an empty box. This wasn't right.

I tried everything—every trick and technique I could think of—to fix the issue. Eventually, I discovered that the combo box was returning ordinal positions instead of text values. Microsoft's help documentation confirmed this behavior. While this revelation was interesting, it didn't solve my problem in a practical way.

**The Fun Yet Fruitless Pursuit**

My efforts to turn this into something useful were largely fruitless. The ordinal position method went against the grain of standard practices, and I saw no real benefit in pursuing it further. Developers like doing things in standard ways and will only embrace non-standard methods if they offer significant advantages. Unfortunately, this ordinal curiosity didn't provide those.

**Why Share This?**

So why am I sharing this? Because sometimes, the journey itself is worth sharing. The discovery, the failed attempts, and the realization that some methods, while interesting, are not necessarily useful—all these experiences are valuable.

I've put a lot of effort into this exploration, and it would be great to leverage that effort into an interesting blog. Think of it as a curiosity—an adventure into the quirks of MS Access that might not change your life but could certainly entertain and educate.

**Conclusion**

I couldn't find a decent use case for the ordinal position feature in combo boxes, but maybe you can! If nothing else, it's a testament to the endless quirks and possibilities within MS Access. So, here's to exploring the curious corners of programming, even if they lead us to dead ends.

Feel free to share your thoughts and any examples where you think this feature might actually be useful. Let's keep the discussion going and see where our collective curiosity takes us!

Universal Filter - Nifty Access​

 

[gemma-the-husky]

A bound combo box is set to reference a specified column of the query, normally the first column (column(0)), but it doesn't have to be.

It displays the first non zero width column, which is most likely not the same thing.

When you say ordinal number, I presume you mean cardinal number. Ordinal numbers are first, second, third, etc. The cardinal number returned is most likely the bound numeric column as noted above.

Do you mean the row/item number of the combo box. I've used that with list boxes I believe, without checking carefully, to iterate the selected rows of a list box.

The number of times I return string/text from a combo box is vanishingly small. The only thing I can think of is a US state table, where the bound column will most likely be the 2 char state code, and the displayed column, the state name.

 

[Uncle Gizmo]

I presume you mean cardinal number. Ordinal numbers are first, second, third, etc.

Thank you for your interest and insightful question!

I referred to their ability to return positional indexes (0, 1, 2, etc.) rather than text values. Your distinction between ordinal and cardinal numbers is noted, and "index" might have been a clearer term.

I configured the combo box to a value list with the bound column set to zero, which caused it to return positional indexes instead of text. While this behavior was interesting, it didn’t offer a practical advantage over standard methods. I'm not sure as if there's much use for it!

I appreciate your feedback and hope this clears up the confusion.

 

[gemma-the-husky]

Do you mean that if the combo box just contains a single column of text values, you can still derive the index number of the selected row?

If so, I didn't appreciate you could do that, and I also don't really see a benefit from doing so.

 

[Uncle Gizmo]

I also don't really see a benefit from doing so.

Yes, even with a single column of text values in a combo box, you can derive the index number of the selected row. While this is an interesting quirk, I agree it doesn't offer clear practical benefits.

However, I'm hopeful someone might find a unique use case for it. If anyone has ideas or applications, please share!

 

[MajP]

Bound Column setting	Description
0	The ListIndex property value, rather than the column value, is stored in the current record. The ListIndex property value of the first row is 0, the second row is 1, and so on. Access sets the ListIndex property when an item is selected from a list box or the list box portion of a combo box. Setting the BoundColumn property to 0 and using the ListIndex property value of the control might be useful if, for example, you are only interested in storing a sequence of numbers.
1 or greater	(Default is 1) The value in the specified column becomes the control's value. If the control is bound to a field, this setting is stored in that field in the current record. The BoundColumn property can't be set to a value larger than the setting of the ColumnCount property.

Bound column settings.

ListBox.BoundColumn property (Access)

Office VBA reference topic

learn.microsoft.com

 

[MajP]

I have not come across much need for using the listindex of a combo which is what is returned by binding to 0. I do use it often in code for manipulating listboxes but not by binding it. The sorter relies on the index to swap an item from a higher index to lower index and vice versa. This allows you to move things up and down in the listbox.

MS Access: custom control built in another language

the code is old and there is a small change you need to make in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window If fGetClassName(hWnd_VSB) = "scrollBar" Then this needs to be changed to If fGetClassName(hWnd_VSB) = "NUIscrollBar"...

www.access-programmers.co.uk

 

[The_Doc_Man]

For me, .ListIndex is one of the possible ways (not saying ONLY way) to determine if a selection has been made in a single-select combo or list box. If no selection has been made, it is -1. I have occasionally used the .ListIndex as a way to grab things from the selected row when it has more than one column. Otherwise, I would simply check whether the box had a value at the moment.

 

[Uncle Gizmo]

With the help of Chatty ,I have updated the code, basically added comments to describe what it's doing. As usual, Chatty decided to give a comprehensive explanation, which I have also included!

Option Compare Database
Option Explicit


'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'>>> Database by Tony Hine, alias Uncle Gizmo                                                  <<<
'>>> Created May, 2021                                                                         <<<
'>>> Last up-dated May, 2021                                                                   <<<
'>>> Telephone International: +44 7747 018 875                                                 <<<
'>>> Telephone UK: 07747 018 875                                                               <<<
'>>> Whats App: 07747 018 875                                                                  <<<
'>>> e-mail: tonyhine@lay-away.co.uk                                                           <<<
'>>> Skype: unclegizmo (I seldom Use Skype)                                                    <<<
'>>> I post at the following forum (mostly) :                                                  <<<
'>>> http://www.access-programmers.co.uk/forums/  (alias Uncle Gizmo)                          <<<
'>>> If my e-mail don't work, try this website: http://www.tonyhine.co.uk/example_help.htm     <<<
'>>> The Nifty Access Website is sales orientated:-                                            <<<
'>>> www.niftyaccess.com (A vehicle for me to generate an income from my retirement)           <<<
'>>> My YouTube Channel HERE:- https://www.youtube.com/user/UncleGizmo                         <<<
'>>> CODE SUPPLIED NOT CHECKED AND TESTED FOR ERRORS!!!! Be Warned                             <<<
'>>> What does this mean? You Must setup Test data and                                         <<<
'>>> scenarios to test it works OK in your Database project                                    <<<
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

'################### --------------- Control Subs --------------- #############
Private Sub Reiniciar_AfterUpdate()
Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        If TypeOf Ctrl Is ComboBox Then
            If Ctrl.RowSourceType = "Value List" Then Ctrl.DefaultValue = Reiniciar.Value
        End If
    Next Ctrl

Call fCreateFilter

End Sub

'############## --------------- Private Subs/Functions --------------- ########
Private Function fCreateFilter() As String
    Dim strFilter As String
    Dim Ctrl As Control

    ' Iterate through each control on the form
    For Each Ctrl In Me.Controls
        ' Check if the control is a ComboBox
        Select Case Ctrl.ControlType
            Case acComboBox
                Dim strCritera As String
                ' Check if the ComboBox value is numeric (handles True/False/All scenarios)
                If IsNumeric(Ctrl.Value) Then
                    Select Case Ctrl.Value
                        Case 0, 1
                            ' These character codes are used to construct the filter string dynamically
                            ' Chr(91) = "[", Chr(93) = "]"
                            strCritera = Chr(91) & Ctrl.Tag & Chr(93) & "=" & Ctrl.Value - 1 & " AND "
                        Case 2
                            ' Show all, so no criteria
                            strCritera = ""
                    End Select
                Else
                    ' Handle non-numeric values or empty values
                    If Ctrl.Value = "" Or IsNull(Ctrl.Value) Then
                        strCritera = ""
                    Else
                        ' These character codes are used to construct the filter string dynamically
                        ' Chr(91) = "[", Chr(93) = "]", Chr(39) = "'"
                        strCritera = Chr(91) & Ctrl.Tag & Chr(93) & "=" & Chr(39) & Ctrl.Value & Chr(39) & " AND "
                    End If
                End If

                ' Append criteria to the filter string
                If strFilter = "" Then
                    strFilter = strCritera
                Else
                    strFilter = strFilter & strCritera
                End If
        End Select
    Next Ctrl

    ' Remove the trailing " AND " if any criteria were added
    If Len(strFilter) > 0 Then
        strFilter = Left(strFilter, Len(strFilter) - 5)
    Else
        strFilter = ""
    End If

    ' Display the filter string in a message box for debugging
    MsgBox " >>> " & strFilter, , "The Filter String Contains:-"

    ' Apply the filter to the subform using a With block for tidiness
    With subFrmWinsSfrmBusqueda.Form
        .Filter = ""
        .Filter = strFilter
        .FilterOn = True
    End With

End Function 'fCreateFilter

Chatty's Explanation of Key Parts:

• Chr(91): Represents the opening square bracket [.
• Chr(93): Represents the closing square bracket ].
• Chr(39): Represents the single quote '.

These character codes are used to construct the filter string dynamically, ensuring that field names and values are correctly encapsulated for SQL syntax.

• For Each Ctrl In Me.Controls: Loops through all the controls on the form.
• Select Case Ctrl.ControlType: Checks if the control is a ComboBox.
• IsNumeric(Ctrl.Value): Handles ComboBox values that are numeric, specifically to enable True/False/All scenarios.
• Len(strFilter) > 0: Removes the trailing " AND " from the filter string if any criteria were added.
• MsgBox: Displays the constructed filter string for debugging purposes.
• With block: Tidies up the last three lines by using a With-End With block for setting the filter on the subform.

Note on "Reiniciar": The Spanish word "reiniciar" can translate to both "restart" and "reset" in English, depending on the context.

Functionality Overview: The code loops through all combo boxes and, upon finding one set up to operate on a Boolean checkbox field, uses its value (0, 1, or 2) and its tag property to build the filter string for the form.

Use Case Example: This method of extracting the ordinal position of the text (yes, no, or all) is valuable because it allows the creation of combo boxes with text in different languages, such as English and Spanish, without needing to change the code to reflect different languages. While this demonstrates a possible use case, it's important to note that deviating from standard practices just to use this curiosity is not recommended.

Why Spanish?: The reason this example contains Spanish is because the original poster (OP) was Spanish and provided comments and code in Spanish. This led me to realize that the same code could be used in any language simply by changing the text in the combo box. For instance, you could use one combo box for Spanish, another for English, another for French, and so on.

The function `fCreateFilter()` is called directly from each combo box's After Update event. This is possible because if you precede the function name with an equal sign, the function can be called directly, similar to calling a macro or an event procedure.

(See image below)

**Point of Interest!**
Normally, a function that does not return any data would be designated as a subroutine. However, in this case, the function must be called in this manner, so it cannot be a subroutine; it has to be a function. The call looks like this:

`=fCreateFilter()`

**Note the Equals Sign**

**Adding Multiple Function Calls**
Adding a function call by preceding it with an equal sign so it calls the function directly is a very handy facility in MS Access. This feature allows you to select multiple controls and perform a multiple update quickly and easily. You can do this in the event property of the control, as demonstrated in the following video:

Wire Up Multipe Controls to a Function - Nifty Access​

 

[Uncle Gizmo]

I have found a way to make this a nice usable piece of code! You can switch all the combo boxes between English and Spanish and any other language quickly and easily with this sub routine:-

Private Sub fSetLanguage(strLang As String)
    Dim Ctrl As Control

    ' Loop through all controls on the form
    For Each Ctrl In Me.Controls
        ' Check if the control is a ComboBox
        If TypeOf Ctrl Is ComboBox Then
            ' Check if the ComboBox uses a Value List as its RowSourceType
            If Ctrl.RowSourceType = "Value List" Then
                ' Set the RowSource of the ComboBox to the new language string
                Ctrl.RowSource = strLang
                ' Refresh the ComboBox to display the new values
                Ctrl.Requery
            End If
        End If
    Next Ctrl

    ' Display the language string in a message box for debugging
    MsgBox " >>> " & strLang, , "The String Var strLang Contains:-"

End Sub 'fSetLanguage

For demo purposes this is how you would test the language switching using command buttons:-

Private Sub Command56_Click()
    ' Set ComboBoxes to Spanish values
    Call fSetLanguage("Sí;No;Todas")
End Sub

Private Sub Command57_Click()
    ' Set ComboBoxes to English values
    Call fSetLanguage("Yes;No;ALL")
End Sub

However in reality you would probably want to link it to an option group and select the language using a select case statement