Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    push selected value to a text box (2000 all updates)

    On an unbound form, there are 42 unbound text boxes...made to simulate a calendar. The boxes are D0 through D41

    I want to flesh out the complete date, and push the value in the box selected to a text box called "txtSelected"

    Here is some of the code on the form.

    - - - - - - - - - - - -
    <code>Option Compare Database
    Option Explicit

    Dim selday As Integer

    Private Sub D0_Click()
    Me.txtSelected = Null
    Dim dteSel As Date
    Me.D0.SpecialEffect = 4
    Me.D0.FontBold = True
    Me.D0.ForeColor = vbRed
    selday = Me.D0.Value
    dteSel = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), selday)
    Me.txtSelected = dteSel
    End Sub

    Private Sub D0_LostFocus()
    Me.D0.SpecialEffect = 2
    Me.D0.FontBold = False
    Me.D0.ForeColor = vbBlack
    End Sub</code>
    - - - - - - - - - - - -

    I can enter all of that data on Click event and the LostFocus events for each of the respective text boxes...but that is terribly cumbersome...and I know there has to be a routine method for accomplishing the same thing.

    I would appreciate a push in the right direction.

    Thanks.

    Tom

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: push selected value to a text box (2000 all updates)

    You need to create a couple of functions that you call from the appropriate event of each control. For example, you might have this function:

    <pre>Private function StandardClick(ctl as control)
    Dim dteSel As Date
    Me.txtSelected = Null
    ctl.SpecialEffect = 4
    ctl.FontBold = True
    ctl.ForeColor = vbRed
    selday = ctl.Value
    dteSel = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), selday)
    Me.txtSelected = dteSel
    End function</pre>



    In the OnClick Property (not in code) of each of those 41 text controls, you have this : =StandardClick(ActiveControl)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: push selected value to a text box (2000 all updates)

    Thanks, Mark

    The code runs but when you attempt to move from one control to another, a RunTime error 94 occurs "invalid use of Null"
    The code you supplied errors out on the line
    selday = ctl.value

    We're getting close, but not quite.

    Tom

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: push selected value to a text box (2000 all updates)

    You just need some code in the beginning of the function to test for a Null value, and immediately exit the function if found.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: push selected value to a text box (2000 all updates)

    Well, I don't know..

    I added the following to the beginning of the code you supplied
    If selday = 0 Then
    Exit Function
    End If

    and I still get the same result. The problem seems to be when you move from one control to another.

    I also tried "If IsNull(selday) Then"
    And "If selday = Null" Then

    There is another function in the code
    <pre>Private Function filldates()
    Me.FirstDate.SetFocus
    Dim curday As Variant, curbox As Integer
    curday = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), 1)
    curday = DateAdd("d", 1 - Weekday(curday), curday)
    For curbox = 0 To 41
    Me("D" & curbox) = Day(curday)
    Me("D" & curbox).Visible = False
    If Month(curday) = Month(Me![FirstDate]) Then Me("D" & curbox).Visible = True
    curday = curday + 1
    Next curbox
    End Function</pre>


    But I don't see that getting in the way.

    Tom

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

    Re: push selected value to a text box (2000 all updates)

    Try adding the following line at the beginning of the function:

    If IsNull(ctl) Then Exit Sub

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: push selected value to a text box (2000 all updates)

    Yep, that works.

    My only hurdle remains this...
    When I click on a date it no longer turns red...it goes blank. The correct value is fed to the text box, but now the control is blank.
    That started to happen since I added the function suggested by Mark Liquorman

    When I click on the control, I want it to turn red...and when I leave it I want it to return to black.

    That's why, formerly, I had tried this code on the LostFocus event for controls.
    Me.D0.SpecialEffect = 2
    Me.D0.FontBold = False
    Me.D0.ForeColor = vbBlack


    Any suggestions?

    Thanks.

    Tom

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

    Re: push selected value to a text box (2000 all updates)

    You could put the code to change the color in the On Enter and On Exit events of the text boxes, and the code to push the value to a text box in the On Click event.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: push selected value to a text box (2000 all updates)

    Nope, that doesn't work either. In fact, I had already tried code on the OnExit event rather than on the LostFocus event.

    This works on the Click event for each box...
    <pre>Me.txtSelected = Null
    Dim dteSel As Date
    Me.D0.SpecialEffect = 4
    Me.D0.FontBold = True
    Me.D0.ForeColor = vbRed
    selday = Me.D0.Value
    dteSel = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), selday)
    Me.txtSelected = dteSel</pre>


    And on the LostFocus event for each box...
    <pre>Me.D0.SpecialEffect = 2
    Me.D0.FontBold = False
    Me.D0.ForeColor = vbBlack</pre>


    But it seems rather unprogrammatic, and laborious, to put both these pieces of code on each of the 41 boxes. That's why I went searching for a function.
    And Mark's function does half the battle.

    However, nothing else seems to accomplish everything.

    Tom

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

    Re: push selected value to a text box (2000 all updates)

    You could use two functions:

    Private Function StandardClick()
    If IsNull(ActiveControl) Then Exit Function
    ActiveControl.SpecialEffect = 4
    ActiveControl.FontBold = True
    ActiveControl.ForeColor = vbRed
    Me.txtSelected = DateSerial(Year(Me![FirstDate]), Month(Me![FirstDate]), ActiveControl.Value)
    End Function

    and

    Private Function StandardLostFocus()
    ActiveControl.SpecialEffect = 2
    ActiveControl.FontBold = False
    ActiveControl.ForeColor = vbBlack
    End Function

    Select D0 ... D41.
    In the On Click event, enter =StandardClick(), and in the On Lost Focus event, enter =StandardLostFocus()

  11. #11
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: push selected value to a text box (2000 all updates)

    Well, that certainly makes sense to me too. And I had tried something similar before.

    Trouble is, it doesn't work.

    When you click on the control, the correct value goes into the date box. The colour does not turn red, but disappears. And losing focus does not bring it back to black.

    Interesting.

    Tom

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

    Re: push selected value to a text box (2000 all updates)

    I don't think I can solve this without seeing the database.

    But why don't you use the calendar control that comes with Access (see <post:=716,717>post 716,717</post:> for an example), or the popup calendar from <post:=254,241>post 254,241</post:>?

  13. #13
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: push selected value to a text box (2000 all updates)

    Hans
    I have used the Microsoft built-in ActiveX calendar control in the past without a lot of hassles, but as you know it does have idiosyncracies.

    There are 2 computers on which this database will currently be used.
    On one of them, it seems impossible to set the calendar to display properly. No matter how we tweak the settings, it displays Saturday-Friday....whereas we want Sunday-Saturday.
    Therefore, I decided to take a run at making one myself.

    I will send this database. The only thing in it is the calendar I worked out. However, I don't know whether or not you will be able to open it. The only file compression software I have is .rar and this site won't take that...so I changed the file extension to .jpg in the hopes that changing it back to .rar would work.

    Tom

  14. #14
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: push selected value to a text box (2000 all updates)

    I forgot I had 7-Zip
    So just in case the .rar won't open, here's a zip one.

    Tom
    Attached Files Attached Files

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

    Re: push selected value to a text box (2000 all updates)

    I don't know what you did, but the proposed code works for me - see the attached version.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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