How do I switch between Access Form and Datasheet views, and remain on the same record, without filtering ?

Question

In MS Access 2007, I want to switch between datasheet and form views, without filtering, and remain on the current record.

Should I use a bookmark ? How ? or How might I place a button on the ribbon to switch views, without having to search for the record or use a filter.

I need this to run Access 2007 Runtime, since it will be implemented on a non-licensed computer. Seems some of the ribbon butttons & groups are not showing even if defined:

I tried the "GroupViews" and "ViewsModeMenu" options in the ribbon but they do not work in the Runtime. Also, any ribbon options that change the view also requery to the first record in the dataset, instead of retaining the current record.

Answer 1

Have you created a custom ribbon (http://www.databasedev.co.uk/access2007ribbon.html)?

This sample RibbonXML creates two buttons, the first to close the form and the second to show a particular form in DatasheetView. You can add a reference to the Ribbon using the Other tab of a form's property sheet.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"&gt;
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="tab1" label="Object Helpers">
                <group id="grp1" label="Helpers">
                    <!-- close current object button -->
                    <button id="btnCloseObject" label="Close Current Object"
                            size="large"
                            imageMso="PrintPreviewClose"
                            onAction="OnCloseCurrentObject"/>
                     <!-- show datasheet -->
                    <button id="btnShowDatasheet" label="Show Datasheet"
                            size="large"
                            imageMso="AccessFormDatasheet"
                            onAction="ShowDatasheet"/>
               </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

You will also need some code and a reference to the Microsoft Office 12.0 or 14.0 Object Library (Tools->References in the code window).

Public Sub OnCloseCurrentObject(ctl As IRibbonControl)
    DoCmd.Close CurrentObjectType, CurrentObjectName
End Sub

Public Sub ShowDatasheet(ctl As IRibbonControl)
    ''This saves the current unique ID
    CurRec = Forms!Test!TransactionID
    DoCmd.RunCommand acCmdDatasheetView

    ''This finds the saved ID, however, it is not necessary in Access 2010
    ''Because the bookmark is kept from form view
    Forms!Test.Recordset.FindFirst "TransactionID=" & CurRec
End Sub

More information: http://www.accessribbon.de/en/index.php?Downloads
http://blogs.msdn.com/access/archive/2007/09/24/ribbon-customization-closing-the-currently-open-object.aspx

Answer 2

You could probably cobble together something that flips between the two views, but as an general rule, you far better off to build an continues form with a button that the user clicks on to launch a details form.

Trying to control how and what the continues form displays as opposed to a form that allows the user to view/edit/print/verify and simply control the user interface in a reasoned manor suggests that you are FAR BETTER off to launch another form. And, not only does this solve a host of problems, but it only one or 2 lines of code, and you don't loose your spot in that continues form to allow the user to launch + view other forms. Eg:

Docmd.OpenForm "frmDetails",,,"id = " & me!id

Here is screen shot, and what really nice about continues forms in access is buttons and objects repeat for you:

And, here another one:

Again, note how the button just repeats, and the above two forms uses the one line of code I posted above.

Continues forms are a great solution, and I don't think it worth the trouble or efforts to try and flip between the views. And the above ideas work well for runtime also.

Answer 3

Someone please explain to me why this is not the answer:

  DoCmd.RunCommand acCmdDatasheetView