Query Tables and Screen Flickers

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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:
Rich (BB 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)
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Aug 21, 2009
Messages
584
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
it looks to me like you are pasting recordsets into a sheet not using querytables?

anyway, LockWindowUpdate should only stop the screen redrawing.
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584

ADVERTISEMENT

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

Well-known Member
Joined
Aug 21, 2009
Messages
584
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is there a reason for not using say a menu system to run the macros, rather than controls on sheets?
 

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,340
Members
414,059
Latest member
Amro El ghazawei

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top