Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    18

    Unanswered: Access Window Won't Restore After Minimization

    I have an Access 2007 ACCDB that runs a series of action queries on the form timer for the default form. When the window is on screen everything runs fine. If I minimize Access and wait until the timer has run once or twice, I am not able to restore the window. When I click on the Access icon in the task bar I get a flicker of a really small window. Sometimes I am able to use alt + space to get to the window control menu, and I can select maximize to get the window back. Any thoughts on this annoying behavior?

    Thanks,
    Daniel

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the VBA code running in what you call "the default form" ?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I wonder if the coded needs to use a do events to allow the Access UI manager to handle other processing
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2011
    Posts
    18
    The timer calls three subs under the sub UpdateData: MOMRequestUpdate, StopJobUpdate, and AllJobUpdate. StopJobUpdate and AllJobUpdate have not been changed in a while, so I don't think the issue is there. It seems that the window will not restore correctly if I try to restore while it is processing UpdateData. It takes about 90 secs.

    Thanks for the responses so far.

    Code:
    Private Sub Form_Timer()
    
       Call UpdateData
    
    End Sub
    Code:
    Private Sub UpdateData()
     
       Call MOMRequestUpdate(True)
       Call StopJobUpdate
       Call AllJobUpdate
       
       Me("lblMsg").Caption = "last update: " & Now
       Me.Repaint
       Me.TimerInterval = 300000
       Me.Refresh 
    End Sub
    Code:
     
    Public Sub MOMRequestUpdate(Optional boolFullReset As Boolean)
    
       Dim qdf As QueryDef
       Dim i As Long
       Dim lngMonths As Long  
       Dim strQueries() As Variant
       lngMonths = -6
       Set rst = New ADODB.Recordset
       Set qdf = New QueryDef
         
       Select Case boolFullReset
          Case True
       
             strQueries = Array("qryDeleteBomRequestALL", "qryInsertBomRequestALL", "qryInsertMain", _
                            "qryInsertLine1", "qryInsertLine2", "qryInsertLine3", "qryInsertLine4", _
                            "qryInsertLine4", "qryInsertLine5", "qryUpdateMain", "qryUpdateLine1", _
                            "qryUpdateLine2", "qryUpdateLine3", "qryUpdateLine4", "qryUpdateLine5", _
                            "qryUpdateDays", "qryUpdateProdCodeRequest")
                            
          Case Else
             
             strQueries = Array("qryDeleteBomRequest", "qryInsertBomRequest", "qryInsertMain", _
                            "qryInsertLine1", "qryInsertLine2", "qryInsertLine3", "qryInsertLine4", _
                            "qryInsertLine4", "qryInsertLine5", "qryUpdateMain", "qryUpdateLine1", _
                            "qryUpdateLine2", "qryUpdateLine3", "qryUpdateLine4", "qryUpdateLine5", _
                            "qryUpdateDays", "qryUpdateProdCodeRequest")
            
       End Select
    
       Forms("frmCard")!lblMsg.Caption = "Updating MOM Requests"
       Forms("frmCard").Repaint
       Call RefreshSharePointData
             
       With Forms("frmCard")
          For i = LBound(strQueries) To UBound(strQueries)
             Select Case strQueries(i)
                Case "qryDeleteBomRequest", "qryInsertBomRequest"
                   .lblMsg.Caption = .lblMsg.Caption & "."
                   .Repaint
                   Set qdf = CurrentDb.QueryDefs(strQueries(i))
                   qdf.Parameters("[Constraint]") = DateAdd("m", lngMonths, Date)
                   qdf.Execute dbFailOnError
                Case Else
                   .lblMsg.Caption = .lblMsg.Caption & "."
                   .Repaint
                   CurrentDb.Execute strQueries(i), dbFailOnError
             End Select
          Next
          
       End With
    
       CurrentDb.Execute "Insert into tblLog (EntryDate,Action) Values(" & FixUp(Now()) & _
                            " ," & FixUp("MOMRequestUpdate") & ")"
                           
       Forms("frmCard")!lblMsg.Caption = vbNullString
    
       
    End Sub
    Code:
      
    Public Sub RefreshSharePointData()
        Dim dbs As Database
        Dim tbl As TableDef
        Set tbl = New TableDef
        Set dbs = CurrentDb()
        
        Application.Echo False ' I added
        For Each tbl In dbs.TableDefs
            If (Mid(tbl.Name, 1, 1) <> "~") And ((tbl.Attributes And dbAttachedTable) = dbAttachedTable) Then
                If Left$(tbl.Name, 21) <> "User Information List" Then
                    If Left$(tbl.Connect, 3) = "WSS" Then
                        DoCmd.SelectObject acTable, tbl.Name, True
                        DoCmd.OpenTable tbl.Name
                        DoCmd.Close acTable, tbl.Name
                    End If
                End If
            End If
        Next
        Application.Echo True
        
    End Sub
    Code:
      
    Public Sub StopJobUpdate()
    
       
       With Forms("frmCard")
             .lblMsg.Caption = "Updating Stop Job Data"
             .Repaint
          CurrentDb.Execute "qryImportEpicorStop", dbFailOnError
             .lblMsg.Caption = .lblMsg.Caption & "."
             .Repaint
          CurrentDb.Execute "qryImportEngStop", dbFailOnError
             .lblMsg.Caption = .lblMsg.Caption & "."
             .Repaint
          CurrentDb.Execute "qryUpdateProdCodeEngStop", dbFailOnError
             .lblMsg.Caption = .lblMsg.Caption & "."
             .Repaint
          CurrentDb.Execute "Insert into tblLog (EntryDate,Action) Values(" & FixUp(Now()) & _
                            " ," & FixUp("StopJobEngUpdate") & ")"
                            
          CurrentDb.Execute "Insert into tblLog (EntryDate,Action) Values(" & FixUp(Now()) & _
                            " ," & FixUp("StopJobEpicorUpdate") & ")"
    
       End With
    
    
    End Sub
    Code:
     
    Public Sub AllJobUpdate()
    
       
       With Forms("frmCard")
             .lblMsg.Caption = "Updating Epicor Job Data"
             .Repaint
          CurrentDb.Execute "qryImportAllJobs", dbFailOnError
             .lblMsg.Caption = .lblMsg.Caption & "."
             .Repaint
          CurrentDb.Execute "qryUpdateProdCodeAllJob", dbFailOnError
             .lblMsg.Caption = .lblMsg.Caption & "."
             .Repaint
          CurrentDb.Execute "Insert into tblLog (EntryDate,Action) Values(" & FixUp(Now()) & _
                              " ," & FixUp("AllJobUpdate") & ")"
    
       End With
       
    
    End Sub

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I tend to agree with healdem: some DoEvents would probably help. But I also noticed that there does not seem to be any instruction resetting the TimerInterval property to zero (maybe I missed it). If the processes initiated by the timer take a long time to execute (longer than the timer duration) the form will enter an infinite loop and you'll end up with a Stack Overflow error. If it's not the case, the UpdateData procedure will be called over and over every 30 seconds.
    Have a nice day!

  6. #6
    Join Date
    Nov 2011
    Posts
    18
    In case this sheds more light on the issue, it seems like the problem occurs when I try to restore the window while the code is running. If restore it in between it works fine; however, if I restore it once while the code is running then I'm sunk. The window is either not visible on the screen or it is re-sized to the smallest possible dimensions.

    After the update the timer gets set to 300000. I am not getting a stack overflow error. My intention is for this code to run 5 secs after the form is opened and then every 5 min after that (it looks like I've got that part working).



    I'll look into DoEvents.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by imagodaniel View Post
    In case this sheds more light on the issue, it seems like the problem occurs when I try to restore the window while the code is running. If restore it in between it works fine; however, if I restore it once while the code is running then I'm sunk. The window is either not visible on the screen or it is re-sized to the smallest possible dimensions.

    After the update the timer gets set to 300000. I am not getting a stack overflow error. My intention is for this code to run 5 secs after the form is opened and then every 5 min after that (it looks like I've got that part working).



    I'll look into DoEvents.
    sounds like a 'do events' issue
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2011
    Posts
    18
    I have sprinkled DoEvents in my code and I am not seeing an improvement. I placed them before and after heavy processing points. The problem is that I can't do anything in the middle of a query. One of them normally takes about 45 seconds.

    I don't have any experience using DoEvents so I am unsure if I am taking a correct approach.

  9. #9
    Join Date
    Nov 2011
    Posts
    18
    DoEvents didn't do the trick. Removed some of the code that ran under the Timer event. The user will just have to push a button for the functionality that I removed from the timer. No problems, since then.

Posting Permissions

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