Adding Functionality to the Shared Addin

For the Visual Studio add-in I used components of the Access object model to create a table, form, or report programmatically, adding fields to the table and controls to the form or report. To implement this functionality, I needed to write the custom Ribbon's XML code, three button functions, and some supporting code.

To create the Ribbon XML and embed it within the project, first create an XML File item by selecting Project C Add New Item, and then the XML File item in the Add New Item dialog (give the file the name Ribbon.xml), as shown in Figure 16.19.

FIGURE 16.19

Creating an XML File item for Ribbon support.

FIGURE 16.19

Creating an XML File item for Ribbon support.

The XML code for the Ribbon in this add-in is listed here (see Chapter 15 for more detailed information on creating Ribbon XML code):

<?xml version="1.0" encoding="utf-8" ?> <customUI

xmlns="http://schemas.microsoft.com/office/2 006/01/customui"> <ribbon startFromScratch="false"> <tabs>

<tab id="dbDemoTab"

label="Visual Studio Add-in" visible="true"> <group id=MdbAccessObjectsGroupM label=MCreate Access Objects"> <button id=MbtnCreateTableM label=MCreate New Table" enabled="true" imageMso="Table" size="normal"

onAction="CreateTableInDesignView"/> <button id="btnCreateForm" label=MCreate New Form" enabled="true"

imageMso="CreateFormInDesignView" size="normal"

onAction="CreateNewForm"/> <button id=MbtnCreateReportM label=MCreate New Report" enabled="true"

imageMso="CreateReportInDesignView" size="normal"

onAction="CreateNewReport"/> </group> </tab> </tabs> </ribbon> </customUI>

Next, the XML file needs to be treated as a resource within the project, so you don't have to deal with a separate text file. To do this, select Ribbon.xml in the Solution Explorer, and select Embedded Resource as the value for the Build Action property in its properties sheet (see Figure 16.20).

FIGURE 16.20

Selecting Embedded Resource as the Build Action property of the Ribbon.xml file.

Properties

Ribbon.xml File Properties

Properties

Ribbon.xml File Properties

I Build Action

1 Content

Copy to Output D

ire

None

Custom Tool

Compile

Custom Tool Narr

Content

File Name |

How the file relates to the build and deployment processes.

Build Action

How the file relates to the build and deployment processes.

Next, select "CreateObjects Properties" from the bottom of the Project menu (if you are working with a different project, its name appears instead of CreateObjects). Click the Resources tab; if you have not already created a resources file, all you will see is the link shown in Figure 16.21.

FIGURE 16.21

A link to add a resource to a project.

FIGURE 16.21

A link to add a resource to a project.

Click the link to create a resources file; a datasheet appears, with columns for Name, Value, and Comment. Drag the Ribbon.xml file from the Solution Explorer to the Resources pane; now, as in Figure 16.22, you will see an icon for the Ribbon.xml file.

FIGURE 16.22

The Ribbon.xml file added to the project's resources file.

FIGURE 16.22

Each of the buttons in the Ribbon XML code needs its own procedure; the three button procedures, plus a standard add-in error handler (same as in the VB 6 COM add-in) are listed next:

Public Sub CreateNewTable(ByVal control As _ Microsoft.Office.Core.IRibbonControl)

On Error GoTo ErrorHandler

Dim strSQL As String Dim strTable As String Dim obj As AccessObject strTable = "tblTest"

strSQL = "CREATE TABLE " & strTable & " (FileDate DATE, " _ & "FileNumber LONG, FileName TEXT (100), " _ & "Current YESNO);" Debug.Print("SQL Statement: " & strSQL)

Create table if it doesn't already exist.

For Each obj In appAccess.CurrentData.AllTables If obj.Name = strTable Then 'Table already exists GoTo ErrorHandlerExit End If Next

Create the table.

appAccess.DoCmd.RunSQL(strSQL)

ErrorHandlerExit: Exit Sub

ErrorHandler: AddlnErr(Err) Resume ErrorHandlerExit

End Sub

Public Sub CreateNewForm(ByVal control As _ Microsoft.Office.Core.IRibbonControl)

On Error GoTo ErrorHandler

Dim frm As Microsoft.Office.Interop.Access.Form Dim txt As Microsoft.Office.Interop.Access.TextBox Dim lbl As Microsoft.Office.Interop.Access.Label Dim cbo As Microsoft.Office.Interop.Access.ComboBox Dim lst As Microsoft.Office.Interop.Access.ListBox Dim chk As Microsoft.Office.Interop.Access.CheckBox

Create a new form.

