Would it be possible to run multiple seperate subs in a timer sub

This is part of the timer sub. It describes the assignement of values to unbound textboxes on an access form. This is just one of them. In total there are 8 likewise procedures.
I like to develop these further by dimming values. However if  I dim for example LDay as Integer do I have to call  Lday1, Lday2 and so on.

Is it possible to dim these values in separate subs and call the subs in the timer procedure. In this case all subs will be more or less identical and it saves a lot of time and  the timer event will probably run smoother.

The Echo true on and echo false are the remnants of an attempt to reduce flicker of the form. Lateron I just reduced the timer interval to lessen this effect running ms access 2016.

Private Sub Form_Timer()

    Dim datLocalTime    As Date
    Dim datGMTTime      As Date
    Dim datLocationTime As Date
    Dim lngRed          As Long
    Dim lngWhite        As Long
    Dim lngGreen        As Long
    Dim lngLtGreen      As Long
    Dim lngDkGrey       As Long
   
    'Setup background colors
    lngRed = RGB(255, 0, 0)
    lngWhite = RGB(255, 255, 255)
    lngGreen = RGB(0, 255, 0)
    lngLtGreen = RGB(153, 255, 90)
    lngDkGrey = RGB(28, 28, 28)
       
   
   
    datLocalTime = GetTimeFromServer(conServerName, "Local")
    datGMTTime = ConvertLocalToGMT
   
    lngActivityCounter = lngActivityCounter + 1
   
        If lngActivityCounter >= 1 Then
         Me.TimerInterval = 30000
         
        Else
         Me.TimerInterval = 1000
             
     End If
   
    Application.Echo False
   
   
       
    If Len(Me.cboColOneRowOne.Column(3)) Then
        datLocationTime = ConvertTime(datGMTTime, "UTC", cboColOneRowOne.Column(3))
        'Me.txtColOneRowTwoTime = Format(datLocationTime, "Long Date") & "  " & Format(datLocationTime, "Long Time")
        Me.txtColOneRowTwoTimeDigital = Format(datLocationTime, "HH:MM")
        VerticallyCenter Me.txtColOneRowTwoTimeDigital
        'VerticallyCenter Me.txtShColOneRowOne
        BottomAlign Me.txtShColOneRowOne
        'TopAlign Me.txtColOneRowTwoTimeDigital
        '\PressTab
        'Me.txtMoveFocus.SetFocus
        Application.Echo True
       
    End If
   
   Application.Echo False
   
    Select Case cboColOneRowOne.Column(4)
       
                   
                Case "AMS": With Me.txtColOneRowTwoTimeDigital
                                          .ForeColor = lngLtGreen
                                          .BackColor = lngDkGrey
                                     End With
                Case "UTC": With Me.txtColOneRowTwoTimeDigital
                                          .ForeColor = lngRed
                                          .BackColor = lngDkGrey
                                     End With
                Case Else: With Me.txtColOneRowTwoTimeDigital
                                          .ForeColor = lngGreen
                                          .BackColor = lngDkGrey
                                     End With
           
      Application.Echo True
     
     End Select
faraoosirisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I wouldn't use timer events to colorize a form.  You are slowing everything down and having to deal with flicker.  It will just get worse.  It will be especially problematic if you have users with older, slower computes so make sure you test at the lowest level of hardware before you go much further.

If you want to use public constants, dim them in a standard module.  Do NOT use a form or report or class module.  I have a module that I name modPublicVariables and any time I want to define a variable that is used in more than one procedure, I dim it here as Public.  This particular constant is what I use to help me build strings without having to worry about embedding double quotes.  For example
strSQL = "Select  tbl1.* Where CompName = " & QUOTE & Me.txtCompName & QUOTE

Option Compare Database
Option Explicit

