Access Database VBA Detect Shift Key on Button Click

While working on a Time Logging database, I wanted to develop a simple way for the user to add a second entry without adding another button to the form.  Let’s say that 99% of the time there will be just one entry, so having the extra button would get in the way of a clean look.  Well, at least in my opinion.  At the same time, I did not want to trouble the user by having them leave the input form.  My solution was to enable the user to hold down the shift key, which would cause the save button to save the current record and automatically reset the form for a new entry.  Normally, the save button would save and close the input form.

The VBA Code

If you want to follow this example, start with a blank form and create a command button cmdTest.

The VBA code provided below is simple and to the point.  Basically, it will detect if the Shift key is pressed and run alternate code.

Shift Click VBA Code

Step 1:  create a public variable to store the status of the Shift key as TRUE or FALSE.

Dim boolShift as Boolean

Step 2: add MouseDown code to the cmdTest button.  This will update the public variable with the status of the shift key each time the user clicks on the button.

Private Sub cmdTest_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
”This is the actual status check for the Shift Key status used in the Click event.
”Set shift button status…  Place a negative (-) in front to make it True/False.
boolShift = -Shift

End Sub

Step 3: Now, it is time to make use of the public variable and build the cmdTest click code.  In the previous step, we set the public variable to the current shift key state.  In this step we will look at that variable and run code based on if the user is holding down the  shift key.

Private Sub cmdTest_Click()
”Determine the user intent based on the pressing of a shift key.
If boolShift = True Then
MsgBox “Shift Key was held Down during Button Click”
Else
MsgBox “Shift Key was NOT detected during Button Click”
End If
End Sub

Step 4:  This step is optional, but I find that it is helpful to let the user know what is happening.  For this example we will change the caption text of the command button based on the status of the shift key.  When the user moves the mouse over the cmdTest button code will run to determine the status of the shift key.  If the shift key is pressed the text will change to “Test + SHIFT” to let the user know something different will happen when the button is clicked.

Private Sub cmdTest_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
”Set Shift button status
boolShift = -Shift

”This is for visually changing the command button text.
If boolShift = True Then
Me.cmdTest.Caption = “Test + SHIFT”
Else
Me.cmdTest.Caption = “Test”
End If
End Sub

 

Finished Product

Below is an example of this code in action.

Updating a Website from Access using VBA and FTP

Access Database Update Website Button
Access Database Update Website Button
Access Database Update Website Button

I have a few personal databases that I’ve developed in which I export files and upload to my website.  When I was using my ISP’s free hosting this was never an issue as I could just select the FTP site saved in My Computer.  Well time has past and my hosting needs changed and I wound up with GoDaddy hosting.  I was soon faced with the reality that a real hosting service was quite different from what I was accustomed to with my free ISP’s web space.  My major problem was my databases were no longer automatically saving the exported files to the web server.  I need a new way of updating a website from Access using VBA and FTP.

 

My first route tried to use Windows ftp.exe, but that does not play nice when passive (PASV) file transfer is required as is the case with GoDaddy.

 

I wanted to be able to click one button within my database to sent the updates to the website.  In this case there were 4 .xml files.  I previously had the code export the xml files to a temp directory so I just needed to find a solution that I could automate the upload process within Access using VBA.  Thanks to my dear friend, Google, I found NcFTP, a command-line FTP utility, which can be automated in the same way as Windows’ ftp.exe.  In my case this worked perfectly with my database and GoDaddy hosting.

 

The first step is to download and install the NcFTP Client (direct download)

Then head back to Microsoft Access and modify your VBA code.  My code begins by exporting the 4 .xml files to a temp directory.  I had a choice, I could tell NcFTP to upload each file or upload all files in a directory.  I choose the later as I wanted my entire website’s data to updated each time there is a change in the database.

Here is the VBA code I used:

Shell “ncftpput -u [USERNAME] -p [PASSWORD] [FTP.SITENAME.COM] [RemoteDestinationFolder] [LocalFile2Upload]”, vbNormalFocus

I included vbNormalFocus to show the NcFTP window during the upload process.  This can be hidden, but I prefer to see it working.

NcFTP Put status window
NcFTP Put status window

 

For the Local file to upload you can specify one file ‘C:\db_temp\filename.xml or a an entire directory C:\db_temp\*

 

Here is an example of how the command line would look if you wanted to upload all files in C:\db_temp to ftp.sitename.com to the remote directory sitename.com/data/

ncftpput -u USERNAME -p PASSWORD FTP.SITENAME.COM data/ C:\db_temp\*

 

There are a few way to implement this code.  For my database I created a table to store my site information less the password.  When the Update website button is clicked the files are exported, the command line is generated and the user is promted from any site credentials not stored in the table.  The files are uploaded from the temp directory.  Finally, the local temp files are deleted.