Microsoft Access tips from DataHouseSoftware.

DataHouse - Shareware Invoicing Database, Contact History & Management and More.

Links

Microsoft Access Tips

A few of the functions we've used in DataHouse and Pipeline. Please feel free to use all and any tips, code etc in your own applications. All tips, code etc is provided without any warranty implied, use at your own risk.

ACCESS 2007 RIBBON - Custom ribbon XML and VBA code

We use the ribbon as the primary navigation tool in Pipeline. Creating a ribbon is essentially a two part process; code the xml for the ribbon, and the vba code that executes the action.

The main source of information on Ribbons (including a ribbon customiser) can be found at www.accessribbon.de and we also found the following site useful www.rptsoftware.com

The ribbon is designed with xml and lives in a table called UsysRibbons. The two fields RibbonName and RibbonXML must be included, RibbonXML is where the xml lives. Our table has two records, RibbonName for the two is Form and Report (see below for the xml) When a button is clicked something has to happen (in Pipeline, mostly this is forms opening) In-built buttons such as control idMso="SortUp" require no coding, their action is built in to Access Custom buttons such as button id="DashboardButton" can fire action in code or a macro. We use the code below, fired by the OnAction line in the xml. In the xml shown below the images are the inbuilt Access ones. You can use the callback getImage="onGetImage" to use your own images. Note that image transparencies are not preserved using LoadPicture. To do this you can use the function LoadPictureGDIP from www.accessribbon.de make sure you include basOGL and the correct references.

Some stuff to watch out for;
A lot of instructions for customising ribbons suggest you create a ribbon for your entire database and specify it at Office Menu > Access Options > Current Database > Ribbon and Toolbar Options. We found that when we did this the ribbon disappeared when previewing reports. We solved this problem by not specifying a ribbon for the whole database, instead specifying one for each form and report. All the forms use the Form ribbon and all the reports use the Report ribbon. For forms and reports the ribbon is specified in the properties section under the Other tab.
Each subform that receives the focus must have the ribbon specified. If no ribbon is specified the default Access toolbar will reappear when the subform gets the focus.
You'll need the right references for working with ribbons. Read all about references at http://www.accessribbon.de/en/index.php?Welcome

RIBBON XML FOR FORM (using built in icons in toolbar)


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad">
     <ribbon startFromScratch="true">
         <tabs>
            <tab id="tabHome" label="Pipeline by DataHouseSoftware">
               <group id="grpData" label="Forms"  visible="true">
<button id="DashboardButton" label="Dashboard"  imageMso="OpenStartPage" size="large" onAction="OnActionButton" screentip="Dashboard"/> 
<button id="CompanyButton" label="Companies" imageMso="MeetingsWorkspace" size="large" onAction="OnActionButton" screentip="Companies"/>
<button id="ContactButton" label="Contacts" imageMso="DistributionListSelectMembers" size="large" onAction="OnActionButton" screentip="Contacts"/>
<button id="JobButton" label="Job" imageMso="FileCreateDocumentWorkspace" size="large" onAction="OnActionButton" screentip="Jobs"/>
<button id="FinancesButton" label="Finances" imageMso="Calculator" size="large" onAction="OnActionButton" screentip="Invoices"/>
<button id="ActivityButton" label="Activity" imageMso="TableDrawTable" size="large" onAction="OnActionButton" screentip="Calls"/>
<button id="SearchButton" label="Search" imageMso="FilePrintPreview" size="large" onAction="OnActionButton" screentip="Search"/>
<button id="PipelineButton" label="Pipeline" imageMso="ViewAllProposals" size="large" onAction="OnActionButton" screentip="Pipeline"/>
<button id="ContractorsButton" label="Contractors" imageMso="CreateTableTemplatesGallery" size="large" onAction="OnActionButton" screentip="Contractors"/>
<button id="ConsultancyButton" label="Consultancy" imageMso="DatabasePermissionsMenu" size="large" onAction="OnActionButton" screentip="Consultancy"/>
<button id="ReportsButton" label="Reports" imageMso="ChartPrimaryHorizontalGridlines"  size="large" onAction="OnActionButton" screentip="Reports"/>
<button id="AboutButton" label="About" imageMso="FilePackageAndSign" size="large" onAction="OnActionButton" screentip="About"/>
<button id="CloseButton" label="Close" imageMso="PrintPreviewClose" size="large" onAction="OnActionButton" screentip="Close"/>
<button id="ExitButton" label="Exit" imageMso="PictureEffectsShadowGallery" size="large" onAction="OnActionButton" screentip="Exit"/>
               </group>

<group id="grpSearch" label="Tools"  visible="true">
  <control idMso="SortSelectionMenu"/>
  <control idMso="FilterClearAllFilters"/>
  <control idMso="RecordsDeleteRecord"/>
  <control idMso="SortUp"/>
  <control idMso="SortDown"/>
</group>

            </tab>
        </tabs>
    </ribbon>
</customUI>

===================

