Listbox does not refresh immediately after requery

[Jonathan Scott via AccessMonster.com]

I have a list box which should be updated with me!listbox.Requery, but is not
doing so immediately. I have to wait say 15 seconds, and if I hit F9, it
finally refreshes. If I hit F9 constantly for the same amount of time, it
still does not refresh/requery immediately. Why should it take so long to
refresh the screen?

Is this a DB issue? Is the DB just not making my changes public soon enough?
How can I force the DB to make it public immediately so I can see it when I
Requery?

Before, I was building my own list with a shared connection betweem the DB
update and the routine that constructs the list for the listbox for me. That
turned out not to be the route as I could not assign anything decently long
to it. This worked perfectly (so long as I did not assign anythign beyond
2000 characters to it).

I guess Access97 won't let me have my cake and eat it too. Hopefully someone
knows how to fix this situation!

Jonathan Scott

 

[david epsom dot com dot au]

You have a problem with the Write, rather than with the Read.
What are you doing to force the Writes to write back to the
database? How are you doing the writes?

(david)

 

[Jonathan Scott via AccessMonster.com]

My function is using currentdb.execute to write them. I don't have any
transactions running on it. I've even tried docmd.runsql, but the results
became entirely unpredictable.

Jonathan Scott

You have a problem with the Write, rather than with the Read.
What are you doing to force the Writes to write back to the
database? How are you doing the writes?

(david)

I have a list box which should be updated with me!listbox.Requery, but is
not

[quoted text clipped - 22 lines]

Jonathan Scott

 

[Jonathan Scott via AccessMonster.com]

In fact, here is the code.

Private Sub MovePriorityDown_Click()
If (Not isNullOrEmpty(Me!RuleDef.Column(0))) Then
Call changeSequence(CurrentDb, Me!RuleDef.Column(0), 1)
End If
Me!RuleDef.RowSource = getListOfRules(CurrentDb, m_ruleGroupID)
Me!RuleDef.Requery
End Sub

Public Sub changeSequence(dbConnection As Database, ruleID As String,
sequenceDelta As Integer)
Dim currentSequence As Integer
Dim newSequence As Integer
Dim ruleGroupID As String

currentSequence = getRuleSequence(dbConnection, ruleID)
newSequence = currentSequence + sequenceDelta
ruleGroupID = getRuleRuleGroupID(dbConnection, ruleID)
If ((newSequence < 1) Or (newSequence > CInt
(getHighestSequenceOnRuleGroup(dbConnection, ruleGroupID)))) Then
Exit Sub
End If

DoCmd.SetWarnings False
'dbConnection.Execute (""
CurrentDb.Execute ("" _
& "UPDATE Rule " _
& "SET [Sequence] = " & TEMPSEQUENCENUMBER & " " _
& "WHERE [RuleGroupID] = " & ruleGroupID & " " _
& " AND [Sequence] = " & (currentSequence + sequenceDelta))
'dbConnection.Execute (""
CurrentDb.Execute ("" _
& "UPDATE Rule " _
& "SET [Sequence] = " & (currentSequence + sequenceDelta) & " " _
& "WHERE [No] = " & ruleID)
'dbConnection.Execute (""
CurrentDb.Execute ("" _
& "UPDATE Rule " _
& "SET [Sequence] = " & currentSequence & " " _
& "WHERE [RuleGroupID] = " & ruleGroupID & " " _
& " AND [Sequence] = " & TEMPSEQUENCENUMBER)
End Sub

Public Function getListOfRules(dbConnection As Database, ruleGroupID As
String) As String
getListOfRules = "SELECT [No], [SQL] FROM [Rule] WHERE RuleGroupID = " &
ruleGroupID & " ORDER BY Sequence"
End Function

The "dbConnection" in getListOfRules is left over from a previous version,
and can be ignored.

Jonathan Scott

My function is using currentdb.execute to write them. I don't have any
transactions running on it. I've even tried docmd.runsql, but the results
became entirely unpredictable.

Jonathan Scott

You have a problem with the Write, rather than with the Read.
What are you doing to force the Writes to write back to the

[quoted text clipped - 7 lines]

 

[david epsom dot com dot au]

dim ws as object
dim db as object

set ws = application.dbengine.workspaces(0)
set db = currentdb

ws.begintrans
db.Execute ....
db.execute ....
db.execute ....
ws.committrans 1 'dbFlushOSCacheWrites/dbForceOSFlush

application.dbengine.idle 8 'dbRefreshCache


Normally, data is written back from the jet cache
as part of a backround process, using the cache
timeout value read from the registry or set by
dbengine.SetOption. You can bring forward this
background process by using the idle method, or
you can change the timer value, or you can tell
the engine that writes should be synchronous (the
action does not complete until the data has been
written back from the cache).

Explicit transactions are synchronous by default,
so instead of using dbEngine.SetOption, or .idle,
you can just wrap your action queries in explicit
transactions.

Transactions have an optional parameter which
instructs Windows to flush the file to disk. This
was added for Windows 95, because Win 95 needed
to be specifically instructed. Each subsequent
version of windows has been slightly different
from every other version, so the effect of this
flag is indeterminate at present.

(david)

In fact, here is the code.

Private Sub MovePriorityDown_Click()
If (Not isNullOrEmpty(Me!RuleDef.Column(0))) Then
Call changeSequence(CurrentDb, Me!RuleDef.Column(0), 1)
End If
Me!RuleDef.RowSource = getListOfRules(CurrentDb, m_ruleGroupID)
Me!RuleDef.Requery
End Sub

Public Sub changeSequence(dbConnection As Database, ruleID As String,
sequenceDelta As Integer)
Dim currentSequence As Integer
Dim newSequence As Integer
Dim ruleGroupID As String

currentSequence = getRuleSequence(dbConnection, ruleID)
newSequence = currentSequence + sequenceDelta
ruleGroupID = getRuleRuleGroupID(dbConnection, ruleID)
If ((newSequence < 1) Or (newSequence > CInt
(getHighestSequenceOnRuleGroup(dbConnection, ruleGroupID)))) Then
Exit Sub
End If

DoCmd.SetWarnings False
'dbConnection.Execute (""
CurrentDb.Execute ("" _
& "UPDATE Rule " _
& "SET [Sequence] = " & TEMPSEQUENCENUMBER & " " _
& "WHERE [RuleGroupID] = " & ruleGroupID & " " _
& " AND [Sequence] = " & (currentSequence + sequenceDelta))
'dbConnection.Execute (""
CurrentDb.Execute ("" _
& "UPDATE Rule " _
& "SET [Sequence] = " & (currentSequence + sequenceDelta) & " " _
& "WHERE [No] = " & ruleID)
'dbConnection.Execute (""
CurrentDb.Execute ("" _
& "UPDATE Rule " _
& "SET [Sequence] = " & currentSequence & " " _
& "WHERE [RuleGroupID] = " & ruleGroupID & " " _
& " AND [Sequence] = " & TEMPSEQUENCENUMBER)
End Sub

Public Function getListOfRules(dbConnection As Database, ruleGroupID As
String) As String
getListOfRules = "SELECT [No], [SQL] FROM [Rule] WHERE RuleGroupID = "
&
ruleGroupID & " ORDER BY Sequence"
End Function

The "dbConnection" in getListOfRules is left over from a previous version,
and can be ignored.

Jonathan Scott

My function is using currentdb.execute to write them. I don't have any
transactions running on it. I've even tried docmd.runsql, but the results
became entirely unpredictable.

Jonathan Scott

You have a problem with the Write, rather than with the Read.
What are you doing to force the Writes to write back to the

[quoted text clipped - 7 lines]

Jonathan Scott