To quote Chicken Little, “The sky is falling! The sky is falling!” The latest sky-is-falling scenario for Access users is the so-called “combo box bug,” which has been around since at least Access 2.0 and is actually a “bookmark bug.” Plenty of articles have been written lately about how serious this situation is and what can be done about it, some very useful and some by writers who have little or no familiarity with Access or the actual issues involved.
It may be a bug, but it’s been around so long that it almost qualifies as an institution. Nor should we be surprised by much of its behavior. After all, if you remove one leg from a three- legged stool, you aren’t usually astonished when it falls down under you. You can see a parallel of sorts in Excel if you delete a row in the middle of a multiple-column worksheet. The record (row) you deleted is gone, so the cursor positions itself on another record. Does it warn you? No, because you can see it. But if you were manipulating the rows through Visual Basic for Applications (VBA) code and you couldn’t actually see the worksheet, would you be surprised that the cursor suddenly pointed to a different record? The most surprising and serious part of the bug is that it applies to bookmarks (record pointers) set directly in a form using the Bookmark property, not just those set using a recordsetclone (a term I’ll define shortly); so it isn’t just an issue of a pointer to a deleted record.
Does it affect all Access users? Only if your forms use bookmarks. Desktop users working directly in tables or queries, or in forms that don’t use bookmarks for navigation, are not at risk. You are not automatically at risk if you used the Combo Box Wizard to build a standard combo box. The Combo Box Wizard puts you at risk *only* if you added a combo box to the form header and selected the option “Find a record on my form based on the value I selected in my combo box.” Unbound combo boxes (those that don’t write data into a table field) are commonly used in navigation because of their nifty matching features. As you type, the combo box tries to match what you’ve typed to an item in its list, so you don’t have to scroll through the list yourself to find the nearest match. Once you select an item in the list, the combo box passes the value to the code routine that tries to find a matching record in the form’s recordset (another term I’ll define shortly). The Combo Box Wizard (along with many developers) does this by using bookmarks, which is where the bug comes in.
Experts are welcome to skip the next two paragraphs, but for those who are unclear about recordsets, recordsetclones, and bookmarks in a database, let me take a moment to explain. Simply stated, recordsets are Jet database objects that represent the records in a table. To go back to our Excel example, the group of rows in the worksheet can be thought of as the equivalent of a recordset. A recordsetclone is a copy of the current recordset used to find a particular record and return a pointer (bookmark) to it. You do something similar when you use the Find button to search for a value in a spreadsheet column: the cursor doesn’t move until the Find is successful.
You don’t want to lose track of the current record in the recordset while you look for the next one, so you make your recordsetclone (“the Clone”) point to the same record that’s current in the recordset, and then hunt through the Clone for the record you want to find. When you find it, you tell the recordset to move forward or backward the number of records between the bookmark in the recordset and the new bookmark in the Clone. If you’ve deleted a record from the recordset without resynchronizing the Clone’s records to match, then moving the number of records that is correct in the Clone will definitely put you on the wrong record in the recordset. This simplification describes the general effect of the bug, by the way, not the actual source of it.
Microsoft’s own suggestion is to add the command Me.Requery before the Me.RecordsetClone.FindFirst command in the combo box code. See:
Fairly exhaustive recreations of the problem and less rudimentary workarounds have been posted in various Web locations, my favorite being The Access Web article by Keri Hardwick. See:
Andy Baron, well-known developer and author, has posted his RecordDelete() and Resynch() functions on several newsgroups, including microsoft.public.access.formscoding and comp.databases.ms-access, and his code is included in The Access Web’s article. Various other Web site newsgroup postings suggest ways of avoiding the problem by using non-bookmark methods like DoCmd.FindRecord for navigating the recordset. Those afflicted with this bug shouldn’t have too much trouble finding a solution that fits their needs. The choice of method will be based on developer expertise, as well as the particular method’s speed, ease-of-use, and reliability.
Does this mean that developers have to totally rewrite their code? If they used bookmark navigation and never tested to see what happened to the bookmarks when a record was deleted, then they will, indeed, have to rewrite some code. Developers who never used bookmark navigation, or those who already requery the recordset using the Me.Requery command or use one of the posted techniques to reset and resynchronize their bookmarks, can take the weekend off.