Version 1.1 Approx 1 MB (zipped) First Published 9 Apr 2024
The idea for this article came from a thread by Greg Sevior at UtterAccess forum: Programatically selecting a Column in a datasheet
Greg asked:
Does anyone have any code that will allow you to programmatically select an entire column in a datasheet as if you had clicked on the column title.
I don't mean as in selecting a field/column in a record, I mean physically selecting the viewable column in a datasheet.
He later explained that the purpose was to spell check a selected datasheet column using code.
The standard approach requires users to select a datasheet column by clicking its column header, then click Spelling in the Home ribbon
For such a basic requirement, it might be expected that the Spelling command would be part of the right click context menu for a datasheet column in a form/subform. However that isn't the case
Form Datasheet Column Context Menu
|
Form Datasheet SubColumn Context Menu
|
One solution is to add a Spelling menu item to both CommandBars used to create the Form Datasheet Column and Form Datasheet Subcolumn context menus
MODIFIED Form Datasheet Column Context Menu
|
MODIFIED Form Datasheet SubColumn Context Menu
|
However, whilst this works well, it doesn't satisfy the original request to select a datasheet column using code.
You would expect that it would be simple to do this using VBA. For example, the following code seems like it should work:
CODE:
Private Sub cmdSpellCheckColumn
Me.ControlName.SetFocus
DoCmd.RunCommand acCmdSelectEntireColumn ' this does nothing
End Sub
The code is valid but the acCmdSelectEntireColumn line (which was introduced in Access 2007) appears to do nothing.
I have checked in several versions from Access 2007 onwards. The code has never worked in this context
I put out a request for help in an MVP email group and was very pleased when fellow Access MVP, Gustav Brock, came up with this workaround to select (as an example) column 3.
Me.SelTop = 1 ' select first row
Me.SelWidth = 1 ' select one column
Me.SelLeft = 1 + 3 ' select the third column - add 1 to allow for zero order index
Me.SelHeight = <Count Of Rows/Records> ' get the number of records
This works and can easily be adapted to select a different range. This satisfied the needs of the OP at UtterAccess forum.
Example App
I decided to adapt Gustav's code to create a more generic solution. The example app opens to a startup form with links to 3 other forms
The first form demonstrates how the above code can be used to select a range of rows and columns:
Private Sub cmdSelectRange_Click()
On Error GoTo Err_Handler
Me.fsubProducts.Form.SelTop = cboFirstRow
Me.fsubProducts.Form.SelLeft = cboFirstColumn + 1 ' need to add 1 to select the correct first field
Me.fsubProducts.Form.SelWidth = cboTotalColumns
If cboTotalRows = "ALL" Then
Me.fsubProducts.Form.SelHeight = Me.fsubProducts.Form.RecordsetClone.RecordCount
Else
Me.fsubProducts.Form.SelHeight = Me.cboTotalRows
End If
Exit_Handler:
Exit Sub
Err_Handler:
If Err = 94 Then ' null error if any combos left blank
MsgBox "You must make a selection in all 4 combo boxes", vbInformation, "Range not selected"
Else
MsgBox "Error " & Err & ": " & Err.Description
End If
Resume Exit_Handler
End Sub
NOTE: The selected range can not be copied and pasted into a new file
The second form uses the earlier code together with DoCmd.RunCommand acCmdSpelling to select a column and then run the spell checker.
You can use either of the command buttons to spell check pre-selected columns. For example:
Private Sub cmdSpellProdName_Click()
Me.fsubProducts.SetFocus ' This Line MUST be included or the Spell checker will not function correctly.
Me.fsubProducts.Form.SelTop = 1
Me.fsubProducts.Form.SelWidth = 1
Me.fsubProducts.Form.SelLeft = 1 + 2 ' 2nd column & add 1
Me.fsubProducts.Form.SelHeight = Me.fsubProducts.Form.RecordsetClone.RecordCount ' select all records
DoCmd.RunCommand acCmdSpelling
End Sub
Alternatively, use the combobox to select a column for spell checking. The combobox is populated using a field list for the Products table
Private Sub lstColumns_AfterUpdate()
Dim colIndex As Integer, strField As String, intType As Integer, strType As String
Dim rs As DAO.Recordset, fld As DAO.Field, strSQL As String
colIndex = Me.lstColumns.ListIndex
strField = Me.lstColumns
strSQL = "SELECT " & strField & " FROM Products;"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
For Each fld In rs.Fields
intType = fld.Type
strType = FieldTypeName(fld)
Next
rs.Close
Set rs = Nothing
Me.fsubProducts.SetFocus
Me.fsubProducts.Form.SelTop = 1
Me.fsubProducts.Form.SelWidth = 1
Me.fsubProducts.Form.SelLeft = colIndex + 2 'need to add 2 to the listbox index to select the correct field
Me.fsubProducts.Form.SelHeight = Me.fsubProducts.Form.RecordsetClone.RecordCount
'field type 10 = short text, 12 = long text
If intType = 10 Or intType = 12 Then
DoCmd.RunCommand acCmdSpelling
Else
MsgBox strField & " is a " & strType & " field" & vbCrLf & vbCrLf & _
"The spell checker can only be used on text fields", vbCritical, "Spell Check error"
End If
End Sub
However, the spell checker only works for textbox controls with short text / long text fields.
If you select any other datatype field e.g. number/currency or a combo box field, the code will fail. This could be a frustrating experience for the end user.
The third form allows you to swop subform control sources and select a short text / long text field for spell checking.
In this case, only text fields are displayed in the listbox so no errors occur. This should make for a better user experience.
When the subform is updated, the list of short text / long text fields that can be spell checked is also automatically updated.
Private Sub lstSubform_AfterUpdate()
'display subform for selected table
Me.fsubForm.SourceObject = "fsub" & Me.lstSubform
Me.fsubForm.Visible = True
cmdClearSubform.Enabled = True
cmdClearField.Visible = True
Me.Label2.Visible = True
strTable = Me.lstSubform
'clear columns listbox
Me.lstColumns.RowSource = ""
'get list of text fields and ordinal positions for listbox
strSQL = "SELECT * FROM " & strTable & ";"
Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
For Each fld In rs.Fields
colIndex = fld.OrdinalPosition
strField = fld.Name
intType = fld.Type
strType = FieldTypeName(fld)
'only add field to listbox if short text (10) or long text (12)
If intType = 10 Or intType = 12 Then
Me.lstColumns.AddItem "" & colIndex & "; " & strField & ""
End If
Next
rs.Close
Set rs = Nothing
Me.lstColumns.Visible = True
Me.cmdClearField.Visible = True
End Sub
'=====================================================
Private Sub lstColumns_AfterUpdate()
'run spell checker on selected column
colIndex = Me.lstColumns.ListIndex
intField = Me.lstColumns
strField = Me.lstColumns.Column(1)
Me.cmdClearField.Enabled = True
Me.fsubForm.SetFocus
Me.fsubForm.Form.SelTop = 1
Me.fsubForm.Form.SelWidth = 1
Me.fsubForm.Form.SelLeft = intField + 2 'need to add 2 to the listbox index to select the correct field
Me.fsubForm.Form.SelHeight = Me.fsubForm.Form.RecordsetClone.RecordCount
DoCmd.RunCommand acCmdSpelling
End Sub
I hope this article will be of use to some of you. Do let me know if you have any suggestions for further improvements.
Download:
Click to download:
Datasheet Select Row / Column_v1.1 ACCDB file approx 1 MB (zipped)
Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 9 Apr 2024
|
Return to Example Databases Page
|
Return to Top
|