Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Auto field update (Access 2000)

    I have a query and form that display the following pair of fields on an order table.

    Payment status (1 = awaiting payment, 2 = paid)
    Payment date/time

    I wish to update the the payment date/time field automatically with the current date/time when the payment status field is updated to 2 (paid) on the form. Is this possible?

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

    Re: Auto field update (Access 2000)

    You can use the After Update event of the Payment status control on the form:

    Private Sub Payment_status_AfterUpdate()
    If Me.[Payment status] = 2 Then
    Me.[Payment date/time] = Now
    End If
    End Sub

    This assumes that the names are exactly as mentioned by you. If not, adjust accordingly.

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto field update (Access 2000)

    Just a thought.
    Might not the Payment Status field be redundant?
    If you have a payment date then the status must be paid surely - no date present then must be awaiting payment.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Auto field update (Access 2000)

    Thanks for the suggestion. I haven't used events before: does this go in Form Properties > Event > After Update > Expression Builder? Also, can it be refined so that the date is updated only if it hasn't previously been updated (i.e. it is currently blank)?

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Auto field update (Access 2000)

    There are actually more than two payment statuses; I only mentioned the significant two in the example for simplicity.

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

    Re: Auto field update (Access 2000)

    Select the text box on the form (in design view).
    Activate the Event tab of the Properties window.
    Click in the After Update box.
    Select [Event Procedure] from the dropdown list.
    Click the ... button to the right of the dropdown arrow.
    This will create the first and last lines of the event procedure:
    <code>
    Private Sub Payment_status_AfterUpdate()

    End Sub
    </code>
    Type or paste the code in between, so that it looks like this:
    <code>
    Private Sub Payment_status_AfterUpdate()
    If Me.[Payment status] = 2 And IsNull(Me.[Payment date/time]) Then
    Me.[Payment date/time] = Now
    End If
    End Sub
    </code>
    (I have added the condition that the Payment date/time is blank.)

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

    Re: Auto field update (Access 2000)

    Hans code could be changed to:
    <pre>Private Sub Payment_status_AfterUpdate()
    If Me.[Payment status] = 2 Then
    If IsNull(Me.[Payment date/time]) then
    Me.[Payment date/time] = Now
    End If
    End If
    End Sub
    </pre>


    nb. do you want the Date or Date and Time? If only the date is required then use Date instead of Now.

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Auto field update (Access 2000)

    OK, thanks for your help.

Posting Permissions

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