How to Prevent Double Clicking in Access

In this tutorial, 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 will 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

 

Archive Table

 

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

 

Field in Access

 

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

 

Button in MS Access

 

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

 

How to Prevent Double Clicking in Access

 

Let’s test the above methodology.

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

Button Access

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

 

Message box - Access

 

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:

Test_Field
ABC
DEF

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:

 

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

 

Table in MS Access

 

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

 

Message Box in MS 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 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.