Open a datasheet from Switchboard

K

kateyliz

Does anyone know if it is possible to open up a datasheet from the
Switchboard? I seem only to be able to create buttons for forms on the
switchboard...

Many thanks

Katie
 
K

kateyliz

Thank you for this information - please can you tell me how I can view the
"commands" for the switchboard? I can't seem to be able to find them
 
J

Jeff Conrad

If you open the Switchboard form in Design View and then go
to the code window you will see these command constants
assigned in the HandleButtonClick function:

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

"kateyliz" wrote in message:
 
K

kateyliz

Thank you for this. I have now managed to find the command. However, there
appears to be an error executing the command.

I have 9 lines already in my code, so I put yours as "10":

Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
Const conCmdOpenFormDatasheet = 10

Also I had some extra code in between at the bottom so I have added mine to
it:

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

' Open a form in Datasheet Mode.
Case conCmdOpenFormDatasheet
DoCmd.OpenForm rst![Argument], acFormDS

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

Are these extra lines of code what are causing my problems?

I also tried entering some code as a seperate function (as per your website)
but that was causing an error too...

Public Function OpenMyForm(Project_List)

DoCmd.OpenForm "frmProject_List", acFormDS

End Function

Sorry to sound so dense... I am not au fait with all this programming, so
can't see where there may be problems.

Your help is very much appreciated!!
 
J

Jeff Conrad

"kateyliz" wrote in message:

Comments inline....
Thank you for this. I have now managed to find the command. However, there
appears to be an error executing the command.
Click to expand...

What exactly is the error message you are receiving?
I have 9 lines already in my code, so I put yours as "10":

Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
Const conCmdOpenFormDatasheet = 10
Click to expand...

That all looks just fine.
Also I had some extra code in between at the bottom so I have added mine to
it:

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

' Open a form in Datasheet Mode.
Case conCmdOpenFormDatasheet
DoCmd.OpenForm rst![Argument], acFormDS

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

Are these extra lines of code what are causing my problems?
Click to expand...

That all looks pretty good except for one thing.

In the Datasheet one you have the recordset as "rst" and in the
other ones listed you use only "rs" as the recordset. Is it defined
as rs or rst in your Declarations?
I also tried entering some code as a seperate function (as per your website)
but that was causing an error too...

Public Function OpenMyForm(Project_List)

DoCmd.OpenForm "frmProject_List", acFormDS

End Function
Click to expand...

What was the error message?

Try changing that to this:

Public Function OpenMyForm()

DoCmd.OpenForm "frmProject_List", acFormDS

End Function

Run the SBM and select RunCode and then enter
OpenMyForm on the Function Name line.
Sorry to sound so dense... I am not au fait with all this programming, so
can't see where there may be problems.
Click to expand...

Don't worry you are not dense.
What version of Access are you using by the way?
Your help is very much appreciated!!
Click to expand...

No problem, we'll figure it out.
 
J

Jeff Conrad

in message:
Error message when using module "OpenMyForm" is The was an error executing
this command". I copied and pasted your new suggestion in but am still
getting the same problem.
Click to expand...

Please post the code you have in the module now as well as *exactly* what
you put in the RunCode option in the Switchboard Manager.
The other method you have suggested - have removed the "!" from the code and
the sheet now opens without errors but still as a form view rather than
datasheet view!
Click to expand...

I did not suggest removing the "!" (exclamation mark) at all!
I noted your recordset was rs in some places and rst (note the letter t
at the end) in another place. Those need to match your declaration.

Please post the modified code you have currently.
I am using Access 2003
Click to expand...

Ok, sounds fine.
Thanks again for your help
Click to expand...

No problem.
 
K

kateyliz

Hi Jeff

Sorry I misunderstood and thought you meant take the ! off.

This is all the code for the switchboard

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True

End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

Me.Caption = Nz(Me![ItemText], "")
FillOptions

End Sub

Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 8

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard
page"
Else
While (Not (rs.EOF))
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend
End If

' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
Const conCmdOpenFormDatasheet = 10

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim con As Object
Dim rs As Object
Dim stSql As String

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM [Switchboard Items] "
stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND
[ItemNumber]=" & intBtn
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If

Select Case rs![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" &
rs![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rs![Argument], acPreview

' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions

' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase

' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rs![Argument]

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

' Open a form in Datasheet Mode.
Case conCmdOpenFormDatasheet
DoCmd.OpenForm rs![Argument], acFormDS

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rs.Close

HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
Exit Function

HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function

Please can you tell me how to find out what I am using for my declaration?

Thanks

Katie
 
J

Jeff Conrad

Sorry for the delay, I was on vacation.
Try using this code for the HandleButtonClick function:
Works just fine for me.
I used 10 in the command field of the table to open a form in datasheet.

' Code Start
Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
Const conCmdOpenFormDatasheet = 10

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim con As Object
Dim rs As Object
Dim stSql As String

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM [Switchboard Items] "
stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & _
" AND [ItemNumber]=" & intBtn
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If

Select Case rs![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rs![Argument], acPreview

' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions

' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase

' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rs![Argument]

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

' Open a form in Datasheet Mode.
Case conCmdOpenFormDatasheet
DoCmd.OpenForm rs![Argument], acFormDS

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rs.Close

HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
Exit Function

HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function
' Code End

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html


in message:
Hi Jeff

Sorry I misunderstood and thought you meant take the ! off.

This is all the code for the switchboard
Click to expand...

<<code snipped>>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Switchboard Problem 1
Switchboard to Datasheet view 0
Why use a switchboard 4
Switchboard Form does not appear in Forms window 1
Switchboard setting up 0
Open datasheet view from switchboard 2
Open a form in filter by form datasheet view from Switchboard 5
Datasheet View 3

Top