Add a date when a change is made to an option group on a subform

[zdjbel]

I have a subform with a 12 button option group, each button denoting a stage in a work process. The table to which the subform it is bound includes a DateLastUpdated field. I would like the system to insert the date into the DateLastUpdated field whenever a user changes the status in the work process, i.e. changes the selection in the option group. It would also be nice if I can pull the user's name from the system/network and store it in a seperate field, e.g. UpdatedBy. How can I do this?

Thanks!

[Dal Jeanis]

1) Generally, I like to get the user's ID when the user logs on, and store it in either a hidden form, a global variable, or a temp variable (depending on Access version) for just such an occasion.

Here's a thread with some code on how to get information about your user and his/her machine. https://www.accessforums.net/access/...ers-36612.html


2) Language is tricky around here. I believe you want to update the existing table record by changing two fields to have the two new values - the "last updated" and the "updated by" fields. The word "Insert" will get you instructions on how to add another record to the same table, which may not be the result you really want.

The general syntax for updating a set of records on a table is

UPDATE MyTable
SET Field1 = NewValue, Field2 = NewValue2
WHERE (Field3 = "X");

This will update all records on MyTable where Field3 = 'X".

Now, you have to make sure that your WHERE clause uniquely defines the records that should be updated. Presumably you have a key field, and you'd use that to identify the record.


You can generally use the Now() or Date() functions to get the current date/time for your timestamp.


If you prefer macros, I don't know how you'd get the user ID, but you'd use something like the instructions on this page to update the current record with your timestamp and your user id. http://answers.microsoft.com/en-us/o...0-5bb268ad14a2

[zdjbel]

1) Generally, I like to get the user's ID when the user logs on, and store it in either a hidden form, a global variable, or a temp variable (depending on Access version) for just such an occasion.

Here's a thread with some code on how to get information about your user and his/her machine. https://www.accessforums.net/access/...ers-36612.html


2) Language is tricky around here. I believe you want to update the existing table record by changing two fields to have the two new values - the "last updated" and the "updated by" fields. The word "Insert" will get you instructions on how to add another record to the same table, which may not be the result you really want.

The general syntax for updating a set of records on a table is

UPDATE MyTable
SET Field1 = NewValue, Field2 = NewValue2
WHERE (Field3 = "X");

This will update all records on MyTable where Field3 = 'X".

Now, you have to make sure that your WHERE clause uniquely defines the records that should be updated. Presumably you have a key field, and you'd use that to identify the record.


You can generally use the Now() or Date() functions to get the current date/time for your timestamp.


If you prefer macros, I don't know how you'd get the user ID, but you'd use something like the instructions on this page to update the current record with your timestamp and your user id. http://answers.microsoft.com/en-us/o...0-5bb268ad14a2

Thanks very much Dal. The solution with the macro worked just fine, I wasn't aware that macros can be used in tables. As for getting and storing the user who updated the data, the link offers a solution for split databases, or so I believe. Splitting my database is in my plans once I'm satisfied with what it does. In the meantime, I'll keep searching for a solution more suited to my database and skill level, which is not very high.

Thank you again, I am grateful and appreciate your taking time to help me out.

Zoran

[Dal Jeanis]

I don't believe there is a difference between the method you'd use to log the user on a split versus a combined database. The only difference between split and unsplit is whether the table being update is directly in the database or linked.

You need to
1) establish a field in each table that needs tracked.
2) identify the user who is using the database.
3) update the field in the table with the user ID.

No difference in the code or macro.

[zdjbel]

I don't believe there is a difference between the method you'd use to log the user on a split versus a combined database. The only difference between split and unsplit is whether the table being update is directly in the database or linked.

You need to
1) establish a field in each table that needs tracked.
2) identify the user who is using the database.
3) update the field in the table with the user ID.

No difference in the code or macro.

Hi Dal! Thanks very much for the guidance, it I'll try to work this out and let you know how it came out.

Cheers!

[Dal Jeanis]

Great! If you got what you need, please mark the thread solved. Top of page, under thread tools.

Feel free to open a new thread with any new questions that come up.