Capture Table Name

[Guest]

My goal is a report that says something like this:

“User JSmith edited record 333 in tblContracts on 3/2/05 at 11:28
record 567 in tblBuyers on 3/2/05 at 11:29
record 567 in tblLots on 3/2/05 at 11:30
[Etc, etc, for all edits by this user]
No further edits by UserJSmith during the time period specified.â€

I am running on a LAN, Windows server ‘03, Access versions ‘00 and ‘03. I
know how to capture username (thanks to this board) and date and time but I
don’t see any way to capture the table name. The form name won’t due because
the form will contain fields from various tables and the record number won’t
due because (see example) the same nbr appears in several tables.

Other than adding code to every field on every form I don’t see how to do it.

Is there a way?

 

[Guest]

The record numbers will be totally irrelevant and would never lead you back
to the record the user changed. Forget record numbers. The are only valid
for the current recordset and even then if the order or filter changes, so do
they. Your best bet would be sure all tables have an auto number field. You
can include those fields in your form's queries and bind them to hidden
controls on your form. Now, the trick is, you will have to know which table
each control on the form gets its data from. So you will know which table to
identify in your table you are building for the report. One possibility is
to use each control's Tag property to identify the table.

So, in the After Update event of the form you can loop through your form's
control collection, compare its value to its OldValue propetry and if they
are not equal, look at the Tag property to see which table it belongs to,
then look in the hidden control for that tables Autonumber field and use this
info to update your report table.

 

[Guest]

Well, 1st by record number i mean the autonumber in the table. In my mind
that IS the ONLY record number, and always will be. It is unique and never
changes and the only way I know of to get a handle on a specfic record. U
seem to mean by record number those silly numbers that display on the status
bar. They are both meaningless and useless as far as i can tell.

That said, looking at the rest of ur answer it seems WAY above my pay grade.
I thought this was a common enough report that some fairly direct way existed
to produce it.

Guess not. Oh well, back to the laundry.
--
SailorMike

The record numbers will be totally irrelevant and would never lead you back
to the record the user changed. Forget record numbers. The are only valid
for the current recordset and even then if the order or filter changes, so do
they. Your best bet would be sure all tables have an auto number field. You
can include those fields in your form's queries and bind them to hidden
controls on your form. Now, the trick is, you will have to know which table
each control on the form gets its data from. So you will know which table to
identify in your table you are building for the report. One possibility is
to use each control's Tag property to identify the table.

So, in the After Update event of the form you can loop through your form's
control collection, compare its value to its OldValue propetry and if they
are not equal, look at the Tag property to see which table it belongs to,
then look in the hidden control for that tables Autonumber field and use this
info to update your report table.

My goal is a report that says something like this:

“User JSmith edited record 333 in tblContracts on 3/2/05 at 11:28
record 567 in tblBuyers on 3/2/05 at 11:29
record 567 in tblLots on 3/2/05 at 11:30
[Etc, etc, for all edits by this user]
No further edits by UserJSmith during the time period specified.â€

I am running on a LAN, Windows server ‘03, Access versions ‘00 and ‘03. I
know how to capture username (thanks to this board) and date and time but I
don’t see any way to capture the table name. The form name won’t due because
the form will contain fields from various tables and the record number won’t
due because (see example) the same nbr appears in several tables.

Other than adding code to every field on every form I don’t see how to do it.

Is there a way?

 

[Guest]

No, I specifically meant the auto number. If you reread my post, you will
note I stated that number was irrelevant. So, you are on to that part.

There is not a specific method that will do what you want. Allen Browne has
some info on creating an Audit log, which is pretty much what you want, but
maybe not exactly what you want. Go to this site:

http://allenbrowne.com/AppAudit.html

Well, 1st by record number i mean the autonumber in the table. In my mind
that IS the ONLY record number, and always will be. It is unique and never
changes and the only way I know of to get a handle on a specfic record. U
seem to mean by record number those silly numbers that display on the status
bar. They are both meaningless and useless as far as i can tell.

