Image of Navigational Map linked to Home / Contents / Search Data1.Recordset.Woops

by Jim Karabatsos - GUI Computing
Image of Line Break

Long-time VB developers are probably wary of the data control in Visual Basic. Let's face it, it was darn near impossible to write professional software in VB3 using the data control. At GUI, we had an unwritten rule that the data control was to be used only for to bind a grid to, and only for browsing. Even that got us into trouble more than once and many times I dispensed with them altogether rather than fight the aggravation.

However, the concept of a data control and data-bound controls is quite nice - all we needed was a good implementation and languages like Delphi proved that this was indeed possible to achieve. A lot of productivity can be gained from the use of this sort of language infrastructure provided, of course, that it works.

Most of the bugs in the data control were fixed in VB4. During my presentation at the Microsoft DevDays conference soon after the introduction of VB5, I made mention of the fact that I believe a lot of developers are wasting development effort because they are not using the data control and associated bound controls where appropriate. Rather, they are laboriously writing code to handle the basic form browsing functionality most applications require.

Now, admittedly, old habits die hard and while I do indeed use data controls more now than I used to, I still do a lot of recordset manipulation in code. Indeed, I consider this to be A Good Thing - keep the user interface layer as loosely coupled to the rest of the application as possible. I tend to create recordsets as stand-alone DAO objects, then if I need to present them to a user I bind a data control to them by assigning the recordset to the data control's Recordset property. If you haven't really looked at data controls since VB3, you might not know that you can now take an existing (ie open) recordset and bind to it, like this:

Dim DB As DAO.Database
Dim RS As Recordset

Dim SQL As String

Set DB = DBEngine.OpenDatabase("D:\Program Files\DevStudio\VB\BIBLIO.MDB")

SQL = "SELECT * FROM AUTHORS"

Set RS = DB.OpenRecordset(SQL, dbOpenDynaset)

Set Data1.Recordset = RS

It was therefore a bit of a surprise to me when one of our clients contacted me a little while ago with a problem using the data control. It turns out that he was using the data control in a way that we never did and so we had never seen this behaviour.

What he was doing was creating a master-detail view of two tables - let's use Publishers and Titles as an example. The source code that accompanies this article demonstrates the problem (however, you will need to point the two data controls to your copy of BIBLIO.MDB).

The demo program uses two data controls, to which we bind one grid each. One data control binds to the Publishers table. When we get a reposition event on that control, we manipulate the second data control to show only those titles published by that publisher. Here is how our client had done this (sort of, anyway - he wasn't using Biblio):

Private Sub Data1_Reposition()

   On Error GoTo Error_Handler

   Data2.RecordSource = "SELECT * FROM TITLES WHERE PubID = " _
                      & Format$(Data1.Recordset!PubID)

   Data2.Refresh

Exit_Point:

   Exit Sub

Error_Handler:

   MsgBox "Error " & Format$(Err.Number) & " - " & Err.Description
   Resume Exit_Point
   Resume ' for debugging

End Sub

Well, it sure looks like it should work, and indeed it does for a while. You can run the demo program and see that the lower grid displays the titles that refer to the currently-selected row in the upper grid.

The fun starts when we get an error in the second grid. The button in the demo application adds a record to the Titles table (which is the detail table, like adding an item to an order). Here is the code:


Private Sub cmdAddRecord_Click()

   On Error GoTo Error_Handler

   With Data2.Recordset
      .AddNew
      .Fields("ISBN") = "1-2345678-9-0"
      .Fields("Title") = "Newly inserted record"
      .Fields("PubID") = Data1.Recordset!PubID
      .Update
   End With
   
   Data2.Refresh
   
Exit_Point:

   Exit Sub
   
Error_Handler:

   MsgBox "Error " & Format$(Err.Number) & " - " & Err.Description

   If Data2.Recordset.EditMode <> 0 Then
      Data2.Recordset.CancelUpdate
      Data2.Enabled = True
   End If
   
   Resume Exit_Point

End Sub

OK, all pretty straight-forward. Again, this is not how I would normally do it, but it should work as written. Make a note of which row you are on in the top grid and press the button ONCE only. The record insertion works and the new record appears. Now click on another row in the top grid and see how everything is still working, with the lower grid changing to show titles referring to the current row in the top one.

Now, go back to the Publisher you selected to insert the new record. Press the button again. Not surprisingly, you get a duplicate key error:

Yep, that's what I would expect. That's indeed why we put an error trap to handle this in the reposition code. We click OK and continue processing. Now try selecting a different row in the top grid. Notice that the bottom grid is not being refreshed. Indeed, we get the following error message from our error handler:

One trick you might want to adopt in your own debugging sessions is to code a redundant Resume statement immediately after your Resume Exit_Point statement , as I have done in the Reposition code above. In normal processing, the second Resume cannot ever be executed. However, this technique lets me put a breakpoint on the Resume Exit_Point statement and, when it is triggered, set the next statement to the Resume statement, which allows me to reset the error handler and find out exactly which statement raised the error. In this case, the culprit was the Data2.Refresh statement.

I tried all manner of things to get this working but to no avail. The only way I could get it to work was to do what I would normally have done anyway, which is to use a stand-alone recordset. I changed the reposition event handler to the following and it worked fine:

Private Sub Data1_Reposition()

   Dim RS As DAO.Recordset

   On Error GoTo Error_Handler

   Set RS = Data1.Database.OpenRecordset( _
            "SELECT * FROM TITLES WHERE PubID = " _
            & Format$(Data1.Recordset!PubID))
   
   Set Data2.Recordset = RS
   Set RS = Nothing

Exit_Point:

   Exit Sub

Error_Handler:

   MsgBox "Error " & Format$(Err.Number) & " - " & Err.Description
   Resume Exit_Point
   Resume
   
End Sub

Let me stress here that there is no reason that the original code should not work. My code is not inherently "better" than the original one and I really do think that the behaviour is a bug, albeit one that is easy to work around. I suspect that the data control is somehow out of sync with the state of the recordset and is trying to manipulate it in inappropriate ways, which is causing the recordset to get upset and go home crying to its mother.

Despite all that, I still maintain that the data control is under-utilised by the majority of VB programmers, especially those who have been in this game for a while. Yes, there are obviously still a few areas that need to be cleaned up. However, the work-around is simple and the development effort savings are very real. The source is available for download.



Written by: Jim Karabatsos
October '98

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]