Hit Counter

[zyus]

How to put a hit counter number in my startup form or any forms in access.

Thanks

 

[Arvin Meyer [MVP]]

Add a table (I called it tblHits) with a single field (I called it HitCount)
to hold the current count. Then update the field in the OnOpen event of the
form:

Function GetCount() As Long
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngMaxNum As Long

Set db = CurrentDb

Set rst = db.OpenRecordset("Select Max(HitCount) As MaxHits FROM
tblHits")
If IsNull(rst!MaxHits) Then
'no records yet, start with one
lngMaxNum = 1
Else
lngMaxNum = rst!MaxHits + 1
End If

GetCount = lngMaxNum
Me.txtHitCount = GetCount

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

 

[zyus]

Do i have to create a separate table just for the hit or i can add the field
in the current table.

TQ

 

[Arvin Meyer [MVP]]

I'd use a separate table because adding a field may produce results that you
don't want. For instance: The first record to open will have the latest
value. That record will change depending upon filters, queries, etc. Some
records will never have any data, yet there will always be a field. The code
I wrote is really for a field, and as a matter of fact, you can leave out
the Max() function with a separate table, because, as written, there will
never be more than 1 record you'll also need to write that record back to
the table, in the form's close event. Do that like (this is untested):

Function WriteCount() As Long
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * FROM tblHits")

With rst
.Edit
!MaxHits = Me.txtHitCount
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[zyus]

I've created separate tbl with one field named hitcount (autonumber).

My form is pointed to another table say tblA. I hv put unbound field named
txthitcount in my form and put a code below in On Open but it gives Compile
error Expected End Sub....

My intention is to put a hit counter so whenever the form is open the
counter number will increase...

Hope my explaination suffice

 

[Arvin Meyer [MVP]]

First paste the 2 functions in the form's code window. They should appear in
the Declarations section under General.

Then in the OnOpen event use:

Private Sub Form_Open(Cancel As Integer)
Call GetCount()
Call WriteCount()
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com