Tracking/Audit Trail Question (and another related to requiredfields)

[wedomarketing]

Hi everyone. Had a quick question to see if this would be possible.

We already have an audit trail installed and it works like a dream,
but my Boss made an interesting proposal and I would like to see if it
could be done.

Is it possible to track when a user actually views a record within a
form, and what user viewed it?

i.e. - Susan viewed this record at 10:43 a.m.

And that's it.

He was also wondering if there would be a way to actually force the
user to make a notation on the form every time the form was viewed.

i.e. - User is browsing through records, can't access the next record
in the list until they check off a box that says "Called Record" or
"Reviewed Record" or "Contacted Record". Then User can continue
browsing. If they page back to that record, the process resets itself
and they again need to verify their reason for being there before they
can leave.

While I don't see immediate issue with my first question other than my
lacking in knowledge on how to do it, the second one seems extremely
impossible, unless I'm missing something.

Here's how we have this set up:

Each form relates to a table. On the form, the data can be created and
entered, but not edited once they leave the form.... at least on the
form itself. We then have a sub-table built into the form that handles
amendments. This way the users can change whatever field they want,
but we still have a record of the main entry that was put in. The
audit trail currently maintains both of these.

I just don't understand how in the world we could possibly have
something that's required every single time a user views a record...
since technically, you would be fulfilling that requirement to leave
the record. It would almost have to auto-delete itself upon
leaving...

My head hurts trying to wrap it around this, but hopefully someone out
there might be able to grasp it and understand what we're trying to
accomplish -- and hopefully point us in the right direction.

 

[wedomarketing]

I should have also noted that we are using Microsoft Access 2007 in
2003 mode, which I'm sure will matter.

 

[Arvin Meyer MVP]

Use the Windows Username api to capture the name of the currently logged on
user:

http://www.mvps.org/access/api/api0008.htm

Then in the form's Current event which fires for each record, fill 2
textboxes (which can even be hidden)

Private Sub Form_Current()
Me.txtuser = fOSUserName()
Me.txtViewed = Now
End Sub

For your second task, you need to replace the form's navigation with your
own, then disable the buttons in the form's Current event, and Enable them
in the AfterUpdate event of the checkbox.

 

[Arvin Meyer MVP]

I should have also noted that we are using Microsoft Access 2007 in
2003 mode, which I'm sure will matter.

Actually, it doesn't matter at all as long is your app is in a Trusted
Location and you allow code to run.

 

[wedomarketing]

How are you referencing the two text boxes? I know what they're there
for, but I think that something needs to be created table-side,
correct? Sorry -- everything makes sense up till filling those two
text boxes; I don't know how to relate the code back to the boxes
themselves.

 

[Arvin Meyer MVP]

How are you referencing the two text boxes? I know what they're there
for, but I think that something needs to be created table-side,
correct? Sorry -- everything makes sense up till filling those two
text boxes; I don't know how to relate the code back to the boxes
themselves.

If you want to save the username and time, you must do it in a table. The 2
text boxes (txtUser and txtViewed) are bound to those fields in the table.
Since the code is in the form's Current event, it pushes the data into the
text boxes every time a record is viewed. The code on the website needs to
be pasted into a standard module and saved so that it can be called by the
form's Current event.

 

[botts08]

Hello,

I work with wedomarketing and when I open the form I get an
error that says "Compile Error: Expected variable or procedure, not
module"...
I click ok and then it highlights the "Private Sub Form_Current()" in
yellow
and highlights the "fOSUserName" in blue.

I really don't know what I'm doing wrong. It's probably something
simple
but could use your help.

Thanks,
Matt

 

[wedomarketing]

Is there any way to take the "viewed by" code provided in this topic
and save it (i.e. add to it each time someone views)? We got it to
work, but it overwrites the previous value instead of adding to it (as
our current audit trail for edits does). This makes tracking difficult
if we can only see the last person that accessed it.

I have been working on the second task and I've made very little
progress. I have managed to recreate the navigation just fine, that
was the easy part... But after fiddling around with code (obvious
newbie), I was only able to come up with this:

---------------> Start Code <--------------------

Option Compare Database

Private Sub Form_Current()

' Provide a record counter for using with
' custom navigation buttons (when not using
' Access built in navigation)

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

'Show the result of the record count in the text box (txtRecordNo)

Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount

****If Reviewed = No And nocontact = No And contact = No Then
Command12.Enabled = False Else Command12.Enabled = True****


End Sub

--------------->End Code<----------------

The asterisks indicate the portion I muddled together to make it so
that my command button is only enabled if at least one of the
checkboxes (labeled reviewed, nocontact, contact) is checked. This,
however, requires an F5 to take effect.

Tried messing around with AfterUpdate, but it didn't like the code
(probably because of the backwards way I'm attempting to do this
because of my lack of knowledge).

So yeah -- Now that this SORT of works, I need it to update itself,
preferably, when the user clicks on the checkbox. I also need it to
clear the values out upon paging to a new page (so when they navigate
back, they have something to click again), and then I need all these
clicks stored in a database with a username attached to them so I can
see who's reviewing the leads, who's contacting them, and who's
leaving messages.

:-(

Not fun... Appreciate any help anybody can give us 2.

 

[Arvin Meyer MVP]

Did you name the standard module the same as the function? If so, change it.
Also make sure that you've copied and pasted the entire code snippet. That
piece of code is very stable, having been used for more than 11 years now by
probably several million people. It is one of the more popular downloads.

 

[Arvin Meyer MVP]

Do you want to record every view, or only those views where 1 or more check
boxes is/are checked yes? If there are more than 1, are they in an option
group?

 

[wedomarketing]

We got it to record the views every single time -- so that worked out
well for us, and it's finally recording every single view. We just
threw it into the detail instead of the header, and our audit trail
picked up the changes.

So thank you very much for your assistance in that matter.

We're still trying to figure out the other issue with the checkboxes
-- and we're at a near standstill, so any help there would be awesome.

 

[wedomarketing]

Alright, I'm going to try to envision this in a different, hopefully
simpler way.

How could we implement a system that requires a user to make a comment
in a text box every time before they paged to a new contact? And then
record these all table-side?

Note that we already have custom navigation enabled.

This is our last step to getting our database completely set up, so we
would greatly appreciate any help someone could provide us with.

Thank you!