Cancel property on form button generates an error, but On_Click does not

[John_G]

This one is really strange.



I have a form (switchboard) on which I have added an additional command button to return to the main menu when it is clicked (saves having to carry it around in all the menus). The code runs fine and does just what it should when the button is clicked from any menu.

To make life easier for users, I also want to let them return to the main menu by pressing ESC, so I set the "Cancel" option on the button to "Yes". This is supposed to run the same code as Clicking the button, and in fact it does, but it generates an error.


The form uses code generated by the switchboard manager, and is easy enough to understand and modify. The On_Click code for the button (called Go_Main) is:

Private Sub Go_Main_Click()
  Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID] = 1"
End Sub

This in turn triggers the On_Current form code:

Private Sub Form_Current()
' Update the caption and fill in the list of options.
    Me.Caption = Nz(Me![ItemText], "")
    FillOptions   
End Sub

FillOptions is generated by switchboard manager and looks like this (I modified it to use DAO instead of ADO):

Private Sub FillOptions()
' Fill in the options for this switchboard page.
    ' The number of buttons on the form.
    Const conNumButtons = 8
    Dim db As Database
    Dim rs As Recordset
    Dim stSql As String
    Dim intOption As Integer
    
    Set db = CurrentDb
    ' 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.
    '
    stSql = "SELECT * FROM [Switchboard Items]"
    stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
    stSql = stSql & " ORDER BY [ItemNumber];"
    Set rs = db.OpenRecordset(stSql)
    
    ' 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"
      rs.Close
      Set rs = Nothing
      Exit Sub
    End If
    
    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  <--  This line generates an error only when ESC is used
    Wend
    ' Close the recordset and the database.
    rs.Close
    Set rs = Nothing
End Sub

As I said, the code runs fine when the button is clicked. But when I press ESC to run it, the indicated line generates Run-time error 3059 - Operation canceled by user.

Interestingly, if I go to debug mode, everything has proper values, and in the immediate pane rs.movelast, rs.movefirst and ?rs.recordcount all work without a problem. rs.recordscount = 6, the correct value.

If I press F5 in the debug, the code resumes and runs to completion properly.

Can enyone explain what is going on? To me, it looks like an MS Access bug.

Thanks.

John

[rpeare]

If you are still working on this have you tried:

1. Doing a record count of rs in that loop causing the problem and only using rs.movenext if the current record is the last record (by recordcount and not relying on .eof)
2. Error trapping for 3059 specifically and doing 'resume next' for 3059 only
3. Using your debug.print statements for your code to find out if there are other unexpected things going on with your code that are causing this final rs.movenext to error out
4. Nesting your loop statement so it's not two different tests like

Set rs = db.OpenRecordset(stSql) 
         ' If there are no options for this Switchboard Page,     
' display a message.  Otherwise, fill the page with the items.     

If rs.recordcount = 0  Then       
    Me![OptionLabel1].Caption = "There are no items for this switchboard page"       
Else
    While rs.EOF <> true
        Me("Option" & rs![ItemNumber]).Visible = True       
        Me("OptionLabel" & rs![ItemNumber]).Visible = True       
        Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]       
        rs.MoveNext  '<--  This line generates an error only when ESC is used     
    Wend     
Endif

' Close the recordset and the database.     
rs.Close      
Set rs = Nothing
set db = nothing '<---- note you should close your database as well as your recordset

I have encountered weird errors like this before and had to code around them

[John_G]

I have it working, I think. I left my code unchanged, except for an On Error Resume Next statement right before the While loop. Not terribly good programming practice I guess, but at least the code runs without error when using Esc to activate it.

Thanks