Drag and Drop in Access

Doug Steele shows you how to add drag-and-drop to your Access application using combinations of multi-value and single-value controls.

The ability to drag and drop is easy to implement in Visual Basic, but the Access form model is different, so it’s not nearly as easy to implement it in Access. However, it’s possible to do, although you need to control it all manually. In Figure 1, I show you drag and drop of multiple items in a List box to a Text box.


Figure 1 – Drag and Drop from a list box to a text box

Just to be perfectly clear, I’m talking about dragging and dropping data, not the controls themselves. That means that some controls aren’t conducive to drag-and-drop. For example, you can’t drag a Command button or a Toggle button. As well, some controls are mutually incompatible for dragging and dropping. While you might be able to drag a check box, what would you expect to happen if you dropped it on a list box? On the other hand, if you dragged a check box to a text box, you might want the text box to display True or False, depending on the state of the check box when you dragged it. Rich-text boxes already support drag-and-drop, so I’m going to ignore them.

Let’s consider what makes up a drag-and-drop event. First, you need to detect that the drag has started. Once you’ve got a drag operation underway, you need to be able to detect when (and where) the drag has stopped. If the drag stopped somewhere that can accept a drop, you need to detect that fact. Finally, if you’ve detected a drop, you need to handle the drop event. Microsoft has KB articles that demonstrate one way to implement these events–see http://support.microsoft.com/?id=287642 for Access 2002. In this column, I’m going to extend the implementation of that functionality.

Dragging around

Dragging something requires that the mouse be depressed while dragging. This means that to be able to detect when a drag has started, you can use the MouseDown event for each control from which you want to be able to drag. Even if you’re not actually going to drag from the control when you activate the MouseDown event, there’s no problem with initializing whatever’s required, just in case.

To be able to detect when a drag has stopped, use the MouseUp event for each control from which you want to be able to drag. (If a mouse button is pressed while the pointer is over a control, that control receives all mouse events up to and including the last MouseUp event, regardless of where the mouse pointer actually is when the mouse button is released.)

The actual code you need to add to the MouseDown and MouseUp events of each control you want to be able to be dragged is pretty simple. When the MouseDown event occurs, you want to set global references to the control itself, and to the form on which the control exists, as well as set a flag to indicate that a Drag has started. I use three module-level variables:

  • mfrmDragForm–The form from which the value is being dragged.
  • mctlDragCtrl–The control on mfrmDragForm from which the value is being dragged.
  • mbytCurrentMode–A flag indicating whether the current action is Dragging, Dropping, or nothing.

One way of doing this is to have a DragStart routine, which can be called from the MouseDown event of every control from which you want to be able to drag. The routine looks like this:

Private Sub Text1_MouseDown (Button As Integer, _

  Shift As Integer, X As Single, Y As Single)

    Call StartDrag(Me)

End Sub

And in a module, you'll have something like this:

Sub StartDrag (SourceForm As Form)

  Set mfrmDragForm = SourceForm

  Set mctlDragCtrl = Screen.ActiveControl

  mbytCurrentMode = DRAG_MODE

End Sub

You shouldn’t use Screen.ActiveForm in place of SourceForm because you may be dragging from a subform. It was a somewhat arbitrary decision on my part not to pass the active control as a parameter, based largely on the fact that passing the form meant less typing in each MouseDown event. If it makes you feel better, you can pass the control as well.

I like to take this technique one step further, though, since I consider it a good idea to give the user visual feedback by changing the mouse cursor to indicate that a drag is occurring. I use code similar to that at www.mvps.org/access/api/api0044.htm, although I name my functions SetMouseCursor and SetMouseCursorFromFile, rather than MouseCursor and PointM, so I won’t go into it here. I use a different icon depending on whether I’m dragging a single value or multiple ones, which means that I need to be able to detect which is the case. To do this, I add a fourth variable to what’s set in StartDrag: mbytDragQuantity (a flag to indicate whether I’m dragging a single value or multiple values). I then have a function SetDragCursor that uses that variable to determine which icon to use for the mouse cursor. Since the list box is the only standard Access control that supports multi-selection, the full code for StartDrag looks more like this:

