Accmddatasheetview Results

Hi All


I have a main form with several subforms (these are always in datasheet-view). I have provided a Change_Display_Button on the header of my Main form so that the user can switch the Main form to datasheet-view.

When in datasheet-view I expose an "Action" combobox so that the user can take an action against any particular row. In addition, one of the options in the "Action" combo is to switch the Main form back to form-view... it does so by calling the Change_Display_Button's click event.

However, taking this action crashes my main form. The reason appears to be that my form-open event runs some code to set up the subforms... this fails cos' the subforms don't exist... curious cos' the form-open event works perfectly well when first opening it in form-view.

If I use Access' own built-in Change-View button to return to form-view it does so happily... I 'm guessing, therefore, that there is another way to switch between views other than the method I use below??

Private Sub Change_Display_Button_Click()
On Error GoTo Err_Change_Display_Button_Click
Dim View_Type As Integer

View_Type = Me.Form.CurrentView

Select Case View_Type
Case 1
'Currently in form view so go to datasheet
DoCmd.RunCommand acCmdDatasheetView
Me.Action_ComboBox.Visible = True
Me.Action_Label.Visible = True
Case Else
DoCmd.RunCommand acCmdFormView
Me.Action_ComboBox.Visible = False
Me.Action_Label.Visible = False
End Select

exit sub

Any ideas anyone?

I'm trying to avoid using Access toolbars and ribbons cos' I aim to switch them off If I can swing it....


I am using something similar to Allen Browne's "Managing Multiple Instances of a Form" ( which allows me to create multiple instances of a form (a requirement.) The approach involves code like:

	    Dim frm as Access.Form
    set frm = new Form_Payment
    ' ... Add the form to a global collection of forms that I manage

When I create a basic form, it needs to be initially shown in datasheet view so the user can select the rows they want to edit. Then they need to be able to change to "Continuous Forms" view (with multiple lines per row) so that they can see all of the fields without having to scroll right/left to edit the data, as they would in a datasheet view. The users are comfortable using (and want to be able to use) filtering and sorting in the datasheet view to choose the data they want to operate on and they are very happy with this approach. The SQL Server back end provides core business rules integrity which makes me happy.

Such a requirement for multiple instances is necessary because they may be operating on one set of payments and get a call regarding a different set of payments. They do not want to lose their place in the other set of payments while they handle the call.

I cannot find a way to support multiple instances of a form where the form opens in "Datasheet" view with the ability to switch it to "Continuous Forms" view.

I looked for a way to switch the view to a continuous forms view where the syntax should be something like:

	    DoCmd.RunCommand acCmdContinuousFormsView

but I could find no such argument for DoCmd.RunCommand! (Only acCmdDatasheetView and acCmdFormView are available.)

It seems that I can only set a form to "Continuous Forms" in the DefaultView property of a form. There does not seem to be any other way to make a form display as a Continuous Form except by setting the DefaultView to that. Then when the form is displayed in "Form" view, it is shown as a continuous form, rather than a single form.

So now I am making forms like this have a DefaultView setting of "Continuous Forms". Once the form is up and running, I can switch between form view (which is a continuous form) and datasheet view by clicking on the toolbar icons or by using the following two routines (when the form has the focus):

	Public Sub SetView_Datasheet()
    DoCmd.RunCommand acCmdDatasheetView      ' this changes the view to a Datasheet view
   'CommandBars.FindControl(ID:=498).Execute ' (I tried this and it works the same as the above.)
End Sub
Public Sub SetView_FormView()
    DoCmd.RunCommand acCmdFormView           ' when the DefaultView property is "Continuous Forms" this changes the view to a
continuous form.
   'CommandBars.FindControl(ID:=502).Execute ' (I tried this and it works the same as the above.)
End Sub

I see no other way to display a form as a continuous form other than to set the DefaultView property. I also see no other way to support multiple instances of the form other than to create a form from its class as Allen has shown with the "new Form_{FormName}" technique. The syntax of "DoCmd.OpenForm" does not support multiple form instances!

So I accepted the fact that I must have the form have a DefaultView of "Continuous Forms" and then I tried to call SetView_Datasheet() in the Form_Load Event procedure. I get an error on the line "DoCmd.RunCommand acCmdDatasheetView" in the form of an error popup box with: Run-time error '3270':
Property not foundSo I used the debugger to skip over the offending line. Once the Form_{X} event routines have all completed, I can successfully execute the "DoCmd.RunCommand acCmdDatasheetView" line in the Immediate window. (So it seems to be something to do with the fact that the Form_Load event is running when I am attempting to change the view.)

I have tried putting the call in the Form_Current event (the last one in the form event processing sequence) and have tried making sure the form has focus and visibility too. All to no avail.

I have also tried using the DoCmd.OpenForm method of opening the form (rather than instantiating it from a class) and get the same result. If I use the DoCmd.OpenForm command, I can pass an argument that indicates what view I want the form to open in. This works and I can open the form with a DefaultView of "Continuous Forms" in a Datasheet View. However, I cannot open a second form! I tried getting the form object and adding it to my global forms collection, but I can see no way to take the form out of the Forms collection without closing the form! (There is no Forms.Remove() method of any kind.)

I can also see no way to instantiate a form and tell it to open in a different view than the DefaultView! I tried adding procedures such as "Class_Initialize" and even "Form_Initialize" to the form's class, but they were not called.

What would be ideal is for MS Access to support a syntax like the following:

	    set frm = New Form_Payment(acDatasheetView)

Any ideas? I am out of things to try!

Thanks for your time and attention. I really appreciate it.

Not finding an answer? Try a Google search.