Public Const QUOTE = """"

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Is it possible to dim these values in separate subs and call the subs in the timer procedure.>>

  Not in other subs, unless you declare them as static.

 It's clearer to declare them at the top of a forms module.

 I think though if you describe in a bit of detail what your trying to achieve, there may be other options.

 Nothing wrong with using a form timer either depending on how you structure it.

Jim.
0
faraoosirisAuthor Commented:
Thank you both for your reply.
@Jim. I want to accomplish the following code for the aformentioned code

If Len(Me.cboColEightRowOne.Column(3)) Then
        datLocationTime = ConvertTime(datGMTTime, "UTC", cboColEightRowOne.Column(3))
        'Me.txtColOneRowTwoTime = Format(datLocationTime, "Long Date") & "  " & Format(datLocationTime, "Long Time")
        Me.txtColEightRowTwoTimeDigital = Format(datLocationTime, "HH:MM")
        VerticallyCenter Me.txtColEightRowTwoTimeDigital
        VerticallyCenter Me.txtShColEightRowOne
        LDate = DateValue(datLocationTime)
        glat = Val(Me.cboColEightRowOne.Column(5))
        glong = Val(Me.cboColEightRowOne.Column(5))
        LYear = year(datLocationTime)
        LMonth = month(datLocationTime)
        LDay = day(datLocationTime)
        LValue = DateDiff("h", datGMTTime, datLocationTime)
        LDst = Abs(IsDaylightSavingsDate(LDate, Me.cboColEightRowOne.Column(3)))
        LSunrise = Format(CalcHrsMins(sunrise(glat, glong, LYear, LMonth, LDay, LValue, LDst) * 1440), "hh:nn")
        LSunset = Format(CalcHrsMins(sunset(glat, glong, LYear, LMonth, LDay, LValue, LDst) * 1440), "hh:nn")
        '\PressTab
        'Me.txtMoveFocus.SetFocus
        Application.Echo True

The dimming is done to fill in de arguments of the sunrise sunset functions. As Iám replying I realize it is a sunrise and sunset value which dont need to be updated every minute.

I will take some time to grab the meaning of the answer of Pat and how to apply it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I meant a general description of what you are doing with the form.   I can see you dimming certain controls, but it is not clear how all that is setup, how many controls there are, etc.

 You mention:

" This is just one of them. In total there are 8 likewise procedures."

 which sounds like you are repeating a lot code.  There are ways around that.   For example, passing in a reference to the control, rather than naming it explicitly in the code.

 That aside, a screen shot or an attached DB with the form in question and a description of how it is being used by the user would be helpful.

  Your getting flicker because of this:

        If lngActivityCounter >= 1 Then
         Me.TimerInterval = 30000
         
        Else
         Me.TimerInterval = 1000

  Depending on what you are doing with the form, both values are probably too small (especially the second).

Jim.
0
faraoosirisAuthor Commented:
Snapshot of the main form
This is a snapshot from the main form. When you open the database this form opens. Bij double clicking on the small black border the access application becomes invisible, and by holding the left mouse key on the same border you can move it freely accros the screen.

It displays 8 timezones. In the middle of the snapshot you can seen that mouse over the toplabel a combobox appears. This gives the user the possibility to change the timezone. This is done via a link to microsoft outlook. Outlook calculates the local time.

This clock is working the way I want it. In the time zone on the left you can already see a rising and setting symbol of the sun. In de space between the name of the zone and the display of the time I want to display the sunset and sunset of the time zone.

The last part is the one I'am currently working on. I Want to use te sunrise and sunset functions as used in the Timer sub.
EE.accdb
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I looked at what you did and you are doing OK, but there are several things that you could do that would help you a lot.  I am short on time at the moment or I would re-do it for you, but here is what I saw:

1. On this:

    If Len(Me.cboColEightRowOne.Column(3)) Then

  Do not do checks like this where you leave it implied as to what constitutes True.   You should always be explicit, so do the check like this:

       If Len(Me.cboColEightRowOne.Column(3))>0 Then

  or if checking for a True / False condition, do not leave it as:

       If <expression> Then

 do it as:

       If <expression> = True Then

 Always give the condition.


2. In regards to the code in the timer event and having to repeat it eight times, you do not need to do that and this is what your question was about.   What you want is only one copy of the code, but call it eight times.   There are two ways you can do this:

  a. You can refer to a control with a string like this:

     Forms("myForm")("myControl")

      or if the code is in the form, you can do:

     Form("myControl")     or     Me("myControl")

    How does that help?   Because you can do this:

     For intK = 1 to 8
          Form("myControl" & intK).ForeColor = 0
     Next intK

   This assumes that your controls are named   myControl1, myControl2, myControl3, etc.   So you can have an array of controls.  Now instead of this:

    If Len(Me.cboColEightRowOne.Column(3)) Then

  you can do this in the timer event:

    For intK = 1 to 8
        strControlName = "myControl" & intK
        Call SetTime(strControlName)
     Next intK

and have one copy of the code which you call in a subroutine:

Private Sub SetTime(strControl as string)

       If  Len(Me(strControl))>0 Then

b.   The second way to do this is to pass a reference to the control itself rather than using a string.   To do that, you would define your sub like this:

Private Sub SetTime(ctl as Control)

       If  Len(ctl)>0 Then

and in the timer event, now do this:

    For intK = 1 to 8
        Call SetTime(Me("myControl" & intK)
    Next intK

 Some find it easier to work with the strings because you can call a procedure from the  debug window  just by typing to test it.     There is also a 3rd method of doing things which is a combination of the two (pass a string, then set a control reference at the start of a procedure), but I have given you enough to look at already.

3. Last thing is break up the code in the timer event.   You do not want to do everything in there every time it fires off.   Only use it as a trigger to check if things need to be done.  What you want is to have separate procedures for each thing you need to do (like setting the clocks time, the sunrise/sunset, etc).

Let us say you fire the timer off every 5 seconds.

   First, you would want to check if the minute has changed.   If it has not, then nothing need be done.   You can do this by saving the last date/time you checked vs Now():

    If DateDiff("s",dtLastDateTime, Now())>60 then
         ' Update the clocks
         For intK = 1 to 8
            strControlName = "myControl" & intK
            Call SetTime(strControlName)
         Next intK
    End If

    dtLastDateTime = Now()

  So if the minute had changed, only then would you update the clocks.    In the process of doing that, then you would check if the date changed.   If so, only then would you update the sunrise and sunset values.

 
  Doing everything each time the OnTimer fires is a lot of extra overhead.    Now because you are only doing the things that need doing, you should see a lot less screen flicker.

  Also by writing the procedures separate from the OnTimer event, you will be able to call them when someone selects a new time zone using one of the combos.

 I wish I had the time right now to spend on this, but I have a number of things I need to get done today.   Hopefully I have given you enough to get you started on restructuring this.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and by the way, nice job on the display in the form!

Jim.
0
faraoosirisAuthor Commented:
Thank you Jim, I wil try to incorporate your suggestions and get back to you.
0
faraoosirisAuthor Commented:
I'am in the process of renaming the comboboxes. In the timer events however I see some references to textboxes.
The numbering for example me.txtColOneRowTwoTime. How about to deal with it. Do I have to take these out of the timer sub or go about the same way with the intK

If Len(Me.cboRowOneCol2.Column(3)) Then
        datLocationTime = ConvertTime(datGMTTime, "UTC", cboRowOneCol2.Column(3))
        'Me.txtColOneRowTwoTime = Format(datLocationTime, "Long Date") & "  " & Format(datLocationTime, "Long Time")
        Me.txtColTwoRowTwoTimeDigital = Format(datLocationTime, "HH:MM")
        LValue = DateDiff("h", datGMTTime, datLocationTime)
        VerticallyCenter Me.txtColTwoRowTwoTimeDigital
        VerticallyCenter Me.txtShColTwoRowOne
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Take them out of the timer sub.

Again, you should have only one piece of code to do each function, which you call passing in the control name as an argument.  In the procedure, you then refer to the argument.

So for example:

For intJ  = 1 to 10
    For intK = 1 to 8
        strControlName = "myControlRow" & intJ & "Col" & intK
        Call SetTime(strControlName)
     Next intK
Next IntJ

  This would give you myControlRow1Col1,  myControlRow1Col2, and so on to 8, then myControlRow2Col1, myControlRow2Col2 and so on until your hit row 10.

 Your procedure would start off like this:

Private Sub SetTime(strControl as string)



and then the check would be:


      If  Len(Me(strControl).Column(3))>0 Then


everywhere in the procedure, you can refer to the control with Me(strControl)


 Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
By the way, if you need to work with more than one control, you can pass in more control names

OR

 you could just pass the row  and column you are on to the procedure, and then work from that.   Example:

  That might be cleaner for you.

Jim.
0
faraoosirisAuthor Commented:
Hi Jim

I made this sub just to see how it behaves. I have only 8 colums in one row to set the time. Therefore is set intJ to 1. Instead off calling the  set time sub I programmed the msg box to see what is going on.

Sub Double_Loop_Example()

   Dim intJ, intK As Integer

   For intJ = 1 To 1
      For intK = 1 To 8
         MsgBox "cboRow" & intJ & "Col" & intK
          Next intK
   Next intJ

End Sub

My question to you wether is is possible to call a second sub in above mentioned sub and have the for next embedden in the outer.
Sequence then should add up to cboRow1Col1, txtRow2Col1, cboRow1Col2, txtRow2Col2 and for the next part could I use the same intK for going to the next column or choose a use a range outside intJ and intK. The second sub could be something like subFillTextBox.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sub Double_Loop_Example()

   Dim intJ, intK As Integer

   For intJ = 1 To 1
      For intK = 1 To 8
         MsgBox "cboRow" & intJ & "Col" & intK
        Call subFillTextBox( "cboRow" & intJ & "Col" & intK)
          Next intK
   Next intJ

End Sub

Private Sub subFillTextBox(strControlName As string)

   If Len(Me(strControlName).Column(3))>0 then
      ' Do something
   End If
End Sub


Jim.
0
faraoosirisAuthor Commented:
Right now I 'am reworking all the comboboxes and textboxes and getting the for next statement to work.

I created a sub from the original timer sub and called it SetTime. The next step for me is to split the other functions namely the filling in of the textboxes.

Doing this step by step as to loose overview and keep the clock working. So far so good.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Excellent!

Next week  I will have time to help you out if you get stuck

But doing what you are doing is a great way to learn.

Once you have worked through this I can take what you’ve done and rework it a little more to show you two additional techniques that you can use when doing things like this.

Jim
0
faraoosirisAuthor Commented:
Good morning.

This is the code from the timer event.

Private Sub Form_Timer()
Dim dtLastDateTime As Date
Dim intJ, intK, intL As Integer
Dim strComboName, strTextboxName, strShieldName As Control

If DateDiff("s", dtLastDateTime, Now()) > 60 Then
For intJ = 1 To 1
      For intK = 1 To 8
      For intL = 2 To 2
        strComboName = "cboRow" & intJ & "Col" & intK
        strTextboxName = "txtRow" & intL & "Col" & intK
        'strShieldName = "txtShRow" & intL & "Col" & intK
         'MsgBox LCounter1 & "-" & LCounter2
         
         Call SetTime(Me(strComboName))
         'MsgBox (strComboName)
         'MsgBox (strTextboxName)
         'MsgBox (strShieldName)
      Next intL
      Next intK
   Next intJ
 
  End If
 
  dtLastDateTime = Now()
   
End Sub

However it does not transfer the strComboName to the SetTime sub.

If Len(Me(strComboName).Column(3)) > 0 Then
        datLocationTime = ConvertTime(datGMTTime, "UTC", Me(strComboName).Column(3)

F8 gives me the code is 438 and the strComboName is empty. It does work when I put in eg "cboRow1Col1"manually. What am I missing in this process.
0
faraoosirisAuthor Commented:
Back to square one. HOWEVER I got the functions to work with just one copy of the timer sub in combination with SetTime sub.

It still gives a screen flicker and I 'am wondering if the DateDiff is working it is supposed to be working. The code sofar.

Private Sub SetTime(strControlName, strTextboxName As String)


    Dim datLocalTime    As Date
    Dim datGMTTime      As Date
    Dim datLocationTime As Date
    Dim lngRed          As Long
    Dim lngWhite        As Long
    Dim lngGreen        As Long
    Dim lngLtGreen      As Long
    Dim lngDkGrey       As Long
    Dim LValue          As Integer
    Dim LYear           As Integer
    Dim LMonth          As Integer
    Dim LDay            As Integer
    Dim LDst            As Integer
    Dim LSunrise        As String
    Dim LSunset         As String
    Dim glat            As Double
    Dim glong           As Double
    Dim LDate           As Date
    Dim intJ, intK      As Integer
    
    
    
    'Setup background colors
    lngRed = RGB(255, 0, 0)
    lngWhite = RGB(255, 255, 255)
    lngGreen = RGB(0, 255, 0)
    lngLtGreen = RGB(153, 255, 90)
    lngDkGrey = RGB(28, 28, 28)
       
    
    
    datLocalTime = GetTimeFromServer(conServerName, "Local")
    datGMTTime = ConvertLocalToGMT
    
    
       
    
       
    If Len(Me(strControlName).Column(3)) > 0 Then
        datLocationTime = ConvertTime(datGMTTime, "UTC", Me(strControlName).Column(3))
        'Me.txtColOneRowTwoTime = Format(datLocationTime, "Long Date") & "  " & Format(datLocationTime, "Long Time")
        Me(strTextboxName) = Format(datLocationTime, "HH:MM")
        VerticallyCenter Me.txtRow2Col1TimeDigital
        LDate = DateValue(datLocationTime)
        glat = Val(Me.cboRow1Col1.Column(5))
        glong = Val(Me.cboRow1Col1.Column(6))
        LYear = year(datLocationTime)
        LMonth = month(datLocationTime)
        LDay = day(datLocationTime)
        LValue = DateDiff("h", datGMTTime, datLocationTime)
        LDst = Abs(IsDaylightSavingsDate(LDate, Me.cboRow1Col1.Column(3)))
        LSunrise = Format(CalcHrsMins(sunrise(glat, glong, LYear, LMonth, LDay, LValue, LDst) * 1440), "hh:nn")
        LSunset = Format(CalcHrsMins(sunset(glat, glong, LYear, LMonth, LDay, LValue, LDst) * 1440), "hh:nn")
        'VerticallyCenter Me.txtShRow1Col1
        BottomAlign Me.txtShRow1Col1
        'TopAlign Me.txtRow2Col1TimeDigital
        '\PressTab
        'Me.txtMoveFocus.SetFocus
        Application.Echo True
        
    End If
    
    
    
   Application.Echo False
    
    Select Case cboRow1Col1.Column(4)
        
                   
                Case "AMS": With Me.txtRow2Col1TimeDigital
                                          .ForeColor = lngLtGreen
                                          .BackColor = lngDkGrey
                                     End With
                Case "UTC": With Me.txtRow2Col1TimeDigital
                                          .ForeColor = lngRed
                                          .BackColor = lngDkGrey
                                     End With
                Case Else: With Me.txtRow2Col1TimeDigital
                                          .ForeColor = lngGreen
                                          .BackColor = lngDkGrey
                                     End With
            
      Application.Echo True
      
     End Select
     
    
     
     
    
    'Me.txtLocalMachineTime = Format(datLocalTime, "Long Date") & "  " & Format(datLocalTime, "Long Time")
    ''Me.txtLocalMachineTimeDigital = Format(datLocalTime, "HH:MM:SS")

    ''Me.txtGMTTime = Format(datGMTTime, "Long Date") & "  " & Format(datGMTTime, "Long Time")
    'Me.txtGMTTimeDigital = Format(datGMTTime, "HH:MM:SS")
    
    'datLocationTime = datGMTTime + GetMinutesOffset("-08:00") / 1440
    ''Me.txtPacificTime = Format(datLocationTime, "Long Date") & "  " & Format(datLocationTime, "Long Time")
    'Me.txtPacificTimeDigital = Format(datLocationTime, "HH:MM:SS")
    
    'datLocationTime = datGMTTime + GetMinutesOffset("-07:00") / 1440
    'datLocationTime = datGMTTime + GetMinutesOffset(Left(Combo70.Column(2), 6)) / 1440
    ''datLocationTime = ConvertTime(datGMTTime, "UTC", Combo70.Column(3))
    'Me.txtMountainTime = Format(datLocationTime, "Long Date") & "  " & Format(datLocationTime, "Long Time")
    ''Me.txtMountainTimeDigital = Format(datLocationTime, "HH:MM:SS")
    
    ''Me.txtLocationTimeZone = Me.lstTimeZones.Column(2)
    
    ''If Len(Me.lstTimeZones.Column(3)) Then
        '\datLocationTime = datGMTTime + GetMinutesOffset(Me.lstTimeZones.Column(3)) / 1440
        'datLocationTime = datGMTTime + GetMinutesOffset(Left(Me.lstTimeZones.Column(3), 6)) / 1440
        'Me.txtLocationTime = Format(datLocationTime, "Long Date") & "  " & Format(datLocationTime, "Long Time")
        'Me.txtLocationTimeDigital = Format(datLocationTime, "HH:MM:SS")
    ''End If
    
    End Sub
    


Sub Form_Timer()
Dim dtLastDateTime As Date
Dim intJ, intK, intL As Integer
Dim strControlName, strTextboxName, strShieldName As String


If DateDiff("s", dtLastDateTime, Now()) > 60 Then
For intJ = 1 To 1
      For intK = 1 To 8
      For intL = 2 To 2
        strControlName = "cboRow" & intJ & "Col" & intK
        strTextboxName = "txtRow" & intL & "Col" & intK & "TimeDigital"
        'strShieldName = "txtShRow" & intL & "Col" & intK
         'MsgBox LCounter1 & "-" & LCounter2
         
         Call SetTime((strControlName), (strTextboxName))
         'MsgBox (strComboName)
         'MsgBox (strTextboxName)
         'MsgBox (strShieldName)
      Next intL
      Next intK
   Next intJ
  
  End If
  
  dtLastDateTime = Now()
   
End Sub 

Open in new window


Kind regards
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Can you post another DB please....I've got some time to look at this.

Jim.
0
faraoosirisAuthor Commented:
Good day Jim,

As per request I attached a copy of the database I'am working on. Right now I'am trying to figure out to get the sunrise sunset times to display the correct values.

Somewhere in the code it seems to make a difference using true false boolean changed to a absolute value displaying 1 and 0, and the usage of 0 versus -1. The last option seems to give the correct values.
EE---Copy.accdb
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
With the Boolean, I haven't looked at that yet, but it may be related to this:

Dim strControlName, strTextboxName, strShieldName As String

In VBA, when you do this, only the last variable ends up as a string.  The rest will end up as variants.   You need to do it like this:

Dim strControlName  As String, strTextboxName  As String, strShieldName As String

which is why most VBA developers will do this:

Dim strControlName  As String
Dim strTextboxName  As String
Dim strShieldName As String

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
On the flickering; your still calling SetTime() every time the timer fires.  There are two problems:

1.  You declared  dtLastDateTime as part of the timer procedure.   You want it at the top of the module so it retains its value between calls (is a global variable).   The other option would be to leave it where it is, but declare it as STATIC (which is basically the same thing. but you see it with the procedure).

2. dtLastDateTime is being reset each time through (this was a mistake in my example).

Your OnTimer Code should look like this:

Sub Form_Timer()

    Dim intJ As Integer, intK As Integer, intL As Integer
    Dim strControlName As String, strTextboxName As String, strShieldName As String

    If DateDiff("s", dtLastDateTime, Now()) > 60 Then
        ' Hold the last reset date/time
        dtLastDateTime = Now()

        ' Update the time on the clocks
       For intJ = 1 To 1
            For intK = 1 To 8
                For intL = 2 To 2
                    strControlName = "cboRow" & intJ & "Col" & intK
                    strTextboxName = "txtRow" & intL & "Col" & intK & "TimeDigital"
                    strShieldName = "txtShRow" & intJ & "Col" & intK
                    Call SetTime((strControlName), (strTextboxName), (strShieldName))
                Next intL
            Next intK
        Next intJ
        
   End If

End Sub

Open in new window


Also note that SetTime() needs to be defined like this:

Private Sub SetTime(strControlName As String, strTextboxName As String, strShieldName As String)

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
faraoosirisAuthor Commented:
I will apply the corrections and get back to you.

With regard to the sunrise sunset calculation I noticed the correction was applied twice.

The outlook function calculates the new local time. The LValue, the time difference between local time and GMT, therefore change from -8 to -7 for San Fransisco. The LDst in the calculation for sunrise sunset therefore can be set to "0" because the LValue is also adjusted and generates the correct time at this time.
0
faraoosirisAuthor Commented:
I declared dtLastDateTime as static and now the update is performed after 60 seconds.

Can I use the dateDiff method also for updating thes unrise sunset once a day, and can it be done in the Timer sub as well.

Furthermore I have a question not related to this question but just curious to know if now() can be rounded done to the minute.
This to start the timer at the whole minute and the differnece between the system time display in the windows toolbar and the worldclock are
more or less in sync.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I declared dtLastDateTime as static and now the update is performed after 60 seconds.>>

 Good.   You should be seeing a lot less flicker as a result.

<<Can I use the dateDiff method also for updating thes unrise sunset once a day, and can it be done in the Timer sub as well.>>

 You could, but it would not be accurate for each clock.

 You would want the sunrise/sunset to update only when each clock crosses into a new day.   So it really should be part of SetTime().   When you set the time, if it is the first minute of a new day, then recalculate the sunrise/sunset.

<<Furthermore I have a question not related to this question but just curious to know if now() can be rounded done to the minute.>>

 Here's one way to do that:

 TimeSerial(Hour(Now()),Minute(Now()),0)

Jim.
0
faraoosirisAuthor Commented:
Hello Jim,

I'am finishing the clock by putting in the textboxes to display the sunrise and sunset.

Thank your for all the time and patience solving the question, Would it be possible to  run multiple separate subs in a timer sub, and giving me insight and learn to play with the seemingly unlimited possibilities of the underrated database program access.
0
faraoosirisAuthor Commented:
Would it be possible to  run multiple seperate subs in a timer sub is solved by the expert help and patience of Jim in guiding this access rookie through the possibilities of Microsofts Access
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.