Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Button = acRightButton Then DatasheetPopups Me, intColCount
End Sub
If so, the MouseUp event calls the DatasheetPopups procedure which determines how many rows, or columns are selected and uses the ShowPopup method to display the appropriate shortcut menu:
Public Sub DatasheetPopups(frm As Form, ColCount As Integer)
Dim strMenu as string
If (frm.SelHeight = 0) And (frm.SelWidth = 0) Then
strMenu = "form datasheet cell"
ElseIf (frm.SelHeight = frm.Recordsetclone.RecordCount) AND (frm.SelWidth = 1) Then
strMenu = "form datasheet column"
ElseIf frm.SelWidth = ColCount Then
strMenu = "form datasheet row"
End If
IF Len(strMenu) > 0 then
commandbars(strMenu).showpopup
Else
msgbox "invalid selection"
End If
End Sub
Note that this procedure requires a ColCount argument, which is the total number of columns in the datasheet; this value is needed to determine whether an entire row (or multiple rows) has/have been selected in the datasheet. However, because a form's recordset may contain fields which are not actually displayed on the form, you cannot simply use the recordset.fields.Count property to determine how many controls are displayed in the datasheet. In order to get this value, you must iterate through the controls on the form and count the non-label controls. I generally declare a variable (intColCount) as an Integer in the forms declaration section, and then call the DatasheetColumnCount function in the datasheets Form_Load event.
Private Sub Form_Load(Cancel As Integer)
'intColCount is declared as Private in the forms declaration section
intColCount = DatasheetColumnCount(Me)
End Sub
The DatasheetColumnCount function looks like:
Public Function DatasheetColumnCount(frm As Form) As Variant
Dim ctrl As Control
Dim intCtrlCount As Integer
DatasheetColumnCount = Null
If frm.CurrentView <> 2 Then Exit Function
For Each ctrl In frm.Section(acDetail).Controls
If ctrl.ControlType <> 100 Then
intCtrlCount = intCtrlCount + 1
End If
Next
DatasheetColumnCount = intCtrlCount
End Function
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
Do you have code to duplicate the built-in commandbars? I'm interested in the Text Filters and Number Filters that leads the users to a dialog box for input. I've got the Sort and other items done, just the sub-menu process is stumping me.
Thanks,
Bill