Query Tables and Screen Flickers

[ClimoC]

Ahoy

No, this is not about ScreenUpdating = True/False

Neither is it because you've turned SU off, and then used 'Select' or 'DoEvents' etc in your code.

When running an update for a QueryTable (once a second), 3 'buttons' (activeX image controls with code behind them) flicker - though I see no reference to selecting and/or screenupdating in my code

Is this purely something about refreshing a querytable that I can't fix?

Is there a subclass or API call I can intercept to stop drawing the images alone?

Much much googling on this has returned fruitless

My code:

Public Sub ActivateConn()

    Dim strFilespec As String
    Dim strConn As String
    Dim sqlStr As String

    Set RecSet = New ADODB.Recordset
    Set cnImportConn = New ADODB.Connection
    strFilespec = "\\FilepathServeretc\Filename.accdb"
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilespec & ";Persist Security Info=False;"

    strsql = "SELECT * FROM [work]"
    Debug.Print strsql
    
On Error Resume Next
    With cnImportConn
                    .CursorLocation = adUseClient
                    .Open strConn
                    .CommandTimeout = 0
                    Set RecSet = .Execute(strsql)
    End With
If Err.Number <> 0 Then
        Application.StatusBar = "Connection to Desktop Failed"
End If
On Error GoTo 0
End Sub

Public Sub AddNewRecord()

     strsql = "INSERT INTO [work] (txdate, channel, programme, available, assigned, startdate, duedate, description, tape, status) " & _
     "VALUES (#" & txdate & "#, '" & channel & "', '" & programme & "', " & available & ", '" & assigned & "', #" & startdate & "#, #" & _
     duedate & "#, '" & description & "', '" & tape & "', '" & status & "')"

cnImportConn.Execute (strsql)

End Sub

Public Sub ReadRec()

    ThisWorkbook.Sheets("Sheet2").Range("A1").CopyFromRecordset RecSet
    
End Sub

Public Function WriteRec(ByVal ID As Long, Field As String, Val As Variant)
If cnImportConn Is Nothing Then Run "ActivateConn"
If Field = "ID" Then Exit Function
Select Case True
            Case Field Like "*date"
                    Val = "#" & Val & "#"
            Case Field Like "*vail*"
                    Val = CBool(Val)
            Case Else
                    Val = "'" & Val & "'"
End Select
            
     strsql = "UPDATE [work] SET " & Field & " = " & Val & " WHERE ID = " & ID
     
cnImportConn.Execute (strsql)

End Function

Public Function Refreshing()
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Application.ScreenUpdating = False
On Error Resume Next
'If cnImportConn Is Nothing Then Run "ActivateConn"
ThisWorkbook.Sheets("WorkFlow").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
Err.Clear
On Error GoTo 0
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
'Application.ScreenUpdating = True
Application.EnableCancelKey = xlInterrupt
End Function

Sub startcatchdelete()
Application.OnKey "{DELETE}", "CaughtDelete"
End Sub

Sub stopcatchdelete()
Application.OnKey "{DELETE}"
End Sub

Public Sub CaughtDelete()
If cnImportConn Is Nothing Then Run "ActivateConn"
If Selection.Address = ActiveCell.EntireRow.Address Then
            ID = Cells(ActiveCell.Row, 2).Value
            strsql = "DELETE * FROM [work] WHERE ID = " & ID
            cnImportConn.Execute (strsql)
Else
            Selection.ClearContents
End If
End Sub

Sub QueryTableRefresh()
Dim qt As QueryTable
vSetTime = Now + TimeValue("00:00:01")
 
Application.OnTime vSetTime, "QueryTableRefresh"

Refreshing

End Sub

Sub EndRefresh()
On Error Resume Next
Application.OnTime EarliestTime:=vSetTime, Procedure:="QueryTableRefresh", Schedule:=False
On Error GoTo 0
End Sub

In short, there's no REASON why I couldn't just use the selection_change event to fire the same macros as the buttons but... well... it doesn't look anywhere near as good (I have a thing about making VB apps look as good as the code behind them is smart)

 

[RoryA]

Do you need ActiveX rather than Pictures? (always best to avoid ActiveX on sheets if at all possible)
Anyway, you could try the LockWindowUpdate API.

 

[ClimoC]

Morning Ror'.

Are you suggesting a Form control instead? I never have much fun with them.

Have tried a straight Insert>Image and assigned a macro, same flickers

Would the LockWindowUpdate API intercept cause any functionality issues with my QT?

I have selection and worksheet changes in this - A worksheet change calls an SQL-sender which gets the record ID, the field name, and the new value, and SQL's the change to the host .accdb

Essentially I'm using (as always, I like to be clever/difficult at the same time - clever being a relative thing considering I'm usually on here tapping your brain, I digress...) Excel's worksheet functionality and GUI to have an accdb serve as a... well server DB - where multiple users may view and update data in a live environment. Apart from the image screen flickers, the solution I have created works well

 

[RoryA]

it looks to me like you are pasting recordsets into a sheet not using querytables?

anyway, LockWindowUpdate should only stop the screen redrawing.

 

[ClimoC]

Oh sorry - no not anymore. That sub 'ReadRec' isn't employed anymore from the 'add record' form (as it updates eevry second, so once the new record has been sent to the accdb, resolved, and refreshed, it'll pop up in the QT after about 2-3 seconds)

So LockWindowUpdate isn't really going to help me because as soon as the table finishes refreshing, and I disable the LockWindowUpdate (or revert to its original state), it's going to flicker again... no?

 

[ClimoC]

Oh sorry - no not anymore. That sub 'ReadRec' isn't employed anymore from the 'add record' form (as it updates eevry second, so once the new record has been sent to the accdb, resolved, and refreshed, it'll pop up in the QT after about 2-3 seconds)

So LockWindowUpdate isn't really going to help me because as soon as the table finishes refreshing, and I disable the LockWindowUpdate (or revert to its original state), it's going to flicker again... no?

 

[RoryA]

Ah, gotcha. I think your Refreshing sub is the issue because it turns screenupdating on and off. That causes the whole screen to be redrawn and hence your controls flicker.

Edit: ignore that - haven't got the glasses on and didn't see you had commented that out! I suspect you are out of luck and the update triggered by the QT is as you said the problem.

 

[RoryA]

Is there a reason for not using say a menu system to run the macros, rather than controls on sheets?

 

[ClimoC]

Is there a reason for not using say a menu system to run the macros, rather than controls on sheets?

Aesthetic

 

[ClimoC]

Is there a reason for not using say a menu system to run the macros, rather than controls on sheets?

*sigh*. Guess I'll have to do a boring XML mod for some ribbon buttons...

They don't look nearly as cool. I like making my sheets look all Web-ey