User and Date Stamp

[Guest]

Hello

I am a complete novice so please forgive what may be a simple question.

I have a database that is shared and has approx 10 users. Each of these
users should update the table via a main form once a week.

I'd like to create a query or report which I could run, telling me the last
date each of these users either opened the databse or updated it (whichever
is easiest). They all use diff laptops and have their own logins (if this
helps?)
Thanks
Kate

 

[KML]

Kate,

Please refer to Allen Browne's excellent work on this subject:

http://www.allenbrowne.com/AppAudit.html

 

[Guest]

Thanks so much for the quick reply. This will take me a while to do and
looks fairly complex but will give it a go!

Just before I start tackling it - I just want to be clear it's going to
provide me with what I need, namely a table or query or report which shows
the last date users either entered or modified data. I don't want this by
record, nor do i want the detail of what was changed - simply the date they
last entered the form?

Thanks

 

[manningfan]

As I recall, that tutorial datestamps records. If you just want to
track when a user enters a form, put some code on the OnLoad event that
writes to a table the username ( Environ("UserName") ) and the form
name ( Me.Name ). Possibly something like:

Private Sub MyForm_Load()
dim db as database
dim rec as recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("tblTracking")
With rec
.AddNew
.Fields("UserName") = Environ("UserName")
.Fields("FormName") = Me.Name
.Fields("DateStamp") = Now()
.Update
.Close
End With

End Sub

If you need to just keep the most recent record, you'll have to do a
DLookup or something to check and see if the record exists, and if so
do an Update on it:

CurrentDb.Execute ("Update tblTracking Set DateStamp = " & Now() & "
Where UserName = " & Environ("UserName") & " and FormName = " & Me.Name

 

[Guest]

Thanks so much for this.

Have followed your notes and created a table called 'tbltracking' but when i
enter the form, i get a 'compile error' which states that 'User Defined Type
Not Defined' and highlights 'dim db as database' in the debugger.

I'm sure is my error, but can't see what I've done wrong!

 

[manningfan]

Hmmm... I believe you will see that error if you don't have the
correct references installed. I apologize in advance but I'm not
positive which references you need for that.

In your code module, click on Tools and then References. It may be
Microsoft DAO Object Library, or possibly the VBA Extensibility. Also
check the Access Object Library. One of those should make that error
go away.

 

[Douglas J. Steele]

It's the DAO Object Library that's missing to generate that error.

 

[Guest]

Thanks so much both

However - one door opens and another closes (or something like that?)

Have got rid of the the compile error by following you instructions but now,
when I enter the form I get the following error 'RunTime error '13' Type
Mismatch'. The debugger then highlights the code
'Set rec = db.openrecordset("tbltracking")

I'm a complete novice when it comes to code - but could this be because my
form doesn't open to a record, the user has to select which record to edit
data for?

Any help (again!) would be gratefully appreciated

 

[Douglas J. Steele]

Change the declaration

dim rec as recordset

to

Dim rec As DAO.Recordset


Since adding the reference to DAO corrected the compile error, you must be
using either Access 2000 or 2002. By default, they have a reference set to
ADO, but not to DAO. The problem is, the Recordset object exists in both the
DAO and ADO models. Because the reference to ADO is higher in the sequence
than the reference to DAO, that's what's going to be used by default. (If
you wanted to be positive you were using an ADO recordset, you'd use Dim rec
As ADODB.Recordset)

 

[Guest]

You guys are geniuses! What a great forum.

Bang on - it worked a treat and does exactly what I want.

Thanks so much