By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,820 Members | 957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,820 IT Pros & Developers. It's quick & easy.

Change form view using VB after the form is opened?

jamjar
P: 50
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
Jun 1 '07 #1
Share this Question
Share on Google+
10 Replies

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 1 '07 #2

jamjar
P: 50
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 6 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 6 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 6 '07 #5

jamjar
P: 50
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.   Dim str As String
  3.     If Me.CurrentView <> 2 Then
  4.         'if not opened as a datasheet, close and re-open in datasheet view
  5.         str = Me.Name
  6.         DoCmd.Close acForm, str
  7.         DoCmd.OpenForm str, acFormDS
  8.         Exit Sub
  9.     End If
  10. End Sub
This seems rather brutal but it works!

James
Jun 6 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim str As String
  3.     If Me.CurrentView <> 2 Then
  4.         'if not opened as a datasheet, close and re-open in datasheet view
  5.         str = Me.Name
  6.         DoCmd.Close acForm, str
  7.         DoCmd.OpenForm str, acFormDS
  8.         Exit Sub
  9.     End If
  10. 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
Jun 6 '07 #7

jamjar
P: 50
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
Jun 7 '07 #8

FishVal
Expert 2.5K+
P: 2,653
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
Jun 7 '07 #9

jamjar
P: 50
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).
Jun 8 '07 #10

P: n/a
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!
Oct 1 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.