RIBBON XML FOR REPORT


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="true">
        <tabs>
            <tab id="tabPrintPreview" label="Preview">
               <group id="grpPrint" label="Print"  visible="true">
                    <button idMso="PrintDialogAccess" 
			size="large" label="Print" 
			imageMso="PrintDialogAccess"/>
                </group>
               <group id="grpZoom" label="Zoom"  visible="true">
                    <splitButton idMso="PrintPreviewZoomMenu" size="large"/>
                    <toggleButton idMso="ZoomFitToWindow" size="large"/>
                    <toggleButton idMso="ZoomOnePage" size="large"/>
                    <toggleButton idMso="PrintPreviewZoomTwoPages" size="large"/>
                </group>

                <group id="grpClosePreview" label="Close Preview"  visible="true">
                    <button idMso="PrintPreviewClose" 
			size="large" label="Close Preview" 
			imageMso="PrintPreviewClose"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

===================

VBA Code in Public Functions

Public Sub OnActionButton(control As IRibbonControl)
'Callbackname in XML File "onAction"
On Error Resume Next
    Select Case control.ID
        Case "DashboardButton"
            Dim stDocName As String
            stDocName = "frmDashboard"
            DoCmd.OpenForm stDocName, acNormal
        Case "CompanyButton"
            Dim stDocName2 As String
            stDocName2 = "frmCompany"
            DoCmd.OpenForm stDocName2, acNormal
        Case "ContactButton"
            Dim stDocName3 As String
            stDocName3 = "frmContact"
            DoCmd.OpenForm stDocName3, acNormal
        Case "JobButton"
            Dim stDocName4 As String
            stDocName4 = "frmJob"
            DoCmd.OpenForm stDocName4, acNormal
        Case "FinancesButton"
            Dim stDocName5 As String
            stDocName5 = "frmFinances"
            DoCmd.OpenForm stDocName5, acNormal
        Case "ActivityButton"
            Dim stDocName6 As String
            stDocName6 = "frmActivity"
            DoCmd.OpenForm stDocName6, acNormal
        Case "SearchButton"
            Dim stDocName7 As String
            stDocName7 = "frmSearch"
            DoCmd.OpenForm stDocName7, acNormal
        Case "PipelineButton"
            Dim stDocName8 As String
            stDocName8 = "frmPipeline"
            DoCmd.OpenForm stDocName8, acNormal
        Case "ContractorsButton"
            Dim stDocName9 As String
            stDocName9 = "frmContractors"
            DoCmd.OpenForm stDocName9, acNormal
        Case "ConsultancyButton"
            Dim stDocName10 As String
            stDocName10 = "frmConsultancy"
            DoCmd.OpenForm stDocName10, acNormal
        Case "ReportsButton"
            Dim stDocName11 As String
            stDocName11 = "frmReports"
            DoCmd.OpenForm stDocName11, acNormal
        Case "AboutButton"
            Dim stDocName12 As String
            stDocName12 = "frmAbout"
            DoCmd.OpenForm stDocName12, acNormal
        
        Case "CloseButton"
            DoCmd.Close
            
        
        Case "ExitButton"
            DoCmd.Quit
            
            
    End Select
End Sub


=================

Function onGetImage(control As IRibbonControl, ByRef image)
    Select Case control.ID
    Case "Test1":
        Set image = LoadPicture("F:\DataHouse\Icons\ac0001-64.gif")
    Case "Test2":
        Set image = LoadPictureGDIP("F:\DataHouse\Icons\ac0001-64.ico")
    Case "Test3":
         Set image = LoadPictureGDIP("F:\DataHouse\Icons\ac0001-64.png")
        

                
    End Select
End Function


=================

RIBBON XML FOR FORM (using custom icons, specified using getImage="OnGetImage" )




<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad">
    <ribbon startFromScratch="true">
        <tabs>
            <tab id="tabHome" label="Pipeline by DataHouseSoftware">
               <group id="grpData" label="Forms"  visible="true">
<button id="DashboardButton" label="Dashboard"  getImage="onGetImage" size="large" onAction="OnActionButton"/> 
<button id="CompanyButton" label="Companies" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="ContactButton" label="Contacts" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="JobButton" label="Job" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="FinancesButton" label="Finances" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="ActivityButton" label="Activity" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="SearchButton" label="Search" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="PipelineButton" label="Pipeline" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="ContractorsButton" label="Contractors" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="ConsultancyButton" label="Consultancy" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="ReportsButton" label="Reports" getImage="onGetImage"  size="large" onAction="OnActionButton"/>
<button id="AboutButton" label="About" getImage="onGetImage" size="large" onAction="OnActionButton"/>
<button id="CloseButton" label="Close" getImage="onGetImage" size="large" onAction="OnActionButton"/>
               </group>

<group id="grpSearch" label="Tools"  visible="true">
<control idMso="SortSelectionMenu"/>
<control idMso="FilterClearAllFilters"/>
<control idMso="RecordsDeleteRecord"/>
<control idMso="SortUp"/>
<control idMso="SortDown"/>
</group>


<group id="grpQuit" label="Quit"  visible="true">
<button id="ExitButton" label="Quit" getImage="onGetImage" size="large" onActioan="OnActionButton" screentip="Exit Pipeline"/>
</group>

            </tab>
        </tabs>
    </ribbon>
</customUI>

These Access tips are brought to you by DataHouseSoftware creators of Pipeline and DataHouse