VBA Code

To open the References dialog, select References from the Tools menu in the Visual Basic window.

To run commands from controls on your customized Ribbon, you need to write a callback procedure for each Ribbon command button. First, set a reference to the Office 12.0 object library in the References dialog (see Figure 15.6); it is needed to support various Ribbon-related objects in the code.

FIGURE 15.6

Setting a reference to the Office 12.0 object library.

References - Northwind

Available References:

I/! Visual Basic For Applications y Microsoft Access 12.Q Object Library

¡^jlicroson: Word 12.0 Object Library !/! OLE Automation

¡«/I Microsoft Visual Basic for Applications Extensibility 5.: S3 Microsoft Office 12.0 Access database engine Object

0 Microsoft Scripting Runtime

1 i AccessibilityCplAdmin 1,0 Type Library □ Acrobat Access 3,0 Type Library

U AcroIEHelper 1.0 Type Library LI Active DS Type Library D AdiveMovie control type library

ActiveX DLL to perform Miaration of MS Reoositorv V: '

Microsoft Office 12.0 Object Library-

Priority

Help

Location: program FJesY^ommon Rles>1i(Tosoft SharedV3FFICE12\M: Language: Standard

The sample Test Ribbon database contains two procedures to be run from Ribbon buttons on the List Fields custom Ribbon; note the ByVal control As IRibbonControl argument, which links the procedure to the control:

Public Sub ListTableFields(ByVal control As IRibbonControl) On Error Resume Next

First, clear data from the old table of table and field names:

strTable = "zstblTableAndFieldNames" strReport = "zsrptTableAndFieldNames" DoCmd.SetWarnings False strSQL = "DELETE * FROM " & strTable DoCmd.RunSQL strSQL

Fill the table with table and field names, iterating through the databases TableDefs collection:

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strTable, dbOpenTable)

For Each tdf In dbs.TableDefs strTable = tdf.Name

If Left(strTable, 4) <> "MSys" Then Set flds = tdf.Fields For Each fld In flds strFieldName = fld.Name With rst .AddNew

!TableName = strTable !FieldName = strFieldName !DataType = fld.Type

!ValidationRule = fld.ValidationRule !Required = fld.Required .Update End With Next fld End If Next tdf rst.Close

DoCmd.OpenTable strTable strTitle = "Table filled" strPrompt = "Print report now?"

intReturn = MsgBox(strPrompt, vbQuestion + vbYesNo, _

strTitle) If intReturn = vbYes Then strReport = "zsrptTableAndFieldNames" DoCmd.OpenReport strReport End If

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & err.Description Resume ErrorHandlerExit

End Sub

Public Sub ListQueryFields(ByVal control As IRibbonControl)

On Error Resume Next

Dim strQueryName As String Dim qdf As DAO.QueryDef

First, clear data from the old table of query and field names:

strTable = "zstblQueryAndFieldNames" strReport = "zsrptQueryAndFieldNames" DoCmd.SetWarnings False strSQL = "DELETE * FROM " & strTable DoCmd.RunSQL strSQL

Fill the table with query and field names, iterating through the databases QueryDefs collection (only select queries will have their fields listed):

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strTable, dbOpenTable)

For Each qdf In dbs.QueryDefs strQueryName = qdf.Name

Debug.Print "Query name: " & strQueryName If Left(strQueryName, 4) <> "MSys" Then Set flds = qdf.Fields For Each fld In flds strFieldName = fld.Name With rst .AddNew

!QueryName = strQueryName !FieldName = strFieldName !DataType = fld.Type !Required = fld.Required .Update End With Next fld End If Next qdf rst.Close

DoCmd.OpenTable strTable strTitle = "Table filled" strPrompt = "Print report now?"

intReturn = MsgBox(strPrompt, vbQuestion + vbYesNo, _

strTitle) If intReturn = vbYes Then strReport = "zsrptTableAndFieldNames" DoCmd.OpenReport strReport End If

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & err.Description Resume ErrorHandlerExit

End Sub

Once you have created the XML code and stored it in the USysRibbons table, and written any needed callback procedures to run from command buttons on the Ribbon, you need to close the database and reopen it, to load the customized Ribbon(s). Then you have to select the Ribbon you want to use in the database, as described here:

1. Close the database, then reopen it.

2. Click the Office button, then the Access Options button (Figure 15.7).

FIGURE 15.7

Opening the Access Options screen.

FIGURE 15.7

Opening the Access Options screen.

3. Select the Current Database page and select the Ribbon you want to load from the Ribbon Name drop-down list, as shown in Figure 15.8.

FIGURE 15.8

Selecting the Ribbon to load into a database.

FIGURE 15.8

Selecting the Ribbon to load into a database.

4. Close the database and reopen it, and, as in Figure 15.9, now you should see the Ribbon customization.

FIGURE 15.9

The Listing Options tab created by the ListFields custom Ribbon.

FIGURE 15.9

5. To load another Ribbon, select it from the Ribbon Name drop-down list, and close and reopen the database.

You may get an error message like the one shown in Figure 15.10 when reopening a SSeSSB database after creating or editing XML code. Note the line and column reference, which should help in figuring out what the problem is, even if the error description isn't much help. Sometimes it is as simple as a missing bracket.

FIGURE 15.10

An informative error message when loading Ribbon customization XML code.

FIGURE 15.10

0 0

Post a comment