Change form view using VB after the form is opened?

[jamjar]

How do I change the form view using VB after the form is opened?

I am using the Switchboard pretty much as the wizard loads it. I've tweaked the appearance and a couple of lines of code but I don't want to modify it too much.

When I try to load a form with the properties set to load as Datasheet view only, the switchboard's VB code overrides these settings and loads it in Form view.

Can I use VB in the Form_Open or some other event to change the view back to datasheet?

I'm using Access 2002 ....

James

[MMcCarthy]

How do I change the form view using VB after the form is opened?

I am using the Switchboard pretty much as the wizard loads it. I've tweaked the appearance and a couple of lines of code but I don't want to modify it too much.

When I try to load a form with the properties set to load as Datasheet view only, the switchboard's VB code overrides these settings and loads it in Form view.

Can I use VB in the Form_Open or some other event to change the view back to datasheet?

I'm using Access 2002 ....

James

For the form being opened change the properties under the format tab of Default View and Views Allowed so both are set to Datasheet.

[jamjar]

For the form being opened change the properties under the format tab of Default View and Views Allowed so both are set to Datasheet.

I tried this and it still opens in Form view instead of Datasheet view. I think this is because the VB code created by the Switchboard wizard:

DoCmd.OpenForm rs![Argument], , , , acAdd

causes it to open in Form view even if the defaults and allowable views are set otherwise, and even thought the 2nd argument does not specify a view.
When I set the second argument to 'acFormDS' the sheet will open in Datasheet mode, but I don't want this for most forms.
I don't know why the VB code would cause the form to open ignoring the set properties even when a view is not specified, but it does. So I'm left wondering if I can change the view in the form's OnOpen event?

James

[MMcCarthy]

I tried this and it still opens in Form view instead of Datasheet view. I think this is because the VB code created by the Switchboard wizard:

DoCmd.OpenForm rs![Argument], , , , acAdd

causes it to open in Form view even if the defaults and allowable views are set otherwise, and even thought the 2nd argument does not specify a view.
When I set the second argument to 'acFormDS' the sheet will open in Datasheet mode, but I don't want this for most forms.
I don't know why the VB code would cause the form to open ignoring the set properties even when a view is not specified, but it does. So I'm left wondering if I can change the view in the form's OnOpen event?

James

Not in the open event but maybe in the Load event. Let me check.

[MMcCarthy]

Not in the open event but maybe in the Load event. Let me check.

Sorry I'm afraid none of the events that trigger when you open the form will allow the view to be changed.

Maybe someone else will have some ideas.

[jamjar]

Sorry I'm afraid none of the events that trigger when you open the form will allow the view to be changed.

Maybe someone else will have some ideas.

Thanks Mary. Since you usually know the answers if they exist ;-) I decided to proceed as if it doesn't .... I had the idea that if is not opened as a datasheet, I force the form to close and re-open properly:

Private Sub Form_Open(Cancel As Integer)
  Dim str As String
    If Me.CurrentView <> 2 Then
        'if not opened as a datasheet, close and re-open in datasheet view
        str = Me.Name
        DoCmd.Close acForm, str
        DoCmd.OpenForm str, acFormDS
        Exit Sub
    End If
End Sub

This seems rather brutal but it works!

James

[MMcCarthy]

Thanks Mary. Since you usually know the answers if they exist ;-) I decided to proceed as if it doesn't .... I had the idea that if is not opened as a datasheet, I force the form to close and re-open properly:

Private Sub Form_Open(Cancel As Integer)
Dim str As String
    If Me.CurrentView <> 2 Then
        'if not opened as a datasheet, close and re-open in datasheet view
        str = Me.Name
        DoCmd.Close acForm, str
        DoCmd.OpenForm str, acFormDS
        Exit Sub
    End If
End Sub

This seems rather brutal but it works!

James

I'm surprised it works as once the form closes the code should lose focus. However, if it works go with it.

Mary

[jamjar]

I'm surprised it works as once the form closes the code should lose focus. However, if it works go with it.

Mary

It is ugly. I was afraid the code would cease when the form was closed but apparently not.

James

[FishVal]

It is ugly. I was afraid the code would cease when the form was closed but apparently not.

James

Think different.

1) Add field [FormView] to the [Switchboard] table and store there number equal to VBA constant (acNormal=0, acFormDS=3)
2) In Switchboard form module replace
DoCmd.OpenReport rs![Argument]
to
DoCmd.OpenReport rs![Argument], rs![FormView]

To my mind this is much easier than to add OnOpen event to each form.

By the way you can switch form view programmatically using
DoCmd.RunCommand acCmdDatasheetView
But this fails in OnOpen, OnLoad, OnActivate, OnCurrent events handlers when form is being opened.

Good Luck

[jamjar]

Think different.

1) Add field [FormView] to the [Switchboard] table and store there number equal to VBA constant (acNormal=0, acFormDS=3)
2) In Switchboard form module replace
DoCmd.OpenReport rs![Argument]
to
DoCmd.OpenReport rs![Argument], rs![FormView]

To my mind this is much easier than to add OnOpen event to each form.

By the way you can switch form view programmatically using
DoCmd.RunCommand acCmdDatasheetView
But this fails in OnOpen, OnLoad, OnActivate, OnCurrent events handlers when form is being opened.

Good Luck

Thanks.
I would modify the Switchboard but I just went through a conversion from 97 to 2002 and had problems with migrating the switchboard over, and I had to start over using the 2002 Switchboard wizard and re-doing the form style, controls and the couple of code changes I had made. So I'd rather not tweak it more than I already have in case the same thing happens when the next conversion comes along (which in my case could just as likely be back to 97, but that's a long story).

[Solange Waithe]

I'm no programmer, but I used google to find an answer to this same problem and found this page - which didn't help directly, except to inspire me to look at my main menu form itself, as I have a button there from which we open the form we need to use in datasheet view.

Here's what I did:
(My apologies if you the answer is already in techspeak above.)

1. Opened the menu form in design view
2. Opened Property Sheet for the Command Button
3. Under Event tab, clicked "..." by "On Click [Embedded Macro]"
4. Under Action Arguments, View, selected Datasheet

Voila!