Count number of times a button on from is clicked

[lookforsmt]

HI!
i have a form which imports excel into db. Below code displays the same when clicked in unbound text field.

Private Sub cmdcopyfiles_6_Click()
Static cnt As Long
cnt = cnt + 1
Me.cmd_Count.SetFocus
Me.cmd_Count.Text = "Clicked" & cnt & " times"
End Sub

The challenge is once the form is closed it reset to zero.
How can i keep the last count displayed after the form is reopened again and it reset only after the date is changed.

any help to modify the code.

 

[lookforsmt]

Apologies. Import code is not provided in the earlier post. i am only looking for help to reset the counter.

 

[Gasman]

Use a TempVar ?

TempVars("ButtonCount")= TempVars("ButtonCount") + cnt

Reset that to zero when your date changes.

Declare the TempVar on startup of the DB

TempVars("ButtonCount") = 0

 

[isladogs]

... Or store the date & count in a table.

What I'm unclear about is why you want to count the clicks
Do you want to import more than once each day?

If not, you could:
a) use a boolean variable & set it true once the import has been done
b) disable the button for the rest of that day
c) reset the boolean variable to false each day & re-enable the button

 

[jleach]

Does this value need to persist across application sessions? (e.g., if you close Access and re-open it, do you want a fresh count, or keep the prior version?)

A few thoughts:

- If you're trying to count the number of imports, just keep an import log and log the import (and date of import) when the import successfully completes, then you can query for the import count at any time.

- Just because the button is clicked doesn't mean the import is sure to happen (file not found, any number of other issues during the process: imports are a particularly unstable process). Thus my first point.

- Static methods will always be reset when the project is closed. Thus, as per Colin's suggestion, table storage is really the ideal way to persist across sessions.

- Tempvars don't survive application restarts either (if that's what you want).

- If you just need to reset the counter for a new date (which I'm not sure is what you're actually after), add a static variable (or whatever) to track DateOfLastImport, and each time this button is clicked, check if DateOfLastImport is the same as Today, and if so, you know you've already an in import today.

In any case, I'd probably veer toward storing it in a table as per the natural flow of data persistence, and use the native Access tools (i.e. - a query) to determine the current count.

Cheers

 

[lookforsmt]

Thank you all for your suggestions.

i am actually going to try all the solutions for my project.

I already have a Date table which records only date. Do i need to open a log table to record Date & count or i can use the date table

 

[isladogs]

You can use the same table.
Are you going to add a new record each day or just overwrite the existing date and count?

 

[lookforsmt]

Thanks ridders, The date table is adding date everyday based on the data imported.
if i need to create log table then i want it to override it on daily basis. Is this possible.

Do you want to import more than once each day? [ NO ]

If not, you could:
a) use a boolean variable & set it true once the import has been done
b) disable the button for the rest of that day
c) reset the boolean variable to false each day & re-enable the button

How can i code above a; b & c

 

[lookforsmt]

hi!, i am tried the below code it displays the number of times the button is clicked in the text field on the form, but it does not get updated in the table.
The text box field get blank once the form is closed and reopened.

Below is the code

    Static hits As Long
    hits = hits + 1
    Me.hits.SetFocus
    If IsNull(Me.hits.Text) Then
    Me.hits.Text = 0
  Else
    Me.hits.Text = "Clicked  " & hits & " time"
  End If

 

[lookforsmt]

HI! all

any suggestions for improving the code, i need to keep displaying the click in the text box so the user does not click the button again.
And also, with the change in date the display reset to zero.

i have created table named Hits and two fields:
hits=Number & EntryDate=Date/Time

 

[isladogs]

I didn't answer before as posts 8 & 9 seemed to be asking for different things.
Which approach do you want to use?

 

[lookforsmt]

Thanks ridders, when given there were couple of options, i wanted to try each of them
1) disable the button after the click - I tried this but after i close the form the button is enabled and the user will not know if the button was clicked earlier.
2) display the click count - I have tried this as well but same as 1) after the form is closed and reopned the text field is blank.
3) I have created another table hits with 2 fields, hits & EntryDate to record the click, but i am not sure how to do this

So i am looking for the 3 option of recording the click and same time it should reset after the date change.

 

[lookforsmt]

Hi! ridders, any suggestion

 

[isladogs]

I've got visitors so can't reply properly for a while.
Can you post your fb for me to look at it?

 

[lookforsmt]

Thanks ridders for the help. Sorry you can reply at your convenience.

i have attached my db.

 

[isladogs]

I've had a quick look at this but before I respond properly can you please clarify your response in post 8.
Do you want users to only click once or click multiple times?
If only once, then my suggestion of disabling the button is the way to go.

If multiple times, then I'll give you a solution for storing the click count (or hits)

 

[isladogs]

Decided to give you 2 different solutions so you can choose which you prefer.
In both cases the form is BOUND to save you a lot of unnecessary work

1 table - EntryDate (PK field) & Clicks (Number)
NOTE I've taken the liberty of removing all underscores to simplify coding

Form A
This is bound to table with 2 bound controls so data is saved automatically
Record source set to current date's record

Both controls are LOCKED to prevent manual editing
When form opens, new record created in table for current date with clicks = 0.
Because entry date is the PK field, reopening the form on same day does not create a new record

Each button click, adds 1 to click count on form (& in table)
Reopening form shows data from previous time form opened

Form B
Similar to form A EXCEPT after 1 button click the button is disabled

 

[lookforsmt]

Thanks ridders, i was away from my laptop due the time difference here.
i was wanting to have both the options so i could apply in my project.

My response in post#8 was asking how can i disable the button once i clicked so the user has no choice. You have already given me the solution in your reply post#17.

I found code on the net for counting the clicks on the form, but it only counted the clicks and i was looking for how to control this by coding. You have also given me this solution in your reply post#17.

I want to thank you very much. I have all my projects with these controls. Now i can apply them to have a better control.

Just coming back to your solution. I am getting a error when i click on the form

Undefined function 'Date' in expression

pls advice, thhanks

 

[isladogs]

Sounds like a reference issue or that its not in a trusted location

If you see a yellow security warning at the top with a button marked Enable Content, click the button!

Check whether there are any VBA references listed as MISSING.
if so, browse and restore them.
Which version of Access are you using? 32 bit or 64 bit?


Forgot to mention that I had removed 3 Acrobat references which were flagged as missing on my computer and not needed for this example.

I've now removed all but the 3 standard Access references and restested this in 32-bit versions of Access 2010 & 2016 as well as Access 64-bit on my laptop.
No errors in any of these.

Updated version attached

 

[lookforsmt]

Thanks ridders for the detailed explanation and the code. It has worked for me fitting for my project. I can use this in most of my projects.

Thanks once again for you help in building the code.
by the way, i am using 64 bit on my laptop and i have made a note of the reference you made in the post.