Understanding datasheet view

Posted on 2004-11-22
Last Modified: 2008-03-03

I'm a little confused by the Datasheet view for forms.

When I enter the design view of a datasheet form, the layout looks like a regular single record columnar design.  But when I open the form (by double clicking on it), I get more what I expected....a Microsoft Excel like view of the data.  All the records at once, row by row.

However, when I add the form to the Switchboard and say Open in Edit Mode, the form opens more like the columnar view, similar to what I saw in design view.

How can I get a form that's more similar to Excel.  I like tubular, but I don't need the formatting/column headings, etc.  I'd prefer a simple clean line by line, like Excel, or like I saw in the preview of the datasheet view.

Question by:shanepresley
    LVL 4

    Expert Comment

    set the Default View to datasheet. This will bring up the "excel" view by default.
    LVL 77

    Expert Comment

    Hello shanepresley,

    Go to the line of code that opens your form and add   acFormDS  as follows:

        DoCmd.openform stDocName, acFormDS, , stLinkCriteria

    LVL 4

    Expert Comment

    sorry Shane, I think i mis-read you. are you after this view in design?
    LVL 1

    Author Comment

    No, I'm looking for this "Excel" view in add/edit mode.  Not design.

    The Default View is set to datasheet.  When I open the form by double clicking on it in the forms list, or by clicking Open, it looks fine (datasheet view).

    But when the form is opened by the Switchboard, it comes up in that single record view, not datasheet.

    LVL 4

    Expert Comment

    I guess the switch board was set up using a wizard. If you look in the event procedure for that button the code has probably set the form to open in the form view.

    You will need to find this and change the code to something along the lines of what peter57 has given. The acFormDS is the key bit here.

    LVL 41

    Expert Comment

    Basically, a form can have its default view overridden when opened - that is, you can force it to appear in any mode you like, and the switchboard does this. Design the switchboard form, view its code and search for the word acNormal. Simply delete that, and you should get the behaviour you want.
    LVL 1

    Author Comment

    Well I'm getting closer.  The SwitchBoard used this code "DoCmd.OpenForm rst![Argument]".  If I change that to "DoCmd.OpenForm rst![Argument], acFormDS" it works for my one form that I have in datasheet view.

    But the problem is that code is generic, so now it opens all my forms in datasheet view.  Is there a way to tell the Switchboard to open the form in the default view?  For most of my forms that is a single record view.  For this one form, it's datasheet view.

    (acNormal wasn't found anywhere)


    Private Function HandleButtonClick(intBtn As Integer)
    ' This function is called when a button is clicked.
    ' intBtn indicates which button was clicked.

        ' Constants for the commands that can be executed.
        Const conCmdGotoSwitchboard = 1
        Const conCmdOpenFormAdd = 2
        Const conCmdOpenFormBrowse = 3
        Const conCmdOpenReport = 4
        Const conCmdCustomizeSwitchboard = 5
        Const conCmdExitApplication = 6
        Const conCmdRunMacro = 7
        Const conCmdRunCode = 8

        ' An error that is special cased.
        Const conErrDoCmdCancelled = 2501
        Dim dbs As Database
        Dim rst As Recordset

    On Error GoTo HandleButtonClick_Err

        ' Find the item in the Switchboard Items table
        ' that corresponds to the button that was clicked.
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
        rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
        ' If no item matches, report the error and exit the function.
        If (rst.NoMatch) Then
            MsgBox "There was an error reading the Switchboard Items table."
            Exit Function
        End If
        Select Case rst![Command]
            ' Go to another switchboard.
            Case conCmdGotoSwitchboard
                Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]
            ' Open a form in Add mode.
            Case conCmdOpenFormAdd
                DoCmd.OpenForm rst![Argument], , , , acAdd

            ' Open a form.
            Case conCmdOpenFormBrowse
                DoCmd.OpenForm rst![Argument], acFormDS

            ' Open a report.
            Case conCmdOpenReport
                DoCmd.OpenReport rst![Argument], acPreview

            ' Customize the Switchboard.
            Case conCmdCustomizeSwitchboard
                ' Handle the case where the Switchboard Manager
                ' is not installed (e.g. Minimal Install).
                On Error Resume Next
                Application.Run "ACWZMAIN.sbm_Entry"
                If (Err <> 0) Then MsgBox "Command not available."
                On Error GoTo 0
                ' Update the form.
                Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
                Me.Caption = Nz(Me![ItemText], "")

            ' Exit the application.
            Case conCmdExitApplication

            ' Run a macro.
            Case conCmdRunMacro
                DoCmd.RunMacro rst![Argument]

            ' Run code.
            Case conCmdRunCode
                Application.Run rst![Argument]

            ' Any other command is unrecognized.
            Case Else
                MsgBox "Unknown option."
        End Select

        ' Close the recordset and the database.
        Exit Function

        ' If the action was cancelled by the user for
        ' some reason, don't display an error message.
        ' Instead, resume on the next line.
        If (Err = conErrDoCmdCancelled) Then
            Resume Next
            MsgBox "There was an error executing the command.", vbCritical
            Resume HandleButtonClick_Exit
        End If
    End Function
    LVL 77

    Assisted Solution


    Well this is a bit more interesting than I thought it would be.
    When you set the default view in the form design you are actually applying the following values:

    Single form = 0
    Continuous Form = 1
    Datasheet = 2
    PivotTable = 3
    PivotChart = 4

    I assumed (wrongly ) that you should be able to look at the defaultview property for the form and then apply that value in the Openform command.

    However, the values that apply in the openform statement are:

    acNormal = 0 (SingleForm view or continuous form view depending on DefaultView setting)
    acDesign = 1
    acPreview = 2
    acFormDS = 3
    acPivotTable= 4
    acPivotChart = 5

    There is no value which opens the form in its DefaultView.
    So to open a form in its defaultview I opened it hidden, retrieved the DefaultView value, closed the hidden form and then used the retrieved the defaultview value to determine the correct parameter value for the openform statement.

    Something like this:

    Private Sub Command33_Click()
    On Error GoTo Err_Command33_Click

        Dim stDocName As String
        Dim stLinkCriteria As String
        Dim pView, oView

        stDocName = "Orders2"
        DoCmd.openform stDocName, acNormal, , stLinkCriteria, , acHidden
        pView = Forms(stDocName).DefaultView
        DoCmd.Close acForm, stDocName

        If pView<2 Then
        oView = 0     ' 0 works for single form and continuous form; drag bottom border of form to see continuous form
        ElseIf pView >= 2 Then
        oView = pView + 1
        MsgBox "View error"
        End If
        DoCmd.openform stDocName, oView, , stLinkCriteria

        Exit Sub

        MsgBox Err.Description
        Resume Exit_Command33_Click
    End Sub

    Not a brilliant bit of MS design, In my view.

    LVL 41

    Accepted Solution

    This should work:

    DoCmd.OpenForm "MyForm",acDesign
    DoCmd.OpenForm "MyForm"

    This will "open" the form in its default view :)
    LVL 1

    Author Comment

    Both those solutions worked, thanks guys!
    LVL 77

    Expert Comment

    Thanks (I know which one I'd use!)


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now