In this guide, I’ll show you the steps to prevent double clicking in Access on the same day.
I can think of few examples of why this functionality might be useful:
- For instance, you may create a button, on an Access Form, which allows users to import daily files. You may then want to prevent users from importing the same daily files again
- Alternatively, you may add a button to execute several Macros/VBAs once per day. You may then want to prevent double-processing on the same day
Methodology to prevent double clicking in Access
There is more than one way to prevent double clicking in Access. I’ll be using the following methodology:
- When the user presses the button for the first time, the system date will be inserted into an Archive table
- If the user presses the button again on the same day, then the following message will be displayed: “You already pressed that button today. Try again tomorrow”
- The button will not perform any additional processing if the current system date = system date inserted into the Archive table
Steps to apply the methodology
(1) First, you’ll need to create a table in Access called ‘Archive_Dates’ with the following fields:
- ID – this field will be your primary key, which will be set to AutoNumber
- Archive_Dates – this field will be used to store the system dates
(2) Now create a second table called ‘Current_Date’ with only one field:
- Date – type any value within that field. For example, type the value of 1:
(3) Write the following SQL query to generate the current system date on a daily basis.
Save this query as: ‘Current_Sys_Date’
SELECT Format(Date(),'ddmmyy') AS System_Date FROM [Current_Date]
(4) Next, create the below Insert query to insert the current system date (from the query created in step 3) into the Archive_Dates table.
Save this query as: ‘Insert_Into_Archive_Dates’
INSERT INTO Archive_Dates (Archive_Dates) SELECT Current_Sys_Date.System_Date FROM Current_Sys_Date
(5) Now, create the following query to ensure that only the maximum record is taken from the ‘Archive_Dates’ table.
Save this query as: ‘Max_ID’
SELECT Archive_Dates.ID, Archive_Dates.Archive_Dates FROM Archive_Dates WHERE Archive_Dates.ID = (select max(Archive_Dates.ID) from Archive_Dates)
(6) Add a button on an Access Form:
(7) Finally, include the below VBA that will be triggered when you click the button:
If DLookup("[Archive_Dates]", "[Max_ID]") = Format(Date, "ddmmyy") Then MsgBox ("You already pressed that button today. Try again tomorrow") Else DoCmd.OpenQuery "Insert_Into_Archive_Dates" Exit Sub End If
The above VBA would check if the date in the Max_ID table matches with the current system date. If it does, then a message box will be opened to inform you that the button was already pressed for today.
However, if the date in the Max_ID table does not match with the current system date, then:
- The current system date will be inserted into the Archive_Dates table for the first time
- The next time that you press the button, the date in the Max_ID table would match with the current system date. You’ll then get the same message box that the button was already pressed for today
And this is the full VBA to prevent double-clicking in Access:
Let’s test the above methodology.
(1) First, click on the button while in the Form View:
(2) Then click on that button again (on the same day). You’ll get this message:
You’ll have to wait till the next day, before you can use this button again.
Enhancing the VBA Code
You may wish to include additional VBA commands in between the Else and Exit Sub that can only be executed once per day. For example, you may want to insert a VBA command to import a file into Access.
Let’s suppose that you have a simple Excel file called Test_File. The file contains this information:
Now let’s say that you want to import that file into a table in Access called Test_Table. You decided to put controls to prevent users from importing the same file again on the same day.
Therefore, your full VBA would look as follows (note that you’ll need to change the PATH to the location where your Excel file is stored on your computer):
Dim PATH As String PATH = "C:\Users\Doron E\Desktop\Import\Test_File.xlsx" If DLookup("[Archive_Dates]", "[Max_ID]") = Format(Date, "ddmmyy") Then MsgBox ("You already pressed that button today. Try again tomorrow") Else DoCmd.OpenQuery "Insert_Into_Archive_Dates" DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Test_Table", PATH, True Exit Sub End If
And this is how the VBA would look like in Access:
If you press the button for the first time, then the data in Excel will be copied into the Test_Table in Access:
Try to press this button again on the same day, and you’ll get this message:
Your file will not be imported again on the same day. You’ll have to wait till the next day before you can import a new file into Access.
You have seen how to prevent double clicking in Access on the same day. The methodology described in this tutorial can be used to place controls, and ensure that certain buttons can only be used once per day.
For additional step-by-step guides, please visit our MS Access tutorials.