1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 Record Date When Field Updated

Discussion in 'Business Applications' started by Wayne0928, Mar 13, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    I've searched the forum and can't find the help I need...perhaps I'm not using the right keywords.

    Anyway, I want to automatically have a time stamp entered in a field named "CancelDate" if a yes/no field named "Cancel?" is checked. On the form I use an action button for that field.

    I'm new with VBA, but after some research I entered this code in the AfterUpdate line in Properties:

    Private Sub CancelDate_AfterUpdate()
    If Me.txtCancel?.value = 1 Then Me.txtCancelDate = Now()
    End If
    End Sub

    ...and it doesn't work. It seems to hang up because of the question mark. I get a Compile Error: Expected:Then or GoTo.

    Can anyone help me?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,683
    Try
    If Me.[txtCancel?] = -1 Then Me.txtCancelDate = Now()

    A yes in a check box field is -1 and unchecked when it has been checked is 0. You can also use Date() instead of now() as it will return the date rather than the date & time.
     
  3. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    Thanks, OBP. Although it still doesn't work. I'm obviously missing something. I tried to attach my test file but I got a security warning. I wrote admin about it. Any other ideas while I'm waiting for a reply from admin?
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,683
    Well one thing that I notice is where the code is, is it in the CancelDate or Cancel field, I thought the Cancel field was the check box, which is where the code should be?
    Otherwise it is updating itself.
    What error do you get with my code?

    You can email the database if you like, the database has to be zipped to attach it to the forum.
     
  5. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    I switched the code to the checkbox field "Cancel?" and the line spacing and now I don't get any error messages, but it still doesn't enter the date. I zipped the file and attached it. The data is fake, so there's no confidence breach. If someone could look at it, it would be fantastic!
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,683
    Two simple errors, your cancel check box is actually called "Cancel" not "txtcancel" and your cancel date field is actually called "CancelDate" not "TxtCanceldate".
    So this works

    If Me.[Cancel?] = -1 Then

    Me.[CancelDate] = Now()

    End If

    If you are not going to any other operations in the code it can be shortened to

    If Me.[Cancel?] = -1 Then Me.[CancelDate] = Now()

    Can I suggest that you think about using an Autonumber key field for the School Table and it's relationship to the attendee data table, although if you have a lot of records it will take a bit of work.
     
  7. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    OBP, thanks for your patience. I know I'm testing your signature about not giving up easily.

    I'm obviously missing something and don't know what it is. As I said, I'm new to using VBA, but everyone seems to say that in order to do what I want this form to do I should use it.

    Would you be so kind as to post back that file that you fixed so I can look at what you did?

    Oh, on the tip for auto-numbering, you're right, and there are a lot of records in the real thing, 1,000+. Someday when I know enough, I need to start over and completely redesign it.

    Thanks again.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,683
    Sure here it is.
     

    Attached Files:

  9. Wayne0928

    Wayne0928 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    38
    Thanks, OBP! Works like a charm (once I figured out that I needed to enable macros).
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1092878

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice