Understanding datasheet view

Posted on 2004-11-22
Medium Priority
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
  • 3
  • 3
  • 3
  • +1

Expert Comment

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

Expert Comment

ID: 12644121
Hello shanepresley,

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

    DoCmd.openform stDocName, acFormDS, , stLinkCriteria


Expert Comment

ID: 12644125
sorry Shane, I think i mis-read you. are you after this view in design?
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 12644175
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.


Expert Comment

ID: 12644208
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

ID: 12644216
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.

Author Comment

ID: 12644462
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

peter57r earned 1000 total points
ID: 12646354

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

shanesuebsahakarn earned 1000 total points
ID: 12646495
This should work:

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

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

Author Comment

ID: 12646643
Both those solutions worked, thanks guys!
LVL 77

Expert Comment

ID: 12646717
Thanks (I know which one I'd use!)


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

839 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