Understanding datasheet view


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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

set the Default View to datasheet. This will bring up the "excel" view by default.
Hello shanepresley,

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

    DoCmd.openform stDocName, acFormDS, , stLinkCriteria

sorry Shane, I think i mis-read you. are you after this view in design?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

shanepresleyAuthor Commented:
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.

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.

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.
shanepresleyAuthor Commented:
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

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.

This should work:

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shanepresleyAuthor Commented:
Both those solutions worked, thanks guys!
Thanks (I know which one I'd use!)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.