Sub StartDrag(SourceForm As Form)

  Set mfrmDragForm = SourceForm

  Set mctlDragCtrl = Screen.ActiveControl

  mbytCurrentMode = DRAG_MODE

  If TypeOf mctlDragCtrl Is ListBox Then

    If mctlDragCtrl.ItemsSelected.Count > 1 Then

      mbytDragQuantity = MULTI_VALUE

    Else

      mbytDragQuantity = SINGLE_VALUE

    End If

  Else

    mbytDragQuantity = SINGLE_VALUE

  End If

  SetDragCursor

End Sub

If you’re using other controls that support multi-select, you’ll need to add additional cases in the TypeOf check.

Dropping

To detect when the dragging stops, you need a StopDrag event that you can call from the MouseUp event of every control from which you want to be able to drag:

Private Sub Text1_MouseUp (Button As Integer, _

  Shift As Integer, X As Single, Y As Single)

    Call StopDrag

End Sub

The StopDrag event looks something like this:

Sub StopDrag()

  mbytCurrentMode = DROP_MODE

  mbytDragQuantity = NO_MODE

  msngDropTime = Timer()

  SetDragCursor

End Sub

This code resets three variables: the mode (from DRAG_MODE to DROP_MODE), the drag quantity (from either SINGLE_VALUE or MULTI_VALUE to NO_MODE), and the mouse cursor. The code also sets a variable, msngDropTime, to the value of the built-in Timer function. This is important, since I use it in the next procedure to be called, DetectDrop.

Once you know that the dragging has stopped, you need to determine whether the drag ended on a control capable of accepting a drop. As soon as the MouseUp for the previous control has been handled, the MouseMove event of the new control will fire. That means that if you want a control to be capable of accepting a drop, you should be able to use the MouseMove event of that control to invoke the DetectDrop procedure:

Private Sub Text2_MouseMove(Button As Integer, _

  Shift As Integer, X As Single, Y As Single)

  Call DetectDrop(Me, Me!Text2, Button, Shift, X, Y)

End Sub

The DetectDrop procedure is a bit more complex than the two others I’ve shown you so far. The first thing I do is check whether the event was called because a Drop has occurred. I do this by checking whether mbytCurrentMode has been set to DROP_MODE:

Sub DetectDrop(DropForm As Form, DropCtrl As Control, _

                Button As Integer, Shift As Integer, _

                X As Single, Y As Single)

' If a drop hasn't happened, then exit.

  If mbytCurrentMode <> DROP_MODE Then

    SetDragCursor

    Exit Sub

 End If

If this is a Drop, I then check to make sure that this invocation of DetectDrop was called by the MouseMove event that immediately followed the MouseUp event that invoked StopDrag. While I’m sure there are other ways of doing this, I find that comparing the results of the Timer function to the value of msngDropTime set by StopDrag works. If it’s not a match, I exit the routine:

mbytCurrentMode = NO_MODE

If Timer - msngDropTime > MAX_DROP_TIME Then

   Exit Sub

End If

If this is the appropriate invocation, I check that the control isn’t being dropped on itself (this is necessary for those controls that are set up for both dragging from and dropping to). I use the hWnd properties of the controls when comparing the two saved Form references. This lets me handle those situations where there are multiple instances of the same form open. It’s possible that the user might be trying to drag from a specific control on one instance of the form to the same control on another instance of the same form–which I want to support. As a result, I can’t just rely on the name of the form when doing this comparison:

If (mctlDragCtrl.Name = DropCtrl.Name) And _

   (mfrmDragForm.hWnd = DropForm.hWnd) Then

   Exit Sub

End If

Processing the drop

Once I know that a drag-and-drop sequence has occurred, the last remaining thing to do is handle the drop. I do this with a routine called ProcessDrop that’s passed eight parameters:

ProcessDrop mfrmDragForm, mctlDragCtrl, _

            DropForm, DropCtrl, _

            Button, Shift, X, Y

As you’ve probably guessed, this can be the most complicated part, especially when you allow dragging from controls that support multi-selected values. As I alluded to earlier, you may have to make decisions about what controls can drag to which other controls, as well as decisions about what to do if you drag multi-selected values onto controls that are only capable of showing a single value.

The simplest form of the ProcessDrop routine is something like this:

Sub ProcessDrop(DragForm As Form, _

                DragCtrl As Control, _

                DropForm As Form, _

                DropCtrl As Control, _

                Button As Integer, _

                Shift As Integer, _

                X As Single, _

                Y As Single)

  DropCtrl = DragCtrl

End Sub

In other words, copy the current value of the control referenced by DragCtrl to the control referenced by DropCtrl. In this code, I’m using the default properties for the controls, which is usually Value. I suppose that I could have been more explicit and used DropCtrl.Value = DragCtrl.Value.

In real life, though, the ProcessDrop routine is seldom that simple. If DragCtrl is a multi-select list box, for example, then as a bare minimum, you need to process each selected entry in that list box. And what if you’re dragging a multi-select list box to a single-value text box? One solution is to concatenate each of the selected entries from the list box into a single string value:

Dim strSelectedItems As String

Dim varCurrItem As Variant

  If TypeOf DragCtrl Is ListBox Then

    If DragCtrl.ItemsSelected.Count > 0 Then

      For Each varCurrItem In DragCtrl.ItemsSelected

        strSelectedItems = strSelectedItems & _

               DragCtrl.ItemData(varCurrItem) & ", "

      Next varCurrItem

      If Len(strSelectedItems) > 2 Then

        strSelectedItems = Left$(strSelectedItems, _

                           Len(strSelectedItems) - 2)

      End If

      DropCtrl = strSelectedItems

    Else

      DropCtrl = DragCtrl

    End If

  Else

    DropCtrl = DragCtrl

  End If

In this code, if DragCtrl is a list box with more than one row selected, I loop through all of the items in the ItemsSelected collection of that list box, concatenating each value to a string, and then assign the value of that string to the DropCtrl. If the bound column of the list box isn’t the value you want to display, you’ll have to change the line value DragCtrl.ItemData(varCurrItem) to something more appropriate, such as DragCtrl.Column(2, varCurrItem).

Updating multiple values

If DropCtrl is another list box, maybe what you want to do is copy (or move) the selected items from the source list box to the target list box. How you do this, of course, depends on how you populated the list boxes. The sample database in the accompanying Download has an example where I demonstrate how to drag from one list box to another. In this case, the two list boxes are based on a table that has a Selected field in it. One list box represents those records in the table for which the Selected field is False, while the other list box represents those records for which the Selected field is True. This means that ListBoxExample (which I call from ProcessDrop) must be able to update the table and requery both list boxes:

Dim dbCurr As DAO.Database

Dim strSQL As String

Dim strMessage As String

Dim strWhere As String

Dim varCurrItem As Variant

  Set dbCurr = CurrentDb()

  strSQL = "UPDATE Customers SET Selected=" & _

    IIf(DragCtrl.Name = "lstListBox1", "True", "False")

  If (Shift And acShiftMask) = 0 Then

    If DragCtrl.ItemsSelected.Count > 0 Then

      For Each varCurrItem In DragCtrl.ItemsSelected

        strWhere = strWhere & "'" & _

                   DragCtrl.ItemData(varCurrItem) & _

                   "', "

      Next varCurrItem

      If Len(strWhere) > 2 Then

        strWhere = " WHERE [CustomerID] IN (" & _

          Left$(strWhere, Len(strWhere) - 2) & ")"

      End If

    Else

        strWhere = " WHERE [CustomerID] = '" & _

          DragCtrl & "'"

    End If

  End If

  If Len(strWhere) > 0 Then

    strSQL = strSQL & strWhere

  End If

  dbCurr.Execute strSQL, dbFailOnError

  DragCtrl.Requery

  DropCtrl.Requery

In this code, I check from which of the two list boxes I’m dragging. If I’m dragging from lstListBox1 to lstListBox2, I know that I need to change the dragged records from not selected to selected. If I’m dragging from lstListBox2 to lstListBox1, I know I need to change them to not selected.

You may have noticed that in this case I’m using one of the other values passed to the routine from the MouseMove event–specifically, the Shift value. This allows me to add the feature that if you drag from one box to the other while holding down the Shift key, all of the records are dragged, not simply the one(s) you’ve actually selected. (It also allows me to justify why I’m passing those values from the MouseMove event to the DetectDrop routine, and then to the ProcessDrop routine.) I check whether or not the Shift key is depressed with this code:

  If (Shift And acShiftMask) = 0

The expression will be non-zero if the Shift key is depressed when the mouse is dragged. If it was depressed, I don’t bother with a WHERE clause in my SQL statement; I simply change all of the Selected values to either True or False. If the Shift key isn’t depressed, I loop through the list of all selected rows in the list box (using the list box’s ItemsSelected collection) and add each one to the WHERE clause.

Once I’ve created my SQL string, I execute it. I use the Execute method of the DAO Database object to run a SQL statement rather than using the DoCmd.RunSQL because the Execute method doesn’t issue the “You’re about to update n records…” message box. Plus, the Execute method allows you to trap any errors that may occur running the SQL.

Now that I’ve updated the table appropriately, I requery the two list box controls, so that their content reflects the updated table.

Other dragged controls will require still different handling, and how you handle each dragged control may depend on what the drop control is. For example, if you want to be able to drag check boxes to text boxes, presumably what you’d want to appear in the text box is “True” or “False”. On the other hand, if you drag a check box onto another check box, you’d probably want the drop check box to take on the same value as the dragged one. You can do that with code like this, which assumes that you’re supporting dropping check boxes on either text boxes or other check boxes:

If TypeOf DragCtrl Is CheckBox Then

    If TypeOf DropCtrl Is TextBox Then

      DropCtrl = IIf(DragCtrl, "True", "False")

    ElseIf TypeOf DropCtrl Is CheckBox Then

      DropCtrl = DragCtrl

    Else

    End If

  End If

I’ll give one more example: Suppose you want to support dragging an OptionGroup on your form onto a text box. The OptionGroup will have a numeric value associated with it that identifies the control that the user has selected. You could either drag that numeric value to a text box, or determine the text associated with the selected control and drag that text. In your code in ProcessDrop, you’d have to specifically determine which text box is to get just the number and which is to get the text, using code similar to this:

 If TypeOf DragCtrl Is OptionGroup Then

    If TypeOf DropCtrl Is TextBox Then

      Select Case DropCtrl.Name

        Case "txtTextBox1"

          DropCtrl = DragCtrl

        Case "txtTextBox2"

          DropCtrl = ReturnSelectedOption(DragCtrl)

        Case Else

      End Select

    Else

    End If

  End If

The ReturnSelectedOption would look something like this:

Function ReturnSelectedOption( _

  OptionGroup As OptionGroup) As String

Dim ctlCurr As Control

Dim booGetText As Boolean

Dim strSelected As String

For Each ctlCurr In OptionGroup.Controls

  If TypeOf ctlCurr Is OptionButton Or _

     TypeOf ctlCurr Is CheckBox Then

    If ctlCurr.OptionValue = OptionGroup.Value Then

      strSelected = ctlCurr.Name

      booGetText = True

      Exit For

    End If

  ElseIf TypeOf ctlCurr Is ToggleButton Then

    If ctlCurr.OptionValue = OptionGroup.Value Then

      ReturnSelectedOption = ctlCurr.Caption

      booGetText = False

      Exit For

    End If

  End If

Next ctlCurr

If booGetText Then

  For Each ctlCurr In OptionGroup.Controls

    If TypeOf ctlCurr Is Label Then

      If ctlCurr.Parent.ControlName = strSelected Then

        ReturnSelectedOption = ctlCurr.Caption

        Exit For

      End If

    End If

  Next ctlCurr

End If

End Function

Hopefully, you’ll be able to take these various building blocks and combine them into a module that will meet your specific needs.

In this article, I’ve only addressed how to drag and drop from one control to another control in the same Access application. Coming up, I’ll take a look at what can be done to drag from non-Access applications to controls on Access applications.

 Your download file is called   Steele_Drag_Drop.accdb

 

About Doug Steele

Doug Steele has worked with databases-both mainframe and PC-for many years with a major international oil company. In a previous life, he taught introductory computer programming at the University of Waterloo. Microsoft has recognized him as an Access MVP for his contributions to the user community over the years.

Doug is the author of Access Solutions with Arvin Meyer.

This entry was posted in Access Controls. Bookmark the permalink.

One Response to "Drag and Drop in Access"

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.