+1 per click on listbox item

[craigprice]

I have an access form which has a list box of applications which people click to quickly launch, I want to be able to track the number of times each of these is clicked to create a top 10 frequently used applications - is there a way that if I create a column for clickcount in my applications table that I can have the number increase as people click on the listbox item?

I am comfortable with how to build the query to gather the top 10 just not sure on how to make the count work.

 

[spikepl]

You need an update query to increment the counter by 1 in the record where the counter for a given app is held.

 

[craigprice]

Ok, maybe I'm not as sure about the query as I was - I have a query that sorts the applications into alphabetic order, I assume I would add my "ClickCount" column into this query and then make it increment the number?

The applications list from a search query, would I need to add an update into this query to detect when an item is clicked and to +1?

The Table is called AppLinks, this contains the AppName, AppLink and ClickCount fields - I need it to detect that an AppName has been selected and +1 to the particular App's ClickCount field.

 

[willknapp]

You could easily incorpoate a VBA procedure that would update the values for you. How do the users interact with the list box? What steps do they take to launch the apps? That is, do they choose an app then click a command button, or do they double click an app?

 

[willknapp]

You can add something along these lines to the code that launches the apps:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM AppLinks WHERE Appname = '" & ListBox.Value & "'")
With rs
    .Edit
    !ClickCount = 1 + !ClickCount
    .Update
    .Close
End With
Set rs = Nothing
Set db = Nothing

 

[craigprice]

it doesnt seem to like that.

Once it gets to .Edit it fails.

Private Sub Combo2_AfterUpdate()
If Len(Me.Combo2 & vbNullString) <> 0 Then
    Application.FollowHyperlink Me.Combo2
End If
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM AppLinks WHERE Appname = '" & Combo2.Value & "'")
With rs
    .Edit
    !ClickCount = 1 + !ClickCount
    .Update
    .close
End With
Set rs = Nothing
Set db = Nothing
 
End Sub

It says no current record on the debug message...

 

[willknapp]

Is Combo2.Value tied to AppName, or is it tied to a different ID field/value?

Also - try using "me.combo2" (like you do earlier in your code) instead of "Combo2.value" - my sample code used "listbox.value" as a generic placeholder, not necessarily a literal control reference.

 

[craigprice]

Still getting the same error when doing that, no current record - driving me crazy I can't understand why it can't see it.

The naming may throw you off a bit, it is infact a list box, it was originally a combo box and I never renamed, so the list box uses a query to list all the applications, you can search with this to filter out the applications, then click on one to launch I just can't understand why it isn't working, it seems it's not detecting which one I'm clicking on perhaps?

 

[willknapp]

I see what the problem is - based on the code you posted, it appears that the bound column of Combo2 is the AppLink column, not the AppName.

Try this with the changes in red:

Private Sub Combo2_AfterUpdate()
If Len(Me.Combo2 & vbNullString) <> 0 Then
    Application.FollowHyperlink Me.Combo2
End If
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.CurrentDb
 Set rs = db.OpenRecordset("SELECT * FROM AppLinks WHERE [B][COLOR=red]AppLink[/COLOR][/B] = '" & Me.Combo2 & "'")
With rs
    .Edit
    !ClickCount = 1 + !ClickCount
    .Update
    .Close
End With
[COLOR=red]Me.Combo2.Requery    [/COLOR][COLOR=seagreen]' Refreshes the listbox to display the updated value[/COLOR]
Set rs = Nothing
Set db = Nothing
 
End Sub

 

[craigprice]

Brilliant! It worked, thank you very much

 

[willknapp]

Brilliant! It worked, thank you very much

Happy to be of help! Good luck.