Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    refresh switchboard (2003)

    Hi All, I have a main switchboard which, when opened, will refresh an option label (forecolor and text) based on the value in textbox with control source set to: =DCount("[surname]","qryHRissuesAllHMOsACTIVE","[fire1]>335"). This textbox is not visable. All this is to show the number of people (HMOs) who are due for their yearly fire training. This all works fine when the switchboard is opened for the first time, however, the switchboard doesn't update this option label when I've opened, edited data, then closed another form (where the names of the HMOs and the dates they've completed fire training. I've tried various methods such as Refresh and Recalc, but can't seem to make it work.....short of closing the swithboard then re-opening each time. How can I make this work? Thanks, Van

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: refresh switchboard (2003)

    Try requerying the text box in the OnActivate event of the switchboard.

    Me!txtsomething.requery.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refresh switchboard (2003)

    Hi John, this doesn't work.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: refresh switchboard (2003)

    That identical syntax always works for me in that situation.
    I just tried it again.

    did you replace txtsomething with the name of the textbox?
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: refresh switchboard (2003)

    Since the text box is calculated (its control source is a formula), you need the Recalc method of the form to update it, not a Refresh or Requery. If Me.Recalc in the On Activate event of the switchboard form doesn't work, try forcing a recalculation from the After Update event of the other form:

    Private Sub Form_AfterUpdate()
    Forms![NameOfSwitchboardForm].Recalc
    End Sub

    where NameOfSwitchboardForm must be replaced with the actual name of the switchboard form.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refresh switchboard (2003)

    Hi Hans, that works well....the text box recalulates as it's suppose to. However, the IF statement doesn't update the OptionLabel5.ForeColor or text. Perhaps the code I am using from the After Update event in the other form is incorrect:

    If Forms![Switchboard].[Text154] <> 0 Then
    Let Forms![Switchboard].[OptionLabel5].ForeColor = 255
    Let Forms![Switchboard].[OptionLabel5].Caption = Val(Text154) & " HR tasks to complete"
    End If

    Val(Text154) is the calculated text box which recalculates OK.

    Regards, Van

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: refresh switchboard (2003)

    If you mean the same Text154 both times, you should use

    If Forms!Switchboard!Text154 <> 0 Then
    Forms!Switchboard!OptionLabel5.ForeColor = 255
    Forms!Switchboard!OptionLabel5.Caption = Val(Forms!Switchboard!Text154) & " HR tasks to complete"
    End If

    There's no need to use Let here, and the second occurrence of Text154 now also refers to the Switchboard form.

  8. #8
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: refresh switchboard (2003)

    Thanks so much Hans...that works gr8!

    Regards, Van

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •