Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    ComboBox (log changes) (2002 SP-2)

    Might it be possible to have a cmbbx "record" the date/time of a OnChange event, and "log" that change in accordance with the particular selection? A portion of my frmOrderDetail (shown below) has ComboOrderStatus with 10 selections (6 of which are date/time critical). Below the cmbbx are the txtbx's used to manually record the Order Status changes (date/time). The problem that occurs is, in a rush, the status is changed, but the date/time change is not updated.

    Perhaps there is another way to deal with this altogether; I'm open to any suggestions.
    Any input greatly appreciated.
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: ComboBox (log changes) (2002 SP-2)

    You could use the After Update event of the combo box, and use the value selected by the user to determine which text boxes should be set.

    I don't know whether the value of the combo box is a number (via a lookup table) or the string as displayed; you will have to adapt the code below according to the situation. And, of course, you must substitute the appropriate names for the text boxes.

    Private Sub ComboOrderStatus_AfterUpdate()
    Select Case Me.ComboOrderStatus
    Case 1 ' or "Assigned"
    Me.txtDateAssigned = Date
    Me.txtTimeAssigned = Time
    Case 2 ' or "Dispatched"
    Me.txtDateDispatched = Date
    Me.txtTimeDispatched = Time
    Case 3 ' or "InRoute"
    ...
    ...
    ...
    End Select
    End Sub

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    Hmm. . .

    I'm not getting any errors (no ErrHandler though), but nothing happens either - no date/time entries are made after selection (or close/open form). One thing I notice in the code is that the entry after "Case 1 ' " is in green (like a vba notation). That is true for all "Cases"

    Private Sub ComboOrderStatus_AfterUpdate()
    Select Case Me.ComboOrderStatus
    Case 1 ' <font color=448800>or "Assigned"</font color=448800>
    Me.AssignedDate = DATE
    Me.AssignedTime = TIME
    Case 2 ' or "BOL"
    Me.BOLDate = DATE
    Me.BOLTime = TIME
    Case 3 ' or "CANCELLED"
    Me.CancelledDate = DATE
    Me.CancelledTime = TIME
    Case 4 ' or "Delivered"
    Me.DeliveredDate = DATE
    Me.DeliveredTime = TIME
    Case 5 ' or "Dispatched"
    Me.DispatchedDate = DATE
    Me.DispatchedTime = TIME
    Case 6 ' or "InRoute"
    Me.InRouteDate = DATE
    Me.InRouteTime = TIME
    End Select
    End Sub

    [side issue] How the *&#* do you retain the VBA code formatting when you paste into the thread?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    Use the preformatted text controls [ PRE ] and [ /PRE ] to hold the formatting (don't include the spaces surrounding the PRE and /PRE). You can find this in 1-Click TagPanel in the response area.

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

    Re: ComboBox (log changes) (2002 SP-2)

    The apostrophe is used to indicate a comment, and comments are displayed in green, so when you write:
    Case 1 ' or "Assigned" the or "Assigned" is treated as a comment and ignored. Is that what you intend?

    What is the actual value stored in the comboOrderStatus? Is it a number : 1,2,3 etc or a word " Assigned", "BOL" etc.

    To preserve formatting of your VBA, enclose the code in pre /pre tags.
    Regards
    John



  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    Thanks Pat; if I had held the curser over it long enough I might have gotten a clue.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    John:

    I've never done a Select Case before , so I wasn't certain if Han's code was just showing a comment or part of the code.

    The combo stores the text ("Assigned", "Dispatched", etc.) I tried the same method (code) in a test db and couldn't get it to work either. Sure appreciate your input!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    The reason John asked if the wording was in the combo text was that you would then use the Case statements like:
    Case "Assigned"
    Case "Dispatched"
    and not Case 1 etc.

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

    Re: ComboBox (log changes) (2002 SP-2)

    Bryan

    If the combo stores the actual words, try this:
    <pre>Private Sub ComboOrderStatus_AfterUpdate()
    Select Case Me.ComboOrderStatus
    Case "Assigned"
    Me.AssignedDate = DATE
    Me.AssignedTime = TIME
    Case "BOL"
    Me.BOLDate = DATE
    Me.BOLTime = TIME
    Case "CANCELLED"
    Me.CancelledDate = DATE
    Me.CancelledTime = TIME
    Case "Delivered"
    Me.DeliveredDate = DATE
    Me.DeliveredTime = TIME
    Case "Dispatched"
    Me.DispatchedDate = DATE
    Me.DispatchedTime = TIME
    Case "InRoute"
    Me.InRouteDate = DATE
    Me.InRouteTime = TIME
    End Select
    End Sub
    </pre>


    I would also check that you are correctly referring to the actual names of the controls .

    John
    Regards
    John



  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    Pat:

    Thanks buddy! You found my error. The corrected code (for anyone else who might be following) is:

    <pre>Private Sub ComboOrderStatus_AfterUpdate()
    Select Case Me.ComboOrderStatus
    Case "Assigned"
    Me.AssignedDate = DATE
    Me.AssignedTime = TIME
    Case "BOL"
    Me.BOLDate = DATE
    Me.BOLTime = TIME
    Case "CANCELLED"
    Me.CancelledDate = DATE
    Me.CancelledTime = TIME
    Case "Delivered"
    Me.DeliveredDate = DATE
    Me.DeliveredTime = TIME
    Case "Dispatched"
    Me.DispatchedDate = DATE
    Me.DispatchedTime = TIME
    Case "In Route"
    Me.InRouteDate = DATE
    Me.InRouteTime = TIME
    End Select
    End Sub</pre>


    This works perfectly. Thanks Pat (and Hans as well)
    [Edit] Ooops! Looks like our "corrected" code crossed over at the same time (some redundancy is harmless)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    The code works perfectly (thanks one and all!) Except that I neglected to make it "user" proof (which in my case is essential). This morning I went to update the status of an order and made the wrong selection in the CmbBx, thereby changing the the date and time entry for a status that had already been recorded (and obliterating the previously recorded info). Might there be some way of amending the code to look for a (IsNot = Null?) entry before the AfterUpdate event fires? Could this amendment be to the entire event, or must it be for each individual case statement? As always, all input greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ComboBox (log changes) (2002 SP-2)

    Are you talking about checking the combobox to see if it's null? If so, just add a conditional wrapper:

    If Len(Me.[ComboOrderStatus] & vbnullstring) > 0 Then
    Select Case ...
    End If
    Charlotte

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    Charlotte:

    Thanks for the reply. Actually, I need to see if the "= DATE" and "=TIME" has an entry.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: ComboBox (log changes) (2002 SP-2)

    You could use the before update event to check whether you have used the code before.
    <pre>Private Sub ComboorderStatus_BeforeUpdate(Cancel As Integer)
    Dim blCancel As Boolean
    Select Case Me.ComboorderStatus
    Case "Assigned"
    If Not IsNull(Me.assignedDate) Then
    blCancel = True
    End If
    Case "BOL"
    If Not IsNull(Me.BolDate) Then
    blCancel = True
    End If
    Case "CANCELLED"
    If Not IsNull(Me.CancelledDate) Then
    blCancel = True
    End If
    Case "Delivered"
    If Not IsNull(Me.DeliveredDate) Then
    blCancel = True
    End If
    Case "Dispatched"
    If Not IsNull(Me.DispatchedDate) Then
    blCancel = True
    End If
    Case "InRoute"
    If Not IsNull(Me.InRouteDate) Then
    blCancel = True
    End If
    End Select
    If blCancel = True Then
    MsgBox ("This Status has been used before. Press Esc to return to previous value")
    Cancel = True
    End If
    End Sub
    </pre>


    If you set Cancel = true in a before update event, the focus stays on the control, and the after update event does not happen.
    Regards
    John



  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: ComboBox (log changes) (2002 SP-2)

    John:
    Your solution worked perfectly! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    What took me so long to get back to you was that I did some major design changes as a result of your contribution. frmOrderDetail has always suffered from a lack of real estate. In reality there are a total of fourteen different status' that the order can take. Without this ability to automate the logging of status changes, I always had to display the date/time entries to get a "visual" that the log entries had, in fact, been made. That many txtbx's (28 in all) on any form takes up a lot of real estate! So, I only displayed six of them. Now, not having to worry about whether the event has been logged, I don't even need to display the log - valuable land recovered! I created a separate frmStatusLog with a CmdBtn next to the CmbBox to call the log form. It's great! Now I have the room I've always wanted for the existing (public) memo field (used to be really tiny) and a new (private) memo field that I have suffered without - all this and I still have room to spare.

    This is a tremendous improvement to the whole app. Thanks again!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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
  •