That said, looking at the rest of ur answer it seems WAY above my pay grade.
I thought this was a common enough report that some fairly direct way existed
to produce it.

Guess not. Oh well, back to the laundry.
--
SailorMike

The record numbers will be totally irrelevant and would never lead you back
to the record the user changed. Forget record numbers. The are only valid
for the current recordset and even then if the order or filter changes, so do
they. Your best bet would be sure all tables have an auto number field. You
can include those fields in your form's queries and bind them to hidden
controls on your form. Now, the trick is, you will have to know which table
each control on the form gets its data from. So you will know which table to
identify in your table you are building for the report. One possibility is
to use each control's Tag property to identify the table.

So, in the After Update event of the form you can loop through your form's
control collection, compare its value to its OldValue propetry and if they
are not equal, look at the Tag property to see which table it belongs to,
then look in the hidden control for that tables Autonumber field and use this
info to update your report table.

My goal is a report that says something like this:

“User JSmith edited record 333 in tblContracts on 3/2/05 at 11:28
record 567 in tblBuyers on 3/2/05 at 11:29
record 567 in tblLots on 3/2/05 at 11:30
[Etc, etc, for all edits by this user]
No further edits by UserJSmith during the time period specified.â€

I am running on a LAN, Windows server ‘03, Access versions ‘00 and ‘03. I
know how to capture username (thanks to this board) and date and time but I
don’t see any way to capture the table name. The form name won’t due because
the form will contain fields from various tables and the record number won’t
due because (see example) the same nbr appears in several tables.

Other than adding code to every field on every form I don’t see how to do it.

Is there a way?

 

[Guest]

Thanx for the link. I started something like that and give up. This looks
simple enough even i may get to work, eventually.

All this effort is to solve the "Wasn't me that changed that, must have been
somebody else." problem.

Users, can't live with 'em can't live w/o 'em.

Thanx again.
--
SailorMike

No, I specifically meant the auto number. If you reread my post, you will
note I stated that number was irrelevant. So, you are on to that part.

There is not a specific method that will do what you want. Allen Browne has
some info on creating an Audit log, which is pretty much what you want, but
maybe not exactly what you want. Go to this site:

http://allenbrowne.com/AppAudit.html

Well, 1st by record number i mean the autonumber in the table. In my mind
that IS the ONLY record number, and always will be. It is unique and never
changes and the only way I know of to get a handle on a specfic record. U
seem to mean by record number those silly numbers that display on the status
bar. They are both meaningless and useless as far as i can tell.

That said, looking at the rest of ur answer it seems WAY above my pay grade.
I thought this was a common enough report that some fairly direct way existed
to produce it.

Guess not. Oh well, back to the laundry.
--
SailorMike

The record numbers will be totally irrelevant and would never lead you back
to the record the user changed. Forget record numbers. The are only valid
for the current recordset and even then if the order or filter changes, so do
they. Your best bet would be sure all tables have an auto number field. You
can include those fields in your form's queries and bind them to hidden
controls on your form. Now, the trick is, you will have to know which table
each control on the form gets its data from. So you will know which table to
identify in your table you are building for the report. One possibility is
to use each control's Tag property to identify the table.

So, in the After Update event of the form you can loop through your form's
control collection, compare its value to its OldValue propetry and if they
are not equal, look at the Tag property to see which table it belongs to,
then look in the hidden control for that tables Autonumber field and use this
info to update your report table.

:

My goal is a report that says something like this:

“User JSmith edited record 333 in tblContracts on 3/2/05 at 11:28
record 567 in tblBuyers on 3/2/05 at 11:29
record 567 in tblLots on 3/2/05 at 11:30
[Etc, etc, for all edits by this user]
No further edits by UserJSmith during the time period specified.â€

I am running on a LAN, Windows server ‘03, Access versions ‘00 and ‘03. I
know how to capture username (thanks to this board) and date and time but I
don’t see any way to capture the table name. The form name won’t due because
the form will contain fields from various tables and the record number won’t
due because (see example) the same nbr appears in several tables.

Other than adding code to every field on every form I don’t see how to do it.

Is there a way?