How to Prevent Double Clicking in Access

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

Empty table example

(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:

Example of simple table

(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:

How to Prevent Double Clicking in Access

(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:

Prevent Double Clicking in Access

Let’s test the above method.

(1) First, click on the button while in the Form View:

Button

(2) Then click on that button again (on the same day). You’ll get this message:

Message Box

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:

Test_Field
ABC
DEF

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:

How to Prevent Double Clicking 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:

Example of dummy data

Try to press this button again on the same day, and you’ll get this message:

How to Prevent Double Clicking in Access

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.

Conclusion

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.