Toggle Between Form & Datasheet View

[Matthew Pfluger]

I would like make a toggle button on my form that changes the current view of
the embedded subform between Form and Datasheet view. What is the syntax for
that? Do I run a specific DoCmd.RunCommand?

Thanks,
Matthew Pfluger

 

[Clifford Bass]

Hi Matthew,

That may not be possible because the view change applies to the top
level form. However here are a couple of untested ideas: 1) Make two copies
of the subform, one with a default view of Form and the other of Datasheet.
Place each into your form, one on top of the other. Make one visible and the
other not. Toggle the visibilities of both when the button is clicked. 2)
Or maybe place just one subform on the form and switch the Source Object
property between each of the subform's names.

Hope this helps,

Clifford Bass

 

[Danny Lesandrini]

Matthew:

I'm currently using this code to do what you describe. It could be improved,
but it's a good starting place.

Put a button on the parent form, like mine, named cmdOfficesSubformView.
In the On_Click event, I call a proc named ToggleSiteOfficeView() and pass
the view state... datasheet or form.
I'm also reading/writing to the registry so the system remembers how the
user last viewed it and I call the ToggleSiteOfficeView at form open.

Hope this gets you started.


Private Sub cmdOfficesSubformView_Click()
On Error GoTo Err_Handler

With cmdOfficesSubformView
If .Caption = "View As Datasheet" Then
ToggleSiteOfficeView (acCmdSubformDatasheetView)
Else
ToggleSiteOfficeView (acCmdSubformFormView)
End If
End With

Exit_Here:
Exit Sub
Err_Handler:
msgbox Err.Description
Resume Next
End Sub


Private Sub ToggleSiteOfficeView(ByVal lngView As Long)
On Error GoTo Err_Handler

If lngView = acCmdSubformFormView Then
Me!SiteOfficesSubform.SetFocus
DoCmd.RunCommand lngView
SaveSetting "PTS", "User Prefs", "SiteOfficeView", acCmdSubformFormView
cmdOfficesSubformView.Caption = "View As Datasheet"

ElseIf lngView = acCmdSubformDatasheetView Then
Me!SiteOfficesSubform.SetFocus
DoCmd.RunCommand lngView
SaveSetting "PTS", "User Prefs", "SiteOfficeView", acCmdSubformDatasheetView
cmdOfficesSubformView.Caption = "View As Form"

Else
' do nothing
End If

Exit_Here:
Exit Sub
Err_Handler:
Select Case Err.Number
Case 2046
' The command or action 'SubformDatasheetView' isn't available now.
Case 3021
' No current record.
Case Else
msgbox Err.Description
End Select
Resume Next
End Sub

 

[Clifford Bass]

Hi Danny,

Ah... a separate command for changing the subform--did not know that.
Thanks!

Clifford Bass

 

[Matthew Pfluger]

Yes, thank you! That's exactly what I wanted! Have a great day.

Matthew Pfluger

 

[Danny J. Lesandrini]

Clifford, it's not all a bed of roses. My code works, it's true, but I get a lot of
little nit-picky errors I can't track down, thus the complicated ON ERROR
routine. Despite the errors, it seems to do what it claims.