テーブル/クエリのデータシートビューでイベントを処理する方法
概要
テーブル/クエリのデータシートビューの実体は、フォーム オブジェクトです。
実際のフォーム オブジェクトと比べると、Forms コレクションに追加されないとか、変更を保存することが出来ないなどの違いがありますが、それでもほとんど通常のフォームと変わらず扱うことが出来ます。
ここでは、テーブル/クエリのデータシートビューの実体がフォーム オブジェクトであることを利用し、イベントを掴まえてそのタイミングで任意の処理を行わせる方法を示します。
なお、本来エンドユーザー向けインターフェイスとすべきはフォームであって、テーブル/クエリのデータシートビューをエンドユーザー向けインターフェイスとすべきではありません。
ここで示す手法はあくまでネタ、お遊びであって、実用度はゼロであること（あるいは実用に供すべきではないこと）を、あらかじめご承知おきください。
※ 上記を理解していない初心者に対して、安易に本トピックのリンクを紹介しないよう、重ねてご協力をお願いします。本トピックに限っては、洒落の分かる大人の方だけ、ご利用ください。
詳細
基本は簡単です。
DoCmd.OpenTable "テーブル名"
DoCmd.SelectObject acTable, "テーブル名", False ' 念のため
Screen.ActiveDatasheet.OnClose = "=MsgBox(""さようなら"")"
1 行目で任意のテーブル/クエリを開きます。
2 行目は、開いたテーブル/クエリがアクティブであることを保証するためにオブジェクトを選択しています。通常はこの行が無くてもアクティブになるはずですので、あくまで念のための処理です。
3 行目が肝の部分で、Screen.ActiveDatasheetで、テーブル/クエリのデータシートビューの実体であるフォーム オブジェクトを参照できます。
後は、任意のイベント プロパティに、実行させたい Function プロシージャ名をセットするだけです。
上の例では [閉じる時] イベントに MsgBox 関数をセットしているので、このテーブルを閉じるときには次のようなメッセージが表示されます。
[Msgboxのスクリーンショット]
イベント プロパティ等への変更は、あくまで実行中だけ有効で、閉じた際に保存されたりはしませんので、ご注意を。
後は好きに応用して、遊んでみてください。
以上です。
…え、短すぎる？
うーん、他に解説することが無いので、デモを落としていじってみてください。
この MDB には、以下の 3 つのデモが含まれます。
サイズを記憶するテーブル/クエリ
前回閉じたときのウィンドウサイズを記憶し、次回同じウィンドウサイズで開きます。
更新日時が自動入力されるテーブル
テーブル上で直接データを変更すると、変更確定時に最終更新日時が自動で更新されます。
テーブルによる擬似メイン/サブフォーム連携
2 つのテーブルをメイン/サブフォームのように連携させます。
[商品区分] テーブルで選んだレコードに応じて、[商品] テーブルのレコードが絞り込まれます。
また、片方のテーブルを閉じると、同時にもう片方のテーブルも閉じます。
補足
実際のフォーム オブジェクトには出来て、テーブル/クエリのデータシートビューには出来ないことも、幾つかあります。
WithEvents によるイベントフックが出来ない
コントロールのイベントが利用できない
"OnGotFocus/フォーカス取得後" イベントが発生しない
条件付き書式が利用できない
他にも違いがありそうです。
※ 上記は YU-TANG による確認の結果であり、私が知らないだけで回避方法があるのかもしれません。
なお、この手法が Access 97 以前でも有効かどうかは、確認が取れていません。
フィードバックを歓迎します。
謝辞
本稿執筆に当たって、下記リソースを参考にさせていただきました。
ありがとうございました。
[Picasoのぴー: テーブルの列幅を最適にする}(http://pxp.seesaa.net/article/5184978.html)
Function pflFitBest(pTblName As String)
Dim obj As Object
Dim ctl As Control
DoCmd.OpenTable pTblName, acViewNormal
DoCmd.SelectObject acTable, pTblName
DoEvents
Set obj = Screen.ActiveDatasheet
For Each ctl In obj.Controls
ctl.ColumnWidth = -2
Next
Set ctl = Nothing
Set obj = Nothing
DoCmd.Close acTable, pTblName, acSaveYes
End Function
Access & VBA FAQ - DatasheetBestFit
Pregunta : ¿Qué código tengo que utilizar para que las columnas de una consulta, tabla o formulario en vista hoja de datos se muestren con "ajuste perfecto"? En la ayuda sobre la propiedad ColumnWidth de las columnas de una hoja de datos habla de ocultar columnas, mostrarlas con el ancho predeterminado, o ajustarlas al ancho que yo le ponga, pero no dice nada de la utilidad que aparece en el menú Formato - Ancho de columna... - (cuadro de diálogo) "Ajuste perfecto".
Respuesta : El procedimiento DatasheetBestFit asigna la propiedad ajuste perfecto a todas las columnas de la hoja de datos que esté activa en ese momento.Ejemplo de uso:
El siguiente código muestra en pantalla la consulta "Consulta1" y después llama al procedimiento DatasheetBestFit para ajustar el ancho de sus columnas
DoCmd.OpenQuery "Consulta1"
Call DatasheetBestFit
'---------------------------------------------------------
'
' DatasheetBestFit
'
' Código escrito originalmente por Juan M Afán de Ribera.
' Estás autorizado a utilizarlo dentro de una aplicación
' siempre que esta nota de autor permanezca inalterada.
' En el caso de querer publicarlo en una página Web,
' por favor, contactar con el autor en
'
' accessvbafaq@ya.com
'
' Este código se brinda por cortesía de
' Juan M. Afán de Ribera
'
Sub DatasheetBestFit()
Dim ctl As Control
Const BestFit = -2
On Error Resume Next
With Screen.ActiveDatasheet
For Each ctl In .Controls
ctl.ColumnWidth = BestFit
Next
End With
End Sub
'---------------------------------------------------------
Tech Info - Query Datasheets and Event Functions
不明
Smart Access - You Can Do That with Datasheets?
You Can Do That with Datasheets?
Michael Kaplan
Everyone who uses datasheets knows they're powerful, but very few people know all of the things you can do with them. Michael Kaplan pulls out a handful of tricks from the unlocked Access 97 wizards that can help you really get the best of these useful items.
One of the things that even the most diehard Visual Basic developer admits is that they wish that Visual Basic had a grid as powerful as Access's datasheet. And with all of that power, there are still many things that no one knows how to do with them. Datasheets are routinely dismissed by developers, who seem to favor continuous forms.
However, datasheets provide a simple yet powerful way to display data to your users. Datasheets also allow you to provide a user interface (UI) that your users can configure to meet their needs. Most developers feel that datasheets give too much control to their users. However, by taking control of your datasheets, you can incorporate them into your applications. In this article, I'll show some of the features of these powerful UI objects that most developers don't understand.
Using the "sorta hidden" properties
Datasheets have a lot of properties meant only for them, which are listed in Table 1.
Table 1. The properties in Access related to datasheets.
| Property | Meaning and usage |
|---|---|
| ColumnHidden | Exists on columns in the datasheet, controls whether the column is visible or not. |
| ColumnOrder | Exists on columns in the datasheet, controls the order in which columns are displayed. |
With the exception of the Subdatasheet properties, you have no direct design-time access to these properties: None of these properties show up in the datasheet's property sheet. As a result, they can only be set at runtime from VBA code in order to make changes. Interestingly, none of the properties are exposed by ADO or ADOX, so if you want to change them, you'll have to use DAO.
While you can't access these properties through property sheets, many of them can be set in the user interface. They are, for example, what's changed when you set the font of a datasheet from the Format menu. For full control over the datasheet, though, you'll want to explicitly set the properties in code and save the object when you're done.
It's worth noting that a datasheet is a form—it says so right in the object browser. The object browser considers the datasheet's columns to be the controls on the form. As a result, a datasheet can consist of any control that can be displayed, which means all TextBox, ComboBox, and CheckBox controls.
Okay, enough introduction; I want to show you some of the cool things you can do with datasheets. Obviously, many of the datasheet properties need no explanation: Anyone can tell what a font or a grid property does. Instead, I'll move to less obvious items of interest...Hiding columns at runtime
Sometimes, you want to hide specific columns in a datasheet. For example, the Access ListBox/ComboBox Wizard does this. The wizard uses a generic, 20-column datasheet as part of its user interface. The wizard hides specific columns to limit its display to the number of columns appropriate to the sample data. Even if your needs aren't quite so grandiose, you might want to hide all but the relevant columns on every display. You might even want to provide a bunch of CheckBoxes at the top of the datasheet to allow your users to hide and show columns. I personally find that kind of user-configurable UI really interesting.
To see this in action, you can check out frmHideShowColumns and sfrmHideShowColumns in the sample database in the accompanying Download file. The subform is just a simple datasheet based on the Products table from Northwind, while the form is unbound and contains check boxes to show and hide the various columns on the subform. The frmHideShowColumns form looks like Figure 1.
In truth, most of the work in this form was just in naming all of the controls consistently (I used "ck" as a prefix for every check box on the main form, "tb" as a prefix for every text box on the subform, and the actual field name for the rest of the bound controls on both the form and subform). Once the appropriate names were in place, there were just two steps left:
- Select all of the CheckBoxes an1.d then add a call to a procedure I wrote called ShowHideColumns (see Figure 2).
- Add code behind the form to make sure that the proper CheckBoxes are checked when the form is loaded and that the form is updated whenever a CheckBox value is changed. The code to check the CheckBoxes is in the Form_Load event:
Private Sub Form_Load()
Dim ctl As Control
Dim stCtl As String
Set sfrm = Me.sfrmHideShowColumns
For Each ctl In Me.Controls
If TypeOf ctl Is Access.CheckBox Then
stCtl = "tb" & Mid$(ctl.Name, 3)
ctl.Value = Not sfrm.Form(stCtl).ColumnHidden
End If
Next ctl
End Sub
The code to show and hide the columns is in a routine called ShowHideColumn. You'll notice that it looks a lot like the code in the Form_Load event:
Private Function ShowHideColumn()
Dim sfrm As SubForm
Dim ctl As Control
Dim stCtl As String
Set sfrm = Me.sfrmHideShowColumns
For Each ctl In Me.Controls
If TypeOf ctl Is Access.CheckBox Then
stCtl = "tb" & Mid$(ctl.Name, 3)
sfrm.Form(stCtl).ColumnHidden = _
Not ctl.Value
End If
Next ctl
End Function
Voilà! You can now show and hide columns on demand!
You can certainly expand this technique. Here are some suggested ways to enhance the functionality:
- Instead of touching every column when one is changed, you could take a parameter in the ShowHideColumns function that would contain the control name.
- You might want to keep some columns from being shown, ever.
- Some columns might always need to stay visible.
Repositioning columns—not!
Believe it or not, once users start realizing that they can reconfigure their UI, they can't get enough of it. Many users know that they can just stick the mouse at the header of the column, click to select, and drag the column around. Easy! But this can be very annoying if the result isn't appropriate for the applications—especially if they save those changes!
The problem is that sometimes you want to make sure that your users can't make dramatic changes to the layout of your datasheet. For instance, you might need to make sure that some data is always visible. Unfortunately, there's no property to fix a column in position. However, you can use the events associated with controls to get the same effect.
To control repositioning, you need to use the MouseUp event of the controls in the datasheet. Set the properties for all of the columns to a single procedure in the same way I described in the previous section (and as shown in Figure 2), and then use the following procedure to keep the columns from wandering:
Private Function DontMuckWithSize()
Dim ctl As Control
Dim ictl As Integer
For ictl = 0 To Me.Controls.Count - 1
Set ctl = Me.Controls(ictl)
If TypeOf ctl Is Access.TextBox Then
ctl.ColumnOrder = ictl + 1
End If
Next ictl
End Function
This code takes advantage of the Controls collection of the datasheet. While the user may reposition the column on the screen, the column's postion in the Controls collection doesn't change from what it was when the form was originally displayed. The code just loops through the collection, setting each column's ColumnOrder back to its position in the Controls collection. The 1 that gets added to the ictl variable is just there to handle the difference in the position in the Controls collection (which is 0-based) and the ColumnOrder number (which is 1-based). You can see this in action in the frmNoReposition form in the sample database.
You can obviously do a lot more with the ColumnOrder property. This example always reorders them back the original order, whereas you might actually want to move your columns to a new position. In fact, you can combine ColumnOrder and ColumnHidden to produce all kinds of complicated schemes that allow you to reuse the same subform across several forms. All you have to do is use these properties to change the emphasis to particular fields in the datasheet. My one piece of advice is to make sure your changes are intuitive. It can be very unsettling for users to have things move around on the fly. You should usually restrict these techniques to changing your layout on load of the form and then leaving the UI alone.
Now, the advanced developer who's had to deal with this problem before knows that there's one case that isn't handled by this sort of code. If the user chooses "Freeze Column" from the Format menu, the selected column is moved all the way to the left-hand side of the screen and fixed there, no matter how you move around the datasheet (much like Excel's column freezing capabilities). You can detect this situation by looking at the FrozenColumns property, which indicates how many columns are frozen (it will always start with 1, because Access counts the record selector as a column that's always frozen). If you want to freeze or unfreeze columns from your code, you can use DoCmd.RunCommand, passing acCmdFreezeColumn and acCmdUnfreezeAllColumns as the parameters.
What's missing from this functionality is an event that informs you that the user has frozen some columns, and also a property on each column that tells you which columns have been frozen. If you want to control which columns are frozen, you'll have to check the FrozenColumns property to see whether the number is wrong, unfreeze all of the columns, and then freeze the ones that you want.
Resizing columns
The ability to resize columns programatically to display the data currently in the column is the original reason I wanted to write this article: It's just an amazing feature. The first place I ever saw it was in Access's Normalizer, better known as the Access Table Analyzer (the names of all of the wizard's objects are still prefixed with "NORM_" because the marketing change to "Table Analyzer" didn't force any code changes!). The Normalizer creates a query using DAO, and then it sets the columns of the resulting datasheet to be the appropriate width to display the data in the columns. And remember, tables and queries in Access are displayed using datasheets (at least, when you have them in browse view), which are forms.
The trick to resizing columns automatically to the data currently in them requires two steps:
You must set the datasheet's ColumnWidth properties to -2, a number that seems to mean "best fit" to Access, internally. Access will then change the ColumnWidth property value to an appropriate number (in twips).
To make the change permanent, you have to add a property called ColumnWidth to DAO's Property collection (which won't contain the property by default for all queries) and set the property's value to the same value as the control's ColumnWidth property from Step 1.
To do this, you can use my procedures FixColumnWidthsOfQuery or FixColumnWidthsOfTable and their helpful subroutine, SetDAOFieldProperty:
Public Function FixColumnWidthsOfQuery _
(stName As String)
Dim db As Database
Dim qdf As QueryDef
Dim fld As DAO.Field
Dim frm As Form
Dim ictl As Integer
Dim ctl As Control
Set db = CurrentDb
Set qdf = db.QueryDefs(stName)
DoCmd.OpenQuery stName, acViewNormal
Set frm = Screen.ActiveDatasheet
For ictl = 0 To frm.Controls.Count - 1
Set ctl = frm.Controls(ictl)
ctl.ColumnWidth = -2
Call SetDAOFieldProperty(qdf.Fields(ictl), _
"ColumnWidth", ctl.ColumnWidth, dbInteger)
Next ictl
DoCmd.Save acQuery, stName
End Function
Public Function FixColumnWidthsOfTable _
(stName As String)
Dim db As Database
Dim tdf As TableDef
Dim fld As DAO.Field
Dim frm As Form
Dim ictl As Integer
Dim ctl As Control
Set db = CurrentDb
Set tdf = db.TableDefs(stName)
DoCmd.OpenTable stName, acViewNormal
Set frm = Screen.ActiveDatasheet
For ictl = 0 To frm.Controls.Count - 1
Set ctl = frm.Controls(ictl)
ctl.ColumnWidth = -2
Call SetDAOFieldProperty(tdf.Fields(ictl), _
"ColumnWidth", ctl.ColumnWidth, dbInteger)
Next ictl
DoCmd.Save acTable, stName
End Function
Private Sub SetDAOFieldProperty _
(fld As DAO.Field, _
stName As String, vValue As Variant, _
lType As Long)
Dim prp As DAO.Property
For Each prp In fld.Properties
If StrComp(prp.Name, stName, _
vbBinaryCompare) = 0 Then
prp.Value = vValue
Exit For
End If
Set prp = Nothing
Next prp
If prp Is Nothing Then
Set prp = fld.CreateProperty(stName, _
lType, vValue)
fld.Properties.Append prp
End If
End Sub
You can simply pass the name of any table or query to these routines and let them do the rest!
If you need to do the same task with a Form, it's even easier: Just set all of the ColumnWidth properties to -2. No extra steps are needed.
Conclusion
Really, this article only touched the surface of datasheets. Looking toward Access 2000, there are new features that can be used for datasheets:
- Conditional formatting allows you to change the way individual cells are formatted based on their values (a feature that's been long requested). Given how many people are still using Access 97, I usually recommend that people use Stephen Leban's solution for this problem, because his will work in Access 95, 97, and 2000.
- Subdatasheets are a powerful capability, which can be used to show hierarchical data very effectively. Like other features I discussed, they depend on Jet properties that can only be created/modified via DAO. Unfortunately, you have to upgrade to Access 2000 if you need this functionality.
- For Access 2000, a developer on the Access team noted how datasheets were "forms" in many senses (the primary reason that you view tables and queries as Forms by using the ActiveDatasheet property). Access 2000 takes this a step further to support using tables and queries directly in the Subform control as a SourceObject. You can take advantage of this change to cut down on the number of forms you need in a project by assigning tables and queries directly to Subforms (modifying properties such as ColumnWidth and ColumnHidden at runtime to change their look and feel).
It's clear that datasheets have some real power behind them, in terms of how they can be used by your customers and how they can be manipulated by you. You might find one or more of these tips useful, and even be inspired to find other useful features from datasheets in Access!
To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.