Unable to recognise a 'Cancel' request

[Kevin Robson]

I am new to Access and I am creating a small programme that puts an incremental bar on the screen showing the progress of a database import.

The progress bar works OK when tested with a simple loop to simulate the data import, but I seem unable to get the program to respond to clicking on a 'Cancel' button to abort the import.

The button will not respond until the loop has terminated naturally. i.e. it accepts the mouse click and only acts upon it at the end of the loop.

Any ideas on how I can get it to respond immediately, so stopping the import part way through?

Any hep would be most appreciated.

 

[spacepro]

Don't know if this will help, but I could only find this out:


Suspending code execution without Debug.Assert (Access 97/2000/2002)
(posted 07/23/02)

We've recently discussed using Debug.Assert as a way of halting VBA code execution. Unfortunately, the Assert method was introduced in Access 2000, so Access 97 users can't take advantage of it. However, Mark Siffler, of Saint Louis, Mo., reminded us of another technique that works in Access 97 and above--the Stop statement. For example, if you want to suspend execution when a variable exceeds a certain value, you could use code such as:

    If X > Y then
        Stop
    End If

Maybe you can adapt the stop command.

The Stop statement acts like a breakpoint you might manually set in the VBE, providing you with a chance to debug and step through the code.

Just out of curiosity why would you want to stop code half way through the process, as you will have incomplete import data.



Andy

 

[ghudson]

I just tried using the Stop statement during an import process and you can not click
another command button while Access is busy importing the data since Access is already
busy processing another function. I did however trap for the user pressing the
"Ctrl + Break" key combination for that will generate the runtime error 2501. Then I display
a custom message alerting the user of the aborted import function. You do need to
account for the fact that the import was aborted but the code was running so a partial
amount of the data was imported. I played with the Stop statement with the error
trap but that brought the code window into focus [a big no no] so the Stop statement
should not be used in a live environment with Access 97. Below is the error routine...

Private Sub bImportData_Click()
On Error GoTo Err_bImportData_Click
    
    CurrentDb().Execute "DELETE * FROM MyTable"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "MyTable", "X:\Data\Transfer\Test.xls", True
    
    MsgBox "End of data importing."
    
Exit_bImportData_Click:
    Exit Sub
        
Err_bImportData_Click:
    If Err.Number = 2501 Then
        'Stop 'do not use the Stop statement for it brings the code window into focus
        MsgBox "You have aborted the import function.", vbInformation, "Abort"
        CurrentDb().Execute "DELETE * FROM MyTable" 'delete the aborted partial import
        Exit Sub
    Else
        MsgBox "Run-time error # " & Err.Number & "." & vbCrLf & vbLf & Err.Description
        Resume Exit_bImportData_Click
    End If
    
End Sub

HTH

 

[spacepro]

ghudson,

I wasn't sure what the stop function was and the wasn't sure about what I found but now you have explained this. It's much clearly.


It seems this scenario has not been touched on very much at all.


Andy