Date a specific record in Microsoft Access

65 pts.
Tags:
Access Database
Access Records
Microsoft Access
I have a database where I would like to specifically date a record when a value/change is made in one of the fields of the record. Specifically, my database has addresses for donation solicitation, and I wish to date a donation made by a receipient at a particular address. So, basically, if I receive a donation from Address "A" today, it tags the record with today's date. When I receive a donation from Address "B" tomorrow, tomorrow's date gets tagged to the record for address "B", while the date for the donation made from address "A" remains unchanged.
ASKED: March 10, 2009  1:38 PM
UPDATED: April 9, 2009  1:49 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Okay. I’m a bit confused.

One user said it can’t be done, and another gave me the way to do it.

Assuming that User Name DPaxton is correct, and I can date a donation, how do I enter the code shown (ME!dateField=Now). Better yet, where do I enter this code? If you can’t already tell, I’m quite the novice at Access.

I think it comes down to whether you are updating the data from a form or directly from the table. It is better to use a form rather than letting users update the table directly. From the form, you have more control and less chance of someone inadvertlently messing up the info. So you need to set up a form that allows the user to select the correct record, then when the new data is input and accepted, not only does the data get updated, but have the form update the receipt date field also.

In the definition of the table the “DataField” must be defined as “Date”.

In a form that is used to update the donation an event is created. It can be either “AfterUpdate” of “AfterChange” or “OnExit”. You would enter the following code “Me!DateField = Now()”. This is done in “DesignView”. Click on the field that is going to be updated and then in “properties” find the “EventType” you want to do the date updating (“AfterUpdate” of “AfterChange” or “OnExit”.) then click on on the down arrow. Then click on [Event Procedure]. Next click on the box next to the down arrow (it has … in it). After that it will open “Microsoft Visual Basic” and place you where the code will need to be endered. After entering “Me!DateField = Now()” (without the “‘s) close the “Microsoft Visual Basic” .

Using after “AfterUpdate” or “AfterChange” each time the donation field (on the form) is updated or changed and you exit the field your date field will contain today’s date. If you use the “OnExit” as your event then each time you pass through the donation field (on the form) the date field would be changed.

Discuss This Question: 11  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Sfd410
    Thanks for responding to my question. However, I don't think that this will work. Don't I need something in the "date" column that automatically reads the "donate" column, and when a value is put into the "donate" column it automatically dates the entry in the "date" column?
    65 pointsBadges:
    report
  • Dwaltr
    You are looking for a database trigger which Access doesn't support. Are you using a form to enter the donate value? If so, you can add an before update event that would update a field tied to your date column. If you want, you can set the date field property Visible = False so that it doesn't show on the form.
    900 pointsBadges:
    report
  • DPaxton
    How do you equate the receipt with the database record? One way would be using a form to update the record. Use an event for the address field (use the on exit or change event). In the code enter "ME!dateField = Now". This will put the date in the date field each time you exit the address field on a record.
    180 pointsBadges:
    report
  • SbElectric
    All good suggestions already been made. The only comments I may add is that – you may want to have the flexibility of inputting the date. Sometimes you may not be able to enter the date the donation was made (the system may be unavailable or you may decide to batch a week’s worth of donation & then enter at one setting). My recommendation is to add a new column in the Table – DtDonationMade. In the Table design mode set Data Type – Date/Time; Format – Short Date; Default - Now(); Required – Yes; Access 2007 provides Date Picker option. This is a handy option to display the monthly calendar where you can click on the desired date. Hope this makes sense & let us know via this forum how you finalized the project. Good luck.
    2,540 pointsBadges:
    report
  • DPaxton
    In form design use a event for the date field to be updated. Use either the "on exit: or "on change" event. This is where the code would be placed.
    180 pointsBadges:
    report
  • Sfd410
    Ok. I've done that but to no avail. I must be doing something wrong. I opened the form in design view, went to the "donate" box, chose "Properties", and entered the required information under "Event", "On Change". In order for the datafield to recognize that I entered a donated value in another field, I had to go to "Properties", "Data", and next to "Control Source", I selected "Donated" which is the field where I enter a donation for a specific record. In doing that, I do get a date in the Date Field, but it is incorrect and seems to vary by the amount shown in the "Donated" field when I enter an arbitrary $ amount. What am I doing wrong?
    65 pointsBadges:
    report
  • DPaxton
    In the Properties for the date field what "Format" is specified. If you want just the date and not date & time is should be "Short Date".
    180 pointsBadges:
    report
  • Sfd410
    Ok. I’ve done that but I still get no date to appear when I make changes to the record (i.e. add an amount to the "donate" field. Do I need to change the “Control Source” from my "Date" field to my "Donated" field? I've tried that, and when I do enter an amount in the "Donate" field, the date that appears in the corresponding box is not the current date, and changes when the dollar amount changes. Should the "Control Source" for my "Date" field be changed back or stay on the "Donated" option? I'm really at wits end with this program.
    65 pointsBadges:
    report
  • Randym
    You need the control source of the donate field to that of the donate field on the table. The control source of the date field needs to be set to that of the date field of the table. Then make an After update event on the donate field that simple does: me!datefield = NOW(). P.S. the fomat set in the form suggested by DPaxton for the date does not control what actually is stored in the date field in the table. So if you do not want time in the date, then you must do: me!datefield = cdate(format(now(),"mm/dd/yy")). This will strip the time off so that you can filter or query on just date later.
    1,740 pointsBadges:
    report
  • Sfd410
    Ok....Sorry that I've taken so long to write back regarding this lastest advice. I now get an error message (after I make changes to the record and try to move to the next record) that says: Microsoft Office Access can't find the macro 'ME!datefield=NOW()' The macro (or it's macro group) doesn't exist, or the macro is new and hasn't been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under. Any ideas on what to do next?
    65 pointsBadges:
    report
  • Randym
    Datefield is just an example. Unless your control on the form is called datefield, you need to replace "datefield" with the real name of your control on the form. This has to be a control on the form and bound to the table. To avoid confusion, I suggest that the name of the control on the form be the same as the column in the table that it is bound to.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following