In this guide, I’ll show you the steps to prevent double clicking in Access on the same day.
Method to Prevent Double Clicking in Access
There is more than one way to prevent double clicking in Access. I’ll be using the following method:
- When you press the button for the first time, the system date will be inserted into an Archive table
- If you press the button again on the same day, then no additional processing will occur, as the current system date = system date inserted into the Archive table
Steps to apply the methodology
(1) First, 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 an 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 a 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 method.
(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 to Prevent Double-Clicking in Access
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 the Test_File. The file contains this information:
Now let’s say that you want to import that file into a table in Access called the 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\Ron\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 on 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 method described in this tutorial can be used to place controls, and ensure that certain buttons can only be used once per day.