On my main form I have a text box with scrolling text that a count from a query is shown, but it does not refresh if criteria that feeds the query changes. I can send the code it is on the on timer event of the form.
On my main form I have a text box with scrolling text that a count from a query is shown, but it does not refresh if criteria that feeds the query changes. I can send the code it is on the on timer event of the form.
I'm not sure why changing the criteria would make any difference but looking at the code would help in the understanding.
This is the code for the scrolling text
Code:Private Sub Form_Timer() Static strMsg As String Static intLet As Integer Static intLen As Integer Dim strTmp As String Const strLen = 150 If Len(strMsg) = 0 Then strMsg = Space(strLen) & "There are" & " " & DCount("[newtestdate]", "[query6]") & " " & "tests and certificates that need your attention, please double click here! " & Space(strLen) intLen = Len(strMsg) End If intLet = intLet + 1 If intLet > intLen Then intLet = 1 End If strTmp = Mid(strMsg, intLet, strLen) Me!lblscroll.Caption = strTmp If strTmp = Space(strLen) Then intLet = 1 'Re-start scrolling End If End Sub
This works fine after the initial loading of the main form but if an item changes on the main form it should reduce the number generated by query6.
Last edited by RuralGuy; 08-25-2016 at 06:36 AM. Reason: Added code tags and indentation
Are you saying that [newtestdate] or [query6] are no longer valid?
No they are still valid, if a certificate or MOT date is entered against a vehicle I would like the scrolling text number to decrease on the fly after close the certificate form. When a MOT is entered the main form updates and shows that the MOT is now good, but the scrolling text remains the same number until the database is closed and reopened or the main form is closed and reopened.
Why not let your Timer Event scroll a Public String, rather that this private one, and then set the string from whatever code you are using to to deal with the MOT's (whatever that is)?
Is that by simply changing the 'Private sub' to a 'public sub' in vba or rewriting the whole vba, sorry for sounding ignorant.
Public Objects are declared *outside* of any Procedure, usually at the top of the class module involved.
Option Compare Database
Option Explicit
Dim YourVariable As String
Private Sub...
Ok I will try moving it to the top of the module.
Let us know how you make out.
Moved it to the top but still not refreshing the count. Does the ontimer event only trigger once when the form is opened can it not be refreshed directly.
On the Event tab of the Properties Sheet for the Form is the "Timer Interval" property. It is in Milliseconds so 1000 = 1 second. To what is yours set?
Hmm...I'm sorry but I thought you were talking about the Marquee not scrolling, not the Count not changing. I need to think a bit. At first blush I would think the DCount should run every Timer Event.
What does [query6] accomplish? Maybe the record count for that query does not change until your form is done.
Maybe try to refresh the form or that control after each run at the bottom of the timer event. Not sure if any of these would work, you don't need all of them, just giving options.
Forms!MyForm.Requery (change MyForm with actual name of your form.)
Forms!MyForm.Refresh
Forms!MyForm.RePaint
Me.lblscroll.Requery
Me.lblscroll.Refresh
Me.lblscroll.Repaint