Counting # of times accessed/used.

[Guest]

I need to know how to count the event that happens below and record it either
to a table or something so that I can see if certain items are frequently
used or can be slated to obsolete. Seeing the commands down below how would
I insert a counting feature? Count table name is "Counter" with field name
as "Scrap by Model". Also is there any difference in recording this on
buttons, queries, reports, etc. or can I use the same command lines for
counting? Thanks.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

stDocName = "Scrap by Model"
DoCmd.OpenReport stDocName, acPreview

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

 

[Graham R Seach]

James,

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

RecordUsage Me.Name, Screen.ActiveControl.Name '*****

stDocName = "Scrap by Model"
DoCmd.OpenReport stDocName, acPreview

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

Public Sub RecordUsage(sFrmName As String, sCtrlName As String)
Dim sSQL As String
Dim sField As String

On Error GoTo Proc_Err

'Assumes the following table exists:
'tblCount
' FormName (Text, 255) Primary Key
' ControlName (Text, 255) Primary Key
' Count (Long Integer)

sField = "[" & sFrmName & "]![" & sCtrlName & "]"

sSQL = "UPDATE tblCount SET Count = Count +1 " & _
"WHERE FormName = """ & sFrmName & _
""" AND ControlName = """ & sCtrlName & """"

CurrentDb.Execute sSQL, dbfailonerror

Proc_Exit:
Exit Sub

Proc_Err:
Err.Clear
On Error GoTo Proc_Still_Err

sSQL = "INSERT tblCount (FormName, ControlName, Count) " & _
"VALUES (""" & sFrmName & """,""" & sCtrlName & """,1)"

CurrentDb.Execute sSQL, dbfailonerror
Resume Proc_Exit

Proc_Still_Err:
Resume Proc_Exit
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

Thanks for the help in sending me on the right direction. This helps a lot
and it now works!

After learning this function I had to modify a few items in the following
sections to make it work. This info is for anyone else who may be looking at
it.

sSQL = "UPDATE [Count] SET Count = Count +1 " & _
"WHERE FormName = """ & sFrmName & _
""" AND ControlName = """ & sCtrlName & """"

sSQL = "INSERT INTO [Count] (FormName, ControlName, Count) " & _
"VALUES (""" & sFrmName & """,""" & sCtrlName & """,1)"

For some reason I also had to change the indexes on the table [Count] to
accept duplicates. Since I only normally use only one primary key it
probably is that I am not familiar enough with using two keys.

James Kendall

James,

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String

RecordUsage Me.Name, Screen.ActiveControl.Name '*****

stDocName = "Scrap by Model"
DoCmd.OpenReport stDocName, acPreview

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

Public Sub RecordUsage(sFrmName As String, sCtrlName As String)
Dim sSQL As String
Dim sField As String

On Error GoTo Proc_Err

'Assumes the following table exists:
'tblCount
' FormName (Text, 255) Primary Key
' ControlName (Text, 255) Primary Key
' Count (Long Integer)

sField = "[" & sFrmName & "]![" & sCtrlName & "]"

sSQL = "UPDATE tblCount SET Count = Count +1 " & _
"WHERE FormName = """ & sFrmName & _
""" AND ControlName = """ & sCtrlName & """"

CurrentDb.Execute sSQL, dbfailonerror

Proc_Exit:
Exit Sub

Proc_Err:
Err.Clear
On Error GoTo Proc_Still_Err

sSQL = "INSERT tblCount (FormName, ControlName, Count) " & _
"VALUES (""" & sFrmName & """,""" & sCtrlName & """,1)"

CurrentDb.Execute sSQL, dbfailonerror
Resume Proc_Exit

Proc_Still_Err:
Resume Proc_Exit
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html