Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: i have a problem with my access database

    Hi im having a problem with my database. I added a command button to my switchboard but everytime i try open it the de bugger opens. i have highlighted the first problematic area, please can someone help me out here






    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

    ' 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]

    ' 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

    Private Sub Property_Listings_Click()
    On Error GoTo Err_Property_Listings_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Customer Property Listings"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Property_Listings_Click:
    Exit Sub

    Err_Property_Listings_Click:
    MsgBox Err.Description
    Resume Exit_Property_Listings_Click

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    probably best to ask this in the Access forum
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2006
    Posts
    4

    Thanks

    Thanks very much for that, where is the access forum?

  4. #4
    Join Date
    Dec 2006
    Posts
    4

    problem with Me![Option1].SetFocus (was "Can Someone Help Me")

    Hi there, im having a problem with microsoft office access, i created a database and on the switchboard i added a command button to open forms. When i try open the switchboard form it begins debugging and finds a problem with this line of code: Me![Option1].SetFocus

    i have added all the code here below, please could someone help me sort this problem out, would be very appreciated.


    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

    ' 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]

    ' 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

    Private Sub Property_Listings_Click()
    On Error GoTo Err_Property_Listings_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Customer Property Listings"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_Property_Listings_Click:
    Exit Sub

    Err_Property_Listings_Click:
    MsgBox Err.Description
    Resume Exit_Property_Listings_Click

    End Sub

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Morning, Healdem! Have you had that coffee yet? According to the top of the screeen, this is the Access forum!

    dBforums > PC based Database Applications > Microsoft Access >
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Missinglinq
    Morning, Healdem! Have you had that coffee yet? According to the top of the screeen, this is the Access forum!

    dBforums > PC based Database Applications > Microsoft Access >
    now now.....
    no one loves a smart aleck...
    and, lets face it, I should know that by now...

    BTW it was originally asked in the 'New Members' thread, it was moved from there to here, leaving a pointer in the old thread so that johnnycy0608 could follow where his posting had gone, and with a bit of luck be able to post direct to the Access thread in future.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •