Update MS Access Marquee text on each loop

[iainct]

I admit to being a bit of a novice, but have designed myself a very handy personal MS Access database. I have tried to find a solution to the following on the net, but have been unsuccessful so far, hence my post (the first time I've done this).


I have a marquee on a form in MS Access, which scrolls the count of "incomplete tasks" to do. A "Tasks COUNT Query" provides a number from zero upwards. After the form loads, the code below scrolls a message (right to left) on the marquee in the form "There are X tasks requiring action." X is the number provided from the "Tasks COUNT Query". I would like the text string on the marquee to update on each loop, so that when I mark a task as complete, the next pass on the marquee shows the number (X) as being the updated count.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Number AsString

Set db = CurrentDb
Set rst = db.OpenRecordset("Tasks COUNT Query")

IfNot(rst.EOF And rst.BOF)Then
DoWhileNot rst.EOF
Number = rst![Tasks]
strTxt = strTxt &"There are "& Number &" tasks requiring action."
rst.MoveNext
Loop
EndIf
rst.Close
strTxt = Left(strTxt, Len(strTxt))'remove the coma at the end
strTxt = Space(30)& strTxt 'start position

Set rst =Nothing
Set db =Nothing
Me.TimerInterval =180

EndSubThe following code runs on the form timer interval:
PrivateSub Form_Timer()
Dim x

OnErrorGoTo Form_Timer_Err

x = Left(strTxt,1)
strTxt = Right(strTxt, Len(strTxt)-1)
strTxt = strTxt & x
lblMarqTask.Caption = Left(strTxt,180)
ExitSub
Form_Timer_Exit:
ExitSub

Form_Timer_Err:
Me.TimerInterval =0
ExitSub
EndSubI would be grateful for any assistance

[ranman256]

you dont need all this code to get the count, ...its just 1 line:

msgox Dcount("*","myquery")