frm = appAccess.CreateForm() frm.RecordSource = "tblTest"

txt = appAccess.CreateControl(FormName:=frm.Name, _ ControlType:=Microsoft.Office.Interop.Access.AcControlType.acTextBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=0, Width:=2500, Height:=400)

lbl = appAccess.CreateControl(FormName:=frm.Name, _

ControlType:=Microsoft.Office.Interop.Access.AcControlType.acLabel, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=1000, Width:=2500, Height:=400)

cbo = appAccess.CreateControl(FormName:=frm.Name, _

ControlType:=Microsoft.Office.Interop.Access.AcControlType.acComboBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=2000, Width:=2500, Height:=400)

lst = appAccess.CreateControl(FormName:=frm.Name, _

ControlType:=Microsoft.Office.Interop.Access.AcControlType.acListBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=3000, Width:=2500, Height:=400)

chk = appAccess.CreateControl(FormName:=frm.Name, _

ControlType:=Microsoft.Office.Interop.Access.AcControlType.acCheckBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=4000, Width:=2500, Height:=400)

ErrorHandlerExit: Exit Sub

ErrorHandler: AddlnErr(Err) Resume ErrorHandlerExit

End Sub

Public Sub CreateNewReport(ByVal control As _ Microsoft.Office.Core.IRibbonControl)

On Error GoTo ErrorHandler

Dim rpt As Microsoft.Office.Interop.Access.Report Dim txt As Microsoft.Office.Interop.Access.TextBox Dim lbl As Microsoft.Office.Interop.Access.Label Dim cbo As Microsoft.Office.Interop.Access.ComboBox Dim lst As Microsoft.Office.Interop.Access.ListBox Dim chk As Microsoft.Office.Interop.Access.CheckBox

Create a new report.

rpt = appAccess.CreateReport() rpt.RecordSource = "tblTest"

txt = appAccess.CreateReportControl(ReportName:=rpt.Name, _ ControlType:=Microsoft.Office.Interop.Access.AcControlType.acTextBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=0, Width:=2500, Height:=400)

lbl = appAccess.CreateReportControl(ReportName:=rpt.Name, _

ControlType:=Microsoft.Office.Interop.Access.AcControlType.acLabel, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=1000, Width:=2500, Height:=400)

cbo = appAccess.CreateReportControl(ReportName:=rpt.Name, _ ControlType:=Microsoft.Office.Interop.Access.AcControlType.acComboBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=2000, Width:=2500, Height:=400)

lst = appAccess.CreateReportControl(ReportName:=rpt.Name, _ ControlType:=Microsoft.Office.Interop.Access.AcControlType.acListBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=3000, Width:=2500, Height:=400)

chk = appAccess.CreateReportControl(ReportName:=rpt.Name, _ ControlType:=Microsoft.Office.Interop.Access.AcControlType.acCheckBox, Section:=Microsoft.Office.Interop.Access.AcSection.acDetail, _ Left:=0, Top:=4000, Width:=2500, Height:=400)

ErrorHandlerExit: Exit Sub

ErrorHandler: AddlnErr(Err) Resume ErrorHandlerExit

End Sub

Public Sub AddInErr(ByVal errX As ErrObject) Displays a message box with error information.

Dim strMsg As String strMsg = _

"An error occurred in the Extras add-in" _ & Microsoft.VisualBasic.Constants.vbCrLf _ & "Error #:" & errX.Number _ & Microsoft.VisualBasic.Constants.vbCrLf _ & "Description: " & errX.Description MsgBox(strMsg, MsgBoxStyle.Critical, "Error!")

___________________________________________________..jt-, ,_______________jnum reference, Microsoft.Office.Interop.Access.acControlType.

If you turn on the Error List pane (View C Error List), you will see a number of warnings about implicit conversion of a variable from Access control to a specific Access control type (see Figure 16.23).

In general, you can clear up conversion warnings by using a conversion function in the code, but Visual Studio 2005 doesn't have any conversion functions for Access controls, so you just have to live with the warnings (the code will run fine).

End Sub

End Sub

___________________________________________________..jt-, ,_______________jnum

FIGURE 16.23

Implicit conversion warnings in the Error List.

FIGURE 16.23

Implicit conversion warnings in the Error List.

If you are very skilled at writing code, and very lucky, at this point you could install and test the code, and everything would work perfectly. But it's more realistic to expect that there will be some bugs in the code that will prevent the add-in from working, so it's a good idea to do some debugging at this point, to save the time you would spend going through the installation and then finding that the add-in doesn't work as expected.

0 0

Post a comment