Results 1 to 8 of 8
  1. #1
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66

    Red face Add date OnChange Event - A to Z Walk Through

    I really want to learn everything I can. I am hoping for someone to guide me through this, and I'll see what I can do. I would consider myself a pre-school VBA coder. I would like someone with a little patience to walk me through this. I am probably going to be asking a few questions since I do not understand everything fully.

    My Table is DATA BANK
    My Form name is TRACER
    My field is STATUS
    The STATUS field is a combo box. I have about 15 STATUS values.
    A few of my status values are "NEW", "SEEKING", and "CLOSED".


    I have other fields on my TRACER form "DATE TIME NEW", DATE TIME SEEKING", and "DATE TIME "CLOSED"

    The goal is to have the DATE TIME fields populate Onchange. This will be a difficult one for me. My first "real test" with VBA.

    I believe this will require a set of if statements.

    Below is the opening code. Anyone care to walk me through this?

    Code:
    Private Sub Combo1132_Change()
    
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I assume you're looking for this type of thing:

    Code:
    Select Case Me.Status
      Case "New"
        Me.DateTimeNew = Now()
      Case "Closed"
        Me.DateTimeClosed = Now()
    End Select
    The spaces in your names aren't worth the bother in the long run. I'd use the after update event rather than the change event.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    Alright. How does this look.

    Code:
    Select Case Me.Status
      Case "NEW"
        Me.DATETIMENEW = Now()
      Case "SEEKING"
        Me.DATETIMESEEKING = Now()
      Case "TRACED"
        Me.DATETIMETRACED = Now()
      Case "CONTACTED"
        Me.DATETIMECONTACTED = Now()
      Case "CONTACTED"
        Me.DATETIMECONTACTED = Now()
      Case "DEAD END"
        Me.DATETIMEDEADEND = Now()
      Case "AGREEMENT SENT"
        Me.DATETIMEAGREEMENTSENT = Now()
      Case "AGREEMENT RECEIVED"
        Me.DATETIMEAGREEMENTRECEIVED = Now()
      Case "DOCUMENTS REQUESTED"
        Me.DATETIMEDOCUMENTSREQUESTED = Now()
      Case "DOCUMENTS RECEIVED"
        Me.DATETIMEDOCUMENTSRECEIVED = Now()
      Case "SUBMITTED TO HOLDER"
        Me.DATETIMESUBMITTEDTOHOLDER = Now()
      Case "PENDING PAYMENT"
        Me.DATETIMEPENDINGPAYMENT = Now()
      Case "PAYMENT STATUS SENT"
        Me.DATETIMEPAYMENTSTATUSSENT = Now()
      Case "PAYMENT STATUS RECEIVED"
        Me.DATETIMEPAYMENTSTATUSRECEIVED = Now()
      Case "CLOSED"
        Me.DATETIMECLOSED = Now()
    
    End Select
    So the case is somewhat like the if statement? Interesting. Just trying to figure it all out. Why did you decide to use case instead of if?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It is somewhat similar to If, yes. You would have used a bunch of ElseIf statements in an If/Then block, which also would have worked. It may just be a style thing, but I tend to use Case when there are more than a couple of options. It feels easier to add options to later, and the test seems more flexible.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    I told you I was going to need a little help. I received a compile error.

    Case "NEW"
    Me.DATETIMENEW = Now()

    Should I add me.FORM.DATETIMENEW = Now()

    I told ya I was pre-school

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The error implies that DATETIMENEW is not the name of a field or control on the form. Is it? If your names still have the spaces, you need to include them. When you type "Me." you should be presented with a list to choose from.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    I modified the names to the current format. I will make sure the fields are formatted properly and report back with any troubles. Thanks for helping me out so far...

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you don't sort it out, post the db here and we'll have a look.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date event e-mail
    By Cran29 in forum Access
    Replies: 5
    Last Post: 06-21-2011, 03:31 PM
  2. Help on an event...
    By allykid in forum Forms
    Replies: 4
    Last Post: 03-15-2011, 11:25 AM
  3. Replies: 1
    Last Post: 06-22-2010, 03:15 PM
  4. Event procedures
    By GIS_Guy in forum Forms
    Replies: 1
    Last Post: 05-11-2010, 02:34 PM
  5. Replies: 21
    Last Post: 06-03-2009, 05:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums