Access Cascading Combo Boxes Continuous Form
This article is a continuation or rather an extension from Cascading Combos and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous article first if you have little or no experience with the concept.
Cascading or Synchronized Combos are relatively easy to create and are quite ubiquitous in Access applications. However, the simple techniques in the previous article fail in continuous forms and datasheets, and there are frequent requests for solutions that work in them too.
I will try to explain the problem, and also why it is not likely to be solved in a future version of Access or any other database front-end environment. The situation is in fact best avoided altogether, for good reasons. If it needs to be done, there are no perfect workarounds, and developers who believe they need this feature are likely to be somewhat disappointed by the end result.
The attached demo file is identical with that of the previous article; you only need to download it once.
CascadingCombos.mdbThe Problem
Based on the previous article, the cascading combo boxes of the figure have been created on a continuous form. This is the intended look, and it works above because the current record has no category selected, meaning that all products are displayed in the products combo. However, during normal data entry or navigation, the form will look like this:
Only products in the category of the current record (!) are visible, all others have vanished momentarily.
The query used as row source for the product combo is:
PARAMETERS cboCategory Byte;
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE cboCategory Is Null Or cboCategory=CategoryID
ORDER BY ProductName
The list is filtered based on the currently selected category. In order to make the design work, it was necessary to add the event handler `form current´, performing a requery of the products combo for the current category. But this affects all rows, not only the current row.
If the value of a combo box can be bound to a field, and thus display a different value on each row, the combo's row source cannot. The same row source is used in all visible instances of the combo. In other words, if the current filter shows only confections, non-confection product names will not be displayed. This is perceived as a major annoyance, conceivably so, and the same can be observed in datasheet view.
Why this isn't a Priority
You might not want to read this right now and scroll past to find an actual solution, but please scroll back later, as this is in fact a solution!
The fundamental flaw in the figures above is that the category has been made a field in this test table. That goes against the recommendations of data normalization, as the category can be derived from the product. As such, it isn't original information, as soon as a product has been selected from the category.
The choice of a category is really only an intermediate step, meant to locate a particular product faster (or perhaps to regroup visually similar products while speaking with a client). Once a product has been selected, the information is redundant, and should be discarded.
Since experienced database designers will never produce a continuous form or a datasheet with cascading combos, there is practically no pressure to create an integrated solution for this problem. If you feel the need to create cascading combo boxes in such a view, you might want to have your table design checked by a specialist. I'm not saying it is always wrong, but I have never seen a situation where cascading combos were truly needed or even desirable on a datasheet.
For example, if data entry is often made by category, the category combo can be placed on the form header and simultaneously filter the products combo and the items in the datasheet themselves. This creates a simple interface, easy to understand and use. When the filter isn't used, all records are displayed and the product combo isn't filtered either.
Note that the field CategoryID is removed from the table entirely. Whenever that information is needed, it is obtained via the products table.
For this solution to work, the form's filter is manipulated to reflect the same filtering as the combo box. Building a filter through Visual Basic code isn't as problematic as building an entire SQL query. The form's module is quite simple in the end.
Option Explicit Private Sub cboCategory_AfterUpdate() If IsNull(cboCategory) Then 'Me.Filter = "" Me.FilterOn = False Else Me.Filter _ = "ProductID In (" _ & " Select ProductID From Products" _ & " Where CategoryID=" & cboCategory _ & " )" Me.FilterOn = True End If cboProduct.Requery End Sub Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer) Me.Filter = "" cboCategory = Null cboProduct.Requery End Sub The subquery is perhaps the only difficulty. It reads quite simply "the product ID must be in the list of products assigned to the combo's current category ID". When the user removes the filter, it is actually deleted and the combo set to Null so that the only way to reset the filter is to reselect a category.
General Solution
This is rather easy to implement and works both for continuous forms and datasheets. However, it isn't exact and requires a relaxed view on cascading combos. Let's first admit that the selection of a category is merely an aid, and that the same data could be entered in reverse order (especially as the category can be deduced from the product). Second, we must assume that users are not fundamentally stupid (to some developers, that's a stretch, I know...)
The user really only wants to see the products of the selected category first, at the top of the list. If other products appear below, it isn't a problem. On the contrary, it provides one more way to correct a mistake made in choosing the category. Anyway, the query for the products combo is updated to sort conditionally instead of filtering.
PARAMETERS cboCategory Byte;
SELECT ProductID, ProductName, CategoryID, UnitPrice
FROM Products
ORDER BY cboCategory=CategoryID, ProductName
Compared to the query in the first section, the WHERE clause has been removed and the predicate has become a sorting expression. Products from the current category appear first, but they are all still part of the list. A separator line can be added between them, using a UNION query:
PARAMETERS cboCategory Byte;
SELECT ProductID, ProductName, CategoryID, UnitPrice, cboCategory=CategoryID As InCateg
FROM Products
UNION
SELECT Null, '————————————', cboCategory, Null, False
FROM Products
ORDER BY InCateg, ProductName
The result looks professional and is quite usable, even in datasheet view.
When synchronized combo boxes are meaningful or required in a continuous form or in a datasheet, this is probably the best solution. Please try it out in the demo database.
Option Explicit Private Sub cboProduct_AfterUpdate() cboCategory = cboProduct.Column(2) End Sub Private Sub cboProduct_Enter() cboProduct.Requery End Sub The form's `current´ event is no longer used. Instead the requery is performed in the combo's `enter´ event. In case a product from another category is selected, that category ID is written back to the category combo.
The Z-Order Hack
A mysterious title for an exact solution, one that I hope you will never need. I developed this in quite a different context involving however a TextBox and a ComboBox on a continuous form. The combo needed to overly the text, and was reduced to only the arrow in its "sleep state". The first version didn't look good, because the combo would be visible on all records, but that was solved by changing the Z-Order, hence the title. You can use "send to back" on a control (this affects the order in which the controls are displayed, or their order on an imaginary z-axis perpendicular to the screen). This placed the control behind all others.
When a control gets the focus, it is temporarily moved to the top of the stack, to make sure it is visible. Luckily, this only happens on the current record. In other words, if a combo is hidden behind a text box, it can become visible on the current record only, while remaining hidden on all the others.
So the solution is simple, let's add a text box displaying the product name (it can be obtained directly in the record source query of the form), and hide the misbehaving combo box behind it. In this case, no need to "send the combo to the back", since we are adding the text box after it, hence above it. This is the result:
I have made the text box a little bit too narrow, to show which is visible: the combo behind or the text box above. On the current record, the combo has been moved to the top and can be used as expected; the fact that the same combo is blank for other records is no longer visible.
____________________
Note: this solution isn't original or unique: I found it already published while doing research for this series of articles. I'm saying that I can't claim to have invented it first, but I don't feel the need to link to these pages either (you will find some easily if you look for them).
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Instead, I want to insist on the fact that this is a "hack", with several little problems.
The user isn't made aware that two controls overlap, and this may lead to some frustration. For example, the text box needs to be locked (editing it would not select a product but change the product name in the products table). So the same control will appear to be locked or not, depending on how the user selected it.
Since the text box is "on top", clicking anywhere in the field will give it the focus; typing is prohibited.
Clicking on the drop-down arrow opens the list of products and also allows typing to take advantage of the auto-select feature.
In order to procure a logical tab order, the developer will probably remove the tab stop from the text box (tabbing from the category selects the products combo), but the user is again not aware of that fact.
Tabbing from the text box will likely select either the combo (the tab key seems stuck) or the next control. In either case, the tab order will not always be coherent.
Finally, this obviously doesn't work at all in a datasheet!
If you use this solution, make sure to test various paths and sequences of events during data entry, using the mouse and the keyboard randomly. Careful adjustments to tab stops, tab order, and perhaps some additional event handlers can make the editing operations feel almost normal.
Conclusion
In a nutshell, cascading combos on a datasheet are more a symptom than a problem to be solved. I hope I have made the reasoning behind this statement understandable and applicable to your databases. Moving the filter outside of the form is one solution; another would be to provide a completely separate data entry form, one item at a time, hence not in continuous form mode.
If the problem cannot be removed entirely, two other solutions have been presented, but both have some limitations and might not produce exactly the result you were looking for. I do hope one of them suits your needs.
Happy hacking!
Markus G Fischer
(°v°)
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.
Source: https://www.experts-exchange.com/articles/5950/Cascading-Combos-on-Continuous-Forms-and-Datasheets.html
0 Response to "Access Cascading Combo Boxes Continuous Form"
Post a Comment