How to simulate drag-and-drop capabilities in Access 2002

DragStart, DragStop

Syntax:

Sub DragStart(DragFrm As Form)

Sub DragStop()

Example:

Private Sub MyControl_MouseDown(Button As Integer, Shift As Integer, ...             
   DragStart Me
End Sub

Private Sub MyControl_MouseDown(Button As Integer, Shift As Integer, ...
   DragStop
End Sub

DropDetect

Syntax:

Sub DropDetect(DropFrm As Form, DropCtrl As Control, _
   Button As Integer, Shift As Integer, _
   X As Single, Y As Single)

• DropFrm is the form containing the control being dropped on.
• DropCtrl is the control being dropped on.
• Button, Shift, X, Y are the parameters from the MouseMove event.
  

Example:

Private Sub MyControl_MouseMove(Button As Integer, Shift As ...

   DropDetect Me, Me![Employee ID], Button, Shift, X, Y
End Sub

DragDrop

Syntax:

Sub DragDrop (DragFrm As Form, DragCtrl As Control, DropFrm As Form, _
   DropCtrl As Control, Button As Integer, Shift As Integer, _
   X As Single, Y As Single)

• DragFrm is the form containing the control being dragged.
• DragCtrl is the control being dragged.
• DropFrm is the form containing the control being dropped on.
• DropCtrl is the control being dropped on.
• Button is the state of the mouse buttons when the drop occurred.
• Shift is the state of the SHIFT, CTRL, ALT keys when the drop occurred.
• X, Y are the x and y coordinates of the mouse where the drop occurred.
  
  NOTE: For more information on Button, Shift, X, Y arguments, search Help for the MouseMove event.
  

Example:

Sub DragDrop (DragFrm As Form, DragCtrl As Control, ...
   On Error Resume Next
   DropCtrl = DragCtrl
   If Err Then MsgBox Error$
End Sub

EXAMPLE 1: Drag and Drop Between Controls on Two Forms

1. Open the sample database Northwind.mdb.
   
2. Create a new module and type the following lines in the Declarations section:

   Option Explicit
   
      Dim DragFrm As Form
      Dim DragCtrl As Control
      Dim DropTime
   
      Const MAX_DROP_TIME = .1
   
      Dim CurrentMode As Integer
      Const NO_MODE = 0
      Const DROP_MODE = 1
      Const DRAG_MODE = 2
   

3. Type the following three procedures:

   Sub DragStart (SourceFrm As Form)
      ' NOTE: You should not use Screen.ActiveForm in place of
      ' SourceFrm because you may be dragging from a subform.
      Set DragFrm = SourceFrm
      Set DragCtrl = Screen.ActiveControl
      CurrentMode = DRAG_MODE
   End Sub
   
   Sub DragStop ()
      CurrentMode = DROP_MODE
      DropTime = Timer
   End Sub
   
   Sub DropDetect (DropFrm 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 CurrentMode <> DROP_MODE Then Exit Sub
      CurrentMode = NO_MODE
   
      ' The timer interval is permitted between the MouseUp event and
      ' the MouseMove event. This ensures that the MouseMove event does
      ' not invoke the Drop procedure unless it is the MouseMove event
      ' that Microsoft Access automatically fires for the Drop control
      ' following the MouseUp event of a drag control. Subsequent
      ' MouseMove events will fail the timer test and be ignored.
      
      If Timer - DropTime > MAX_DROP_TIME Then Exit Sub
   
      ' Did we drag/drop onto ourselves?
      If (DragCtrl.Name <> DropCtrl.Name) Or _
            (DragFrm.hWnd <> DropFrm.hWnd) Then
         ' If not, then a successful drag/drop occurred.
         DragDrop DragFrm, DragCtrl, DropFrm, DropCtrl, Button, Shift, X, Y
      End If
   End Sub
   
   

4. Type the following DragDrop procedure.
   
   
   NOTE: This procedure does not have to appear in the same module as the procedures typed above. Because you will be customizing the contents of the DragDrop procedure to respond to a successful drag-and-drop operation, it may be better to place it in a separate module.

   Sub DragDrop (DragFrm As Form, DragCtrl As Control, _
      DropFrm As Form, DropCtrl As Control, _
      Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
   
      On Error Resume Next
      DropCtrl = DragCtrl
      If Err Then MsgBox Error$
   End Sub
   

5. Open the Orders form in Design view and add the following OnMouseMove event procedure code for the Salesperson combo box:
   
   

   Private Sub EmployeeID_MouseMove (Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
      DropDetect Me, Me![EmployeeID], Button, Shift, X, Y
   End Sub
   

   NOTE: This control will only be a drop target.
   
6. Open the Employees form in Design view and set the Enabled property of the EmployeeID text box to Yes.
   
7. Add the following OnMouseDown event procedure code for the EmployeeID control:
   
   

   Private Sub EmployeeID_MouseDown (Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
      DragStart Me
   End Sub
   

   NOTE: This control will only be a drag target.
   
8. Add the following OnMouseUp event procedure code for the EmployeeID control:

   Private Sub EmployeeID_MouseUp (Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
      DragStop
   End Sub
   

9. View the Orders form in Form view and position the form in the upper-left corner of the screen.
   
10. View the Employees form in Form view and position it on top of the Orders form, but to the right in such a way that the Salesperson combo box on the Orders form is still visible.
    
11. Hold down the mouse button in the EmployeeID text box in the Employees form, move the mouse pointer (still holding the button down) over the Salesperson combo box on the Orders form, and then release the mouse button.
    
    
    RESULT: The Salesperson combo box will contain the salesperson that you dragged from the Employees form. Try moving to different Employee records and dragging the Employee ID to the Salesperson combo box.
    

EXAMPLE 2: Drag and Move Items from One List Box to Another List Box

1. Complete the first four steps of "EXAMPLE 1" to create the Drag and Drop procedures in the Northwind database.
   
2. Open the Customers table in Design view and add the following new field:

   
   Field Name: Selected
   Data Type : Yes/No
    

3. Create a new, blank form with the following properties:

   
      Form: List Box Example
      ----------------------
      Caption: ListBoxes
      RecordSelectors: No
      NavigationButtons: No
      ScrollBars: Neither
   
      List box
      --------------------------------------------------------------
      Name: List1
      RowSourceType: Table/Query
      RowSource: SELECT CustomerID, CompanyName FROM Customers WHERE
      Selected=False ORDER BY CompanyName;
      ColumnCount: 2
      ColumnWidths: 0
      Width: 1.5"
      Height: 1.5"
   
      List box
      --------------------------------------------------------------
      Name: List2
      RowSourceType: Table/Query
      RowSource: SELECT CustomerID, CompanyName FROM Customers WHERE
      Selected=True ORDER BY CompanyName;
      ColumnCount: 2
      ColumnWidths: 0
      Width: 1.5"
      Height: 1.5"
    

4. Enter the following event procedures for the List1 and List2 mouse events:

   Private Sub List1_MouseDown (Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
      DragStart Me
   End Sub
   
   Private Sub List1_MouseMove (Button As Integer, Shift As Integer, _
         X As Single, Y As Single)
      DropDetect Me, Me![List1], Button, Shift, X, Y
   End Sub
   
   
   Private Sub List1_MouseUp (Button As Integer, Shift As Integer, _
         X As Single, Y As Single)
      DragStop
   End Sub
   
   Private Sub List2_MouseDown (Button As Integer, Shift As Integer, _
         X As Single, Y As Single)
      DragStart Me
   End Sub
   
   Private Sub List2_MouseMove (Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
      DropDetect Me, Me![List2], Button, Shift, X, Y
   End Sub
   
   Private Sub List2_MouseUp (Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
   
      DragStop
   End Sub
   

5. Modify the DragDrop procedure created in step 4 in "Example 1" to read as follows:

   Sub DragDrop (DragFrm As Form, DragCtrl As Control, _
      DropFrm As Form, DropCtrl As Control, _
      Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
   
      ' Which form was dropped on?
      ' It is a good idea to use the DragDrop procedure to
      ' determine which drag-and-drop operation occurred; then call
      ' appropriate code to handle the special cases.
      Select Case DropFrm.Name
         Case "List Box Example"
            ListBoxExample DragFrm, DragCtrl, DropFrm, DropCtrl, _
               Button, Shift, X, Y
         Case Else
            ' For all other cases, copy contents of Drag to Drop
            ' control.
            On Error Resume Next
            DropCtrl = DragCtrl
            If Err Then MsgBox Error$
      End Select
   
   End Sub
   

6. Type the following new procedure in the module with the DragDrop procedure:

   Sub ListBoxExample (DragFrm As Form, DragCtrl As Control, _
      DropFrm As Form, DropCtrl As Control, _
      Button As Integer, Shift As Integer, _
      X As Single, Y As Single)
   
      Dim DB As DAO.Database
      Dim SQL As String
   
      Set DB = CurrentDB()
   
      ' Create SQL statement to update Selected field of
      ' .. drag/dropped list box item.
      SQL = "UPDATE Customers SET Selected="
   
      ' Drag from List1 toggle Selected=True, List2 toggles False.
      SQL = IIF(DragCtrl.Name = "List1", SQL & "True", SQL & "False")
      ' If CTRL key not used, alter dragged value only.
      If (Shift And CTRL_MASK) = 0 Then
         SQL = SQL & " WHERE [CustomerID]='" & DragCtrl & "'"
      End If
   
      ' Run update query to toggle Selected field of Customer record(s).
      DB.Execute SQL
   
      ' Requery the list box controls to show update lists.
      DragCtrl.Requery
      DropCtrl.Requery
   
   End Sub
   

7. View the List Box Example form in Form view, and then drag company names from one list to the other and vice versa. Press the CTRL key and drag to move ALL rows from one list to the other list.