Switching between form views using acCmdSubformDatasheet

Hi,

Form:                  surveyGroup
Subform             survey
sub-subform       boatTransects

I have a button on the surveyGroup form that switches the view of 'survey' from data sheet to subform view.

The following code works, but when I click the mouse 'flashing cursor'  into a textbox on 'boatTransects' and the button is then clicked, the 'boatTransects' form toggles between subform view and datasheet view NOT the 'survey' form.

What I need to do is get the reference right so that the focus is always on the 'survey' form and therefore gets its view changed regardless of where the cursor is clicked on the other sub-subforms.

Thanks


Private Sub btnView_Click()
If Forms!surveyGroup.survey.Form.surveyTypeID.Value = 2 Then
  'MsgBox "is Land"
  Me.survey.Form.subform_boatTransects.Visible = False
  Me.survey.Form.subform_LandSightings.Visible = True
 
'Swaps the view 
   Me.survey.SetFocus
    DoCmd.RunCommand acCmdSubformDatasheet
    Me.surveyDate.SetFocus
Else
  'MsgBox "is Boat"
  Me.survey.Form.subform_boatTransects.Visible = True
  Me.survey.Form.subform_LandSightings.Visible = False
 
  'Swaps the view 
  Me.survey.SetFocus
  DoCmd.RunCommand acCmdSubformDatasheet
 Me.surveyDate.SetFocus
End If
End Sub

Open in new window

LVL 1
davecocksAsked:
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.

thenelsonCommented:
try this:
  Me.surveyGroup.SetFocus
  Me.survey.SetFocus
  DoCmd.RunCommand acCmdSubformDatasheet
0
thenelsonCommented:
If that doesn't do it try this:
  Me.surveyGroup.SetFocus
  Me.survey.SetFocus
  Me.survey.Form.SetFocus
  DoCmd.RunCommand acCmdSubformDatasheet
0
davecocksAuthor Commented:
Hey,
Thanks for the reply.     I tried both but no joy.

access didin't like       Me.surveyGroup.Setfocus         (method or group not found)
I tried                          Forms!surveyGroup.Setfocus      (worked)

                                  Me.survey.Form.SetFocus      (run time error 2449 invalid method in expression)
I tried     Forms!surveyGroup.survey.Form.SetFocus   (run time error 2449 invalid method in expression)

That left        Forms!surveyGroup.SetFocus
                      Me.survey.SetFocus

Which worked but didn't solve the problem of of the other forms changing.  

Any other ideas?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

thenelsonCommented:
Me.surveyGroup.Setfocus   Oops, Sorry.   Me.Bad

1. Try setting the focus to a control in survey:
  Me.survey.SetFocus
  Me.survey!MyControl.SetFocus
  DoCmd.RunCommand acCmdSubformDatasheet

2. Is survey the name of the subform container or the name of the form in the container? If they have the same name, try changing the container to ctnSurvey and use
Me.ctnSurvey.SetFocus
DoCmd.RunCommand acCmdSubformDatasheet
0

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
thenelsonCommented:
I got it.  Place:
DoCmd.RunCommand acCmdSubformDatasheet
in the on enter event of the container for survey SubForm.  

So it only fires when You want it to create a public variable in the main form surveyGroup:

In surveyGroup:
Public CreateDataSheet As Boolean

In survey:
Private Sub ctnsurvey_Enter()
If CreateDataSheet Then DoCmd.RunCommand acCmdSubformDatasheet
CreateDataSheet = False
End Sub

In your Code:
CreateDataSheet = True
Me.survey.SetFocus



0
davecocksAuthor Commented:
You.Great!!!   1) worked like a charm!!!!  That's brilliant thanks mate!
0
davecocksAuthor Commented:
Didn't refresh my web page fast enough!  Thanks for posting another solution as well !!
0
thenelsonCommented:
I ran into a problem with 1 so that's why I came up with another.

Also figured out what was going on. When you enter a subform, the control in it that last had focus still has the focus. When you clicked on the boatTransects subform, it's container got the focus in the subform. Clicking on the button changed the focus to surveyGroup main form but when you changed the forces to survey, the boatTransects subform container still had the focus within survey.

You're welcome.  Glad to help and thank you very much for the points with "A" grade!  It was a fun one!

Happy Computing!

Nelson
0
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.