Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Canceling an event (2002)

    I have a form with a command button that executes the code below. I'd like to be able to give the user the ability to cancel the event. Ideally, when the second loop starts I'd like to pop up a message box with a cancel button. If the user clicks on cancel, the loop exits and event terminates. I'm not sure what I'm not understanding, but I can't seem to find any good information on how I can accomplish something like this. Any help would be appreciated.


    Private Sub Command1_Click()
    On Error GoTo Err_Command1_Click

    Dim db As DAO.Database
    Dim rsParcels As DAO.Recordset
    Dim intIim As Long
    Dim blnPhoto As Boolean
    Dim blnPlatMap As Boolean
    Dim blnSketch As Boolean


    Set db = CurrentDb()
    Set rsParcels = db.OpenRecordset("Select * From tblParcels")

    rsParcels.MoveFirst
    rsParcels.MoveLast

    If rsParcels.RecordCount > 0 Then

    rsParcels.MoveFirst

    Do

    'Check to see if the images exist
    With rsParcels
    .Edit
    !PhotoFlg = IIf(Dir("C:Imgs" & rsParcels.Fields("tax_dist") & _
    rsParcels.Fields("parcel") & "Photo.jpg") <> "", True, False)
    !PlatMapFlg = IIf(Dir("C:Imgs" & rsParcels.Fields("tax_dist") & _
    rsParcels.Fields("parcel") & "PlatMap.png") <> "", True, False)
    !SketchFlg = IIf(Dir("C:Imgs" & rsParcels.Fields("tax_dist") & _
    rsParcels.Fields("parcel") & "Sketch.png") <> "", True, False)
    .Update
    End With

    rsParcels.MoveNext

    Loop Until rsParcels.EOF

    End If

    rsParcels.Close

    Set rsParcels = db.OpenRecordset("Select * From tblParcels " & _
    "Where PhotoFlg = False " & _
    "And PlatMapFlg = False " & _
    "And SketchFlg = False ")

    rsParcels.MoveFirst
    rsParcels.MoveLast

    If rsParcels.RecordCount > 0 Then

    rsParcels.MoveFirst

    Do

    'Need to cancel event when user presses cancel button
    'on MsgBox
    intIim = GetAdtrImg(rsParcels!Tax_dist & rsParcels!Parcel, 6)

    Loop Until rsParcels.EOF

    End If



    Exit_Command1_Click:
    Exit Sub

    Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click

    End Sub

  2. #2
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Canceling an event (2002)

    If MsgBox("Do you want to continue?", vbYesNoCancel) <> vbYes Then
    GoTo Exit_Command1_Click
    End If

    Or you might want to use vbYesNo or vbOKCancel as the 2nd argument.

    If you only want the box to pop up once, put it before the loop begins (rather than after the Do line).

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canceling an event (2002)

    I don't want the loop execution to halt to receive the MsgBox selection. Basically, I want to be able to stop the second loop at any time when the user clicks on a cancel button. I'm assuming some how I have to have the loop executing in the background maybe or something. The second loop will run for quite some time, I'd like to be able to stop the loop, perform some clean up and allow the user to exit the app. Any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Canceling an event (2002)

    You can use a module-level variable to signal if the user clicks the cancel button. Put the following line near the top of the form module, before all subs and functions:

    Private blnFlag As Boolean

    Modify the second loop in your code as follows:

    ' Initialize the flag variable
    blnFlag = False
    Do
    ' Perform the loop action
    intIim = GetAdtrImg(rsParcels!Tax_dist & rsParcels!Parcel, 6)
    ' Give other events a chance
    DoEvents
    Loop Until rsParcels.EOF Or blnFlag = True

    Put a command button cmdCancel on the form, with caption Cancel, and the following On Click code:

    Private Sub cmdCancel_Click()
    blnFlag = True
    End Sub

    The DoEvents in the loop allows the processor to handle other events than the current one. If the user clicks on cmdCancel, the flag variable is set to True, and this causes the loop to end the next time Loop Until is executed.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Canceling an event (2002)

    Hans,

    That's exactly what I needed! <img src=/S/cool.gif border=0 alt=cool width=15 height=15> I didn't think it should be that difficult, but I couldn't come up with it on my own.

    Thanks for the help...

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New problem

    Hans,

    I'm getting an error that says "File Sharing lock count exceeded. Increase MaxLocksPerFile registry entry." I get the error when I loop through the rsParcels recordset in the code below. The recordset has 336,170 records in it. Any help would be appreciated.


    > ---
    Large code fragment (over 4,000 characters) moved to attachment by HansV.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New problem

    I don't know why this error would occur, but then I don't know what the GetAdtrImg function does. The Knowledge Base mentions that this error could occur with synchronization of replicated databases - see You receive an "File sharing lock count exceeded" error message when you synchronize two replicas in Access 2000; this article explains how to increase the MaxLocksPerFile registry entry.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New problem

    Hans,

    I changed the registry entry and it worked. Looks like the SetOption command would be an option as well. Is there something I'm not understanding about that code? I looked into batch updating, but it says I have to an ODBCDirect workspace, which I know nothing about. I'm just curious if I should be doing anything differently with updates via a recordset for an optimization standpoint? I'm going to be working with some large tables, I want to make sure I do things correctly from the start.

    Thanks...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New problem

    As I wrote, I don't know why your code would cause the error. Perhaps others will have suggestions.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: New problem

    I'm sorry, but I don't understand what SetOption has to do with your problem, since if it was in your code I missed it. You can roll back changes if you nest them inside a transaction, but batch updates are only supported in ODBCDirect workspaces or through use of ADO. I don't quite understand when you want to allow the user to cancel. By the "second loop", do you mean the second record in the first Do Loop or are you talking about completing the first set of Do Loops and then allowing a break? Is there a reason you're doing this with a recordset rather than through a query, other than allowing the user to cancel the update?
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New problem

    Charlotte,

    The SetOpton command isn't in my code, my post wasn't very clear. I just meant that based on what I read, I could have used SetOption in my code rather than changing the registry entry.

    Both Do loops could run for quite some time, my goal was to allow the user to cancel either loop if they desire. I used a recordset because I thought that was the best way to accomplish what I needed, I'm not sure how I could accomplish the update with a query? I'm learning Access and VBA, if you have any suggestions you'd like to offer as to how I could improve my code, I'd love to hear them. I attached the latest version of my event, I've made some additional changes.

    I really appreciate the response.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New problem

    The SetOption Jim refers to is not the Application.SetOption method that sets options for Access (equivalent to Tools | Options...), but the DAO method:

    DAO.DBEngine.SetOption dbMaxLocksPerFile, 15000

    This sets the MaxLocksPerFile value temporarily. Next time you start Access, it will use the value set in the registry again.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New problem

    The first loop could be replaced by an update query. Its SQL would look like this:

    UPDATE tblParcels SET PhotoFlg = (Dir("C:Imgs" & [tax_dist] & [parcel] & "Photo.jpg") <> ""), FlatMapFlag = (Dir("C:Imgs" & [tax_dist] & [parcel] & "PlatMap.png") <> ""), SketchFlg = (Dir("C:Imgs" & [tax_dist] & [parcel] & "Sketch.png") <> "")

    I can't give any advice about the second loop, since I don't know what the function in that loop does.

  14. #14
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New problem

    Would the query be a better choice for performance and/or from a best practices standpoint? I never thought of going about it that way, but I like the straightforward approach.

    The second loop calls a VBA function (attached) that I wrote. The function then runs third party macro software through the macro's scripting interface. Any advice on how to improve any of the code is appreciated.


    Thanks.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: New problem

    In most cases, a query is more efficient than a code loop. With the number of records you have, you can easily measure the execution time of the loop and of the query.

    Since the second loop calls external software, you cannot replace it by a query, I think. You might make the code somewhat more efficient by making the variables db, rsMacroLog and iim1 into module-level or public variables (depending on your setup), setting them before entering the second loop and releasing them after exiting the loop. That way, they don't have to be set/created anew for each pass through the loop.

Page 1 of 2 12 LastLast

Posting Permissions

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