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 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.
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.