Hit counter and who last accessed databse

  • Thread starter Thread starter gillman54
  • Start date Start date
G

gillman54

Hi,

Is it possible to insert a hit counter into a database form, also is it
possilble to display who last accessed a form and the date which they visited?

Thanks

Chris
 
It's possible, but you'll have to do all of the work yourself, as Access
doesn't keep that sort of information.

You'll probably want a table that holds Form Name, User Name and Date
Accessed. Let's assume that table is named FormUsage, with fields named
FormName, UserName and DateAccessed.

Let's also assume that you have labels named lblHits, lblLastAccessedBy and
lblLastAccessedOn on your form.

In the form's Open event, you'd query the FormUsage to get the statistics,
and add a row for the current user:

Private Sub Form_Open(Cancel As Integer)

Const cDateFormat As String = "\#yyyy\-mm\-dd hh\:nn\:ss\#"

Dim dtmLastAccessed As Date
Dim lngTotalHits As Long
Dim strLastAccessedBy As String
Dim strSQL As String

lngTotalHits = DCount("*", "FormUsage", _
"FormName = '" & Me.Name & "'")

Me!lblHits.Caption = lngTotalHits

If lngTotalHits > 0 Then
dtmLastAccessed = DMax("DateAccessed", "FormUsage", _
"FormName = '" & Me.Name & "'")
strLastAccessedBy = DLookup("UserName", _
"FormUsage", "FormName ='" & Me.Name & _
"' And DateAccessed = " &
Format(dtmLastAccessed, cDateFormat)
Me.lblLastAccessedBy.Caption = strLastAccessedBy
Me.lblLastAccessedOn.Caption = dtmLastAccessed
Me.lblLastAccessedBy.Visible = True
Me.lblLastAccessedOn.Visible = True
Else
Me.lblLastAccessedBy.Visible = False
Me.lblLastAccessedOn.Visible = False
End If

strSQL = "INSERT INTO FormUsage " & _
"(FormName, UserName, DateAccessed) " & _
"VALUES('" & Me.Name & "', '" & _
fOSUserName & "', " & _
Format(Now(), cDateFormat) & ")"

CurrentDb.Execute strSQL, dbFailOnError

End Sub


The fOSUserName, which retrieves the network ID of the current user is,
comes from http://www.mvps.org/access/api/api0008.htm at "The Access Web"
 
Sure, you could either use a table or a custom database property to store the
value, then, just increment it each time the form is opened. You could do
the same with the userid and date visited.

If you go the table route, you could create a table that contains fields
FormUseID, FormName, HitCounter, LastUser, LastAccess

If you wanted to do this for more than one form, I would create a subroutine
to do the update for you. Something like:

Public Sub FormUse(FormName as String)

Dim strSQL as String

if DCOUNT("FormUseID", "tbl_FormUse", "FormName = '" & FormName & "'") =
0 Then
strSQL = "INSERT INTO [" & FormName & "] (FormName, HitCounter,
LastUser, LastAccess) " _
& "Values ('" & FormName & "', 1, '" & fOSUserName() &
"', #" & Now() & "#)"
Else
strSQL = "UPDATE tbl_FormUse " _
& "SET [HitCounter] = [HitCounter] + 1, " _
& "[LastUser] = '" & fOSUserName() & "', " _
& "[LastAccess] = #" & Now() & "# " _
& "WHERE [FormName] = '" & FormName & "'"
End If
currentdb.execute strsql, dbFailOnError

Exit Sub

Then, in the forms open or load event, you would add a line that calls the
FormUse subroutine, something like:

Private Sub Form_Load

Call FormUse(me.name)

End Sub

If you then want to display the counter on your form, you would use the
DLOOKUP() function as the ControlSource for a textbox:

= DLOOKUP("HitCount", "tbl_FormUse", "FormName = 'frmWhatever'")
 
forgot to mention that the fOSUserName() function comes from the Access Web
site at: http://www.mvps.org/access/api/api0008.htm

----
HTH
Dale



Dale Fye said:
Sure, you could either use a table or a custom database property to store the
value, then, just increment it each time the form is opened. You could do
the same with the userid and date visited.

If you go the table route, you could create a table that contains fields
FormUseID, FormName, HitCounter, LastUser, LastAccess

If you wanted to do this for more than one form, I would create a subroutine
to do the update for you. Something like:

Public Sub FormUse(FormName as String)

Dim strSQL as String

if DCOUNT("FormUseID", "tbl_FormUse", "FormName = '" & FormName & "'") =
0 Then
strSQL = "INSERT INTO [" & FormName & "] (FormName, HitCounter,
LastUser, LastAccess) " _
& "Values ('" & FormName & "', 1, '" & fOSUserName() &
"', #" & Now() & "#)"
Else
strSQL = "UPDATE tbl_FormUse " _
& "SET [HitCounter] = [HitCounter] + 1, " _
& "[LastUser] = '" & fOSUserName() & "', " _
& "[LastAccess] = #" & Now() & "# " _
& "WHERE [FormName] = '" & FormName & "'"
End If
currentdb.execute strsql, dbFailOnError

Exit Sub

Then, in the forms open or load event, you would add a line that calls the
FormUse subroutine, something like:

Private Sub Form_Load

Call FormUse(me.name)

End Sub

If you then want to display the counter on your form, you would use the
DLOOKUP() function as the ControlSource for a textbox:

= DLOOKUP("HitCount", "tbl_FormUse", "FormName = 'frmWhatever'")

----
HTH
Dale



gillman54 said:
Hi,

Is it possible to insert a hit counter into a database form, also is it
possilble to display who last accessed a form and the date which they visited?

Thanks

Chris
Click to expand...
Click to expand...
 
<picky>

& "[LastAccess] = #" & Now() & "# "

isn't a good idea, IMHO, since it won't work as expected for users whose
Short Date format is set to dd/mm/yyyy.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dale Fye said:
Sure, you could either use a table or a custom database property to store
the
value, then, just increment it each time the form is opened. You could do
the same with the userid and date visited.

If you go the table route, you could create a table that contains fields
FormUseID, FormName, HitCounter, LastUser, LastAccess

If you wanted to do this for more than one form, I would create a
subroutine
to do the update for you. Something like:

Public Sub FormUse(FormName as String)

Dim strSQL as String

if DCOUNT("FormUseID", "tbl_FormUse", "FormName = '" & FormName & "'")
=
0 Then
strSQL = "INSERT INTO [" & FormName & "] (FormName, HitCounter,
LastUser, LastAccess) " _
& "Values ('" & FormName & "', 1, '" & fOSUserName() &
"', #" & Now() & "#)"
Else
strSQL = "UPDATE tbl_FormUse " _
& "SET [HitCounter] = [HitCounter] + 1, " _
& "[LastUser] = '" & fOSUserName() & "', " _
& "[LastAccess] = #" & Now() & "# " _
& "WHERE [FormName] = '" & FormName & "'"
End If
currentdb.execute strsql, dbFailOnError

Exit Sub

Then, in the forms open or load event, you would add a line that calls the
FormUse subroutine, something like:

Private Sub Form_Load

Call FormUse(me.name)

End Sub

If you then want to display the counter on your form, you would use the
DLOOKUP() function as the ControlSource for a textbox:

= DLOOKUP("HitCount", "tbl_FormUse", "FormName = 'frmWhatever'")

----
HTH
Dale



gillman54 said:
Hi,

Is it possible to insert a hit counter into a database form, also is it
possilble to display who last accessed a form and the date which they
visited?

Thanks

Chris
Click to expand...
Click to expand...
 
Thanks, Doug. I like your date format string, think I'll start using that as
well

----
HTH
Dale



Douglas J. Steele said:
<picky>

& "[LastAccess] = #" & Now() & "# "

isn't a good idea, IMHO, since it won't work as expected for users whose
Short Date format is set to dd/mm/yyyy.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dale Fye said:
Sure, you could either use a table or a custom database property to store
the
value, then, just increment it each time the form is opened. You could do
the same with the userid and date visited.

If you go the table route, you could create a table that contains fields
FormUseID, FormName, HitCounter, LastUser, LastAccess

If you wanted to do this for more than one form, I would create a
subroutine
to do the update for you. Something like:

Public Sub FormUse(FormName as String)

Dim strSQL as String

if DCOUNT("FormUseID", "tbl_FormUse", "FormName = '" & FormName & "'")
=
0 Then
strSQL = "INSERT INTO [" & FormName & "] (FormName, HitCounter,
LastUser, LastAccess) " _
& "Values ('" & FormName & "', 1, '" & fOSUserName() &
"', #" & Now() & "#)"
Else
strSQL = "UPDATE tbl_FormUse " _
& "SET [HitCounter] = [HitCounter] + 1, " _
& "[LastUser] = '" & fOSUserName() & "', " _
& "[LastAccess] = #" & Now() & "# " _
& "WHERE [FormName] = '" & FormName & "'"
End If
currentdb.execute strsql, dbFailOnError

Exit Sub

Then, in the forms open or load event, you would add a line that calls the
FormUse subroutine, something like:

Private Sub Form_Load

Call FormUse(me.name)

End Sub

If you then want to display the counter on your form, you would use the
DLOOKUP() function as the ControlSource for a textbox:

= DLOOKUP("HitCount", "tbl_FormUse", "FormName = 'frmWhatever'")

----
HTH
Dale



gillman54 said:
Hi,

Is it possible to insert a hit counter into a database form, also is it
possilble to display who last accessed a form and the date which they
visited?

Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
This is what I use in the land of oz.

& "[LastAccess] = Now() " _



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Dale Fye said:
Thanks, Doug. I like your date format string, think I'll start using that
as
well

----
HTH
Dale



Douglas J. Steele said:
<picky>

& "[LastAccess] = #" & Now() & "# "

isn't a good idea, IMHO, since it won't work as expected for users whose
Short Date format is set to dd/mm/yyyy.

</picky>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dale Fye said:
Sure, you could either use a table or a custom database property to
store
the
value, then, just increment it each time the form is opened. You could
do
the same with the userid and date visited.

If you go the table route, you could create a table that contains
fields
FormUseID, FormName, HitCounter, LastUser, LastAccess

If you wanted to do this for more than one form, I would create a
subroutine
to do the update for you. Something like:

Public Sub FormUse(FormName as String)

Dim strSQL as String

if DCOUNT("FormUseID", "tbl_FormUse", "FormName = '" & FormName &
"'")
=
0 Then
strSQL = "INSERT INTO [" & FormName & "] (FormName, HitCounter,
LastUser, LastAccess) " _
& "Values ('" & FormName & "', 1, '" & fOSUserName()
&
"', #" & Now() & "#)"
Else
strSQL = "UPDATE tbl_FormUse " _
& "SET [HitCounter] = [HitCounter] + 1, " _
& "[LastUser] = '" & fOSUserName() & "', " _
& "[LastAccess] = #" & Now() & "# " _
& "WHERE [FormName] = '" & FormName & "'"
End If
currentdb.execute strsql, dbFailOnError

Exit Sub

Then, in the forms open or load event, you would add a line that calls
the
FormUse subroutine, something like:

Private Sub Form_Load

Call FormUse(me.name)

End Sub

If you then want to display the counter on your form, you would use the
DLOOKUP() function as the ControlSource for a textbox:

= DLOOKUP("HitCount", "tbl_FormUse", "FormName = 'frmWhatever'")

----
HTH
Dale



:

Hi,

Is it possible to insert a hit counter into a database form, also is
it
possilble to display who last accessed a form and the date which they
visited?

Thanks

Chris
Click to expand...
Click to expand...
Click to expand...
 
Back
Top