Time Stamp Not Working

[UNC_Access]

Hi!

Here is my issue:

1. My goal is to have a time stamp which will update if a record in a table is modified or created.

2. I want that time stamp to show up on the user form (but not for editing, just for vieweing).

3. I tried to follow these instructions (using Access 2010):

http://office.microsoft.com/en-us/a...me-when-a-record-is-modified-HA010342039.aspx
Summary:

a. Created "Last Update" field in a table (Table1)
b. Created SetValue Macro (called Macro1)
c. Opened the table's Form and went to the Form's properties (Selection Type: Form)
d. Under the Event section, I added Macro1 to the "Before Update" property


4. No date/time appears, and I get the following error message if I try to move on to another record (or even close out of the form):

The object doesn't contain the Automation object 'Table1'
You tried to run a Visual Basic procedure to set a property or method for an object. However, that component doesn't make the property or method available for Automation operations. Check the component's documentation for information on the properties and methods it makes available for automation operations.

How can I fix this so that my form will tell me the date of last update, and will refresh if any data in the record is modified?

Thanks!

 

[Beetle]

If you're using A2010 then the simplest way track record modifications is with the Before Change of the table itself. This way, a modification date and time can be stored any time a record is modified, no matter where it's modified from (i.e. any query or form that's based on that table, or even within the tabel itself). This way you only have to create the macro code in one place to handle all possible scenarios. You've already added a field to store the value (I assume it's a Date/Time field), so now all you need is a couple of simple lines in the Before Change event of the table;

 

[UNC_Access]

Beetle, I tried just that (created a macro in my table - before change - and saved it) and my Last Update field is not changing still (the field is blank no matter what, even if I modify a field in the record. Not sure where to go. What information can I provide so I can get help?

 

[John Big Booty]

This is stuff that really should be happening at Form level rather than Table level.

 

[John Big Booty]

Tables are for storing your data and your user should have no direct access to the tables or the data they hold. All interactions with the tables and the data therein should happen through the "filter" of a Form, in this way you can control how the user views and interacts with that data.

 

[Beetle]

@UNC_Access

Can you describe in more detail exactly what you did or can you post a sanitized copy of your app?

@JBB

Agreed, and I wasn't suggesting that he give the users access to the table, just that putting the logic at the table level may be a bit simpler for something like this. With the new data macros in A2010 you can do something like this at the table level and ensure that the field you want gets updated no matter how the record gets modified. Not that you would let users modify directly in the table, but if you had some scenario where, say , you have more than one form that could be used to update records in that table, you wouldn't have to worry about making sure that you have the proper code each form.

 

[UNC_Access]

Beetle:

Yes, I will try to post some more information (and maybe a cleaned-up version of the Access file) later on.

Thanks so much!

 

[UNC_Access]

I attached the file I am working on.

Check out these three forms (in the Forms section):

frm_Overpayments
frm_Repayments
frm_Write_Offs

The tables linked to them are under the Key Tables section:

tbl_Overpayments
tbl_Repayments
tbl_Write_Offs

Basically, I want this field to auto-update if any modifications are made to the record:

Also, I don't want the user to be able to modify the date (I don't even want the user to be able to select the text box).


Also, I had an unrealated issue (if you don't mind helping):

On the frm_Overpayments form, check out my search box - any ideas on how I can get the listing in chronological order? (i.e. 1111, 1112, 2222, 3333, 4444)

Any other suggestions on how I can improve this file are definitely welcome!

Thanks!

 

[Beetle]

I'll have to look at it on Monday when I'm back in the office. I don't have A2010 available right now.

 

[John Big Booty]

The way I would do this is to have the following code in Each of the Form's After Update events;

Me.Last_Update = Now()

 

[UNC_Access]

John,

I tried what you suggested, but I got the following error:

Here is design view:

Do I have to put "Last_Update" in brackets, like this:

Me.[tbl_Overpayments]![Last Update] = Now()

?

Thanks!

 

[John Big Booty]

You will need to select Event Procedure from the drop down list and then click the ellipsis button (...) to the right;

This will open the code window, that is where you need to put the code I provided earlier;

 

[UNC_Access]

Thanks John! I am starting to get a feel for how this works.

I still have one problem - the form/table will not save.

I did exactly what you instructed, but when I modify the record (maybe change the first name, for example), I can't leave the record, and it won't save.

Any suggestions?

Thanks!

 

[John Big Booty]

OK, I see what you mean, use the Before Update (same code) event and get rid of the After Update event.

 

[UNC_Access]

Works perfectly now! Thank you so much!

On a separate note, do you have any recommendations on how I can reorder the combo box info in ascending order?:

 

[John Big Booty]

With the form in Design view select the combo, now go to the Data tab, click in the Row Source, now click the ellipsis button which will open the Row Source as a query in design view. Select Ascending from the drop down list in the Sort row.

Close the query design window and save design changes to the Row Source

 

[UNC_Access]

Perfect! Thanks so much!

I have one final question and I will be done with this database.

On the Repayments form, I would like to create a way for a user to:

1. First search the repayments table by the OP Number (for example: 1111) using a combo box
2. Then, using a second combo box, select a specific payment ID

My goal is that the second combo box will only show ID numbers associated with the OP Number

For example:

OP Number 1111 made 3 repayments:

ID OP Amount
1 1111 $100
2 1111 $50
3 1111 $25

Assuming that 1111 is selected in the first combo box, I want the second combo box to show ONLY the three associated IDs:

1
2
3

Is this possible?

 

[John Big Booty]

Have a look at a Cascading Combo box set.

 

[UNC_Access]

Will Do! Thanks!