ACCESS VBA CODE

Summary:

This tutorial helps orient you to the programming tools i.e, VBA code in Microsoft Access and point you to some resources where you can learn more about programming.

You must have used Access macros and very well know how much complex it is to work with.…..isn’t it? But do you know you can simplify this complexity just by making use of VBA codes in Access.

Now you will ask what this VBA code is and how to use it? Well to get answers of such questions you just need to get stick to this post and get the complete info and simplify your working style in MS Access.

Introduction To Access VBA

VBA is a programming language that you can use to create powerful application in Access. VBA includes numerous of commands that allows you to perform much complex operations that is quiet tougher to do by using the Access macros.

Not only this, you can extend the power of VBA by using the third party controls and writing your own functions/ procedures for own specific needs. You can also use the VBA code to integrate your access with other programs too.

Programming Microsoft Access with VBA can be a lot easier if you know the keyboard shortcuts for the most common commands and the most common bits of code that you will use in the editor after then debug your Access application.

How To Activate The Built-In VBA Functions in Access

Do you know you can activate the built in VBA functions in your Access 2013/2016 application.  Wants to know how then follow these step by step instructions.

In order to use the built in VBA functions in your access  data base , you will need to turn on the visual basic reference called “Visual Basic For Applications”.

  • For this, you just need to open the database. Press Alt+F11 to open the Microsoft Visual Basic  When the Microsoft Visual Basic window appears, select References under the Tools menu.

  • When the Referenceswindow appears, look for the reference called Visual Basic for Applications and make sure that it is checked.
  • After then click on the OK button.

  • Now your access database is able to use the built-in VBA functions.

Common Access VBA Programming And Debugging Code

Here is the list of common VBA code that helps you in performing VBA task completed, so keep it handy.

To Open A Form In Form View:

DoCmd.OpenForm “formname“,acNormal

To Make Changes To The Form Property On An Open Form:

Forms![formName].propertyName = newValue

Get value from a control on an open form:

Forms![formName]![controlName].Value

Change value of a control on an open form:

Forms![formName]![controlName].Value = newValue

Change a control property on an open form:

Forms![formName]![controlName].propertyName = newValue

Close a form, saving changes:

DoCmd.Close acForm, “formName“, acSaveYes

Print a report:

DoCmd.OpenReport “reportName“, acViewNormal

Run an action query:

DoCmd.RunSQL “SQLstatement

To display a simple message

MsgBox “yourMessage

To ask yes/no question onscreen.

variable = MsgBox(“yourMessage“, vbQuestion + vbYesNo)

ALSO READ:

How To Fix MS Access Reserved Error 7713, 7748, 7711 In Access 2016/ 2013/2010/2007

Navigating And Selecting Code In Vba

You can use theses VBA shortcuts in the code window and the immediate window (that let you run code any time, right on the spot) to simplify your working style.

Action Shortcut Key
Move cursor right one character
Select character to right Shift+→
Move cursor right one word Ctrl+→
Select to end of word Ctrl+ Shift+→
Move cursor left one character
Select character to left of cursor Shift+←
Move cursor left one word Ctrl+←
Move cursor to start of line Home
Select text to start of line Shift+Home
Move cursor to end of line End
Select text to end of line Shift+End
Move cursor up a line
Move cursor down a line
Move cursor to next procedure Ctrl+↓
Move cursor to previous procedure Ctrl+↑
Scroll up one screen PgUp
Scroll down one screen PgDn
Go to top of module Ctrl+Home
Select all text to top of module Ctrl+ Shift+Home
Go to bottom of module Ctrl+End
Select all text to bottom of module Ctrl+ Shift+End
Cut selection Ctrl+X
Copy selection Ctrl+C
Paste selection Ctrl+V
Cut current line to Clipboard Ctrl+Y
Delete to end of word Ctrl+Delete
Delete character or selected text Delete (Del)
Delete character to left of cursor Backspace
Delete to end of word Ctrl+Delete
Delete to beginning of word Ctrl+Backspace
Undo Ctrl+Z
Indent line Tab
Outdent line Shift+Tab
Find Ctrl+F
Replace Ctrl+H
Find Next F3
Find Previous Shift+F3
Get help with currently selected word F1
Quick Info Ctrl+I

General VBA Editor Shortcut Keys

The following table will give you the knowledge of the shortcut keys and their functions available throughout the Visual Basic editor, where you will write , modify and run your VBA code.

Action Shortcut Key
Switch between Access and the VBA editor Alt+F11
Help F1
View Object Browser F2
View properties/events F4
View Code window F7
View Immediate window Ctrl+G
View shortcut menu Shift+F10 (or right-click)
Run a Sub/UserForm F5
Stop code execution Ctrl+Break
Close VBA Editor and return to Access Alt+Q

VBA Debugging Shortcut Keys

Code in programming language has to be written exactly, there can be no errors or bugs. If there is any error or bugs then it will show compiling issue. Fortunately, you can debug code in VBA quickly with these debugging shortcuts.

Action Shortcut Key
Toggle breakpoint F9
Step into F8
Step over Shift+F8
Step out Ctrl+Shift+F8
Run to cursor Ctrl+F8
Clear all breakpoints Ctrl+F9

Important Access VBA Code Along with the Examples

ChangeLinkPath

This function is mainly used to change the old path for the linked tables to a new path. The function does this by dropping the linked tables and then relinking it to the same table in the database specified in the new path.

Public Function ChangeLinkPath(strNewPath As String) As String 

Dim dbs As DAO.Database 
Dim strTblName As String 
Dim colTbl As Collection 
Dim intTbl As Integer 

If strNewPath <> “” And Dir(strNewPath) <> “” Then

      Set colTbl = New Collection 
      Set dbs = CurrentDb 

      For intTbl = dbs.TableDefs.Count – 1 To 0 Step -1 
            If dbs.TableDefs(intTbl).Connect <> “” And _ 
                  Not dbs.TableDefs(intTbl).Connect Like “*” & strNewPath Then 
                  colTbl.Add dbs.TableDefs(intTbl).Name 
                  dbs.TableDefs.Delete dbs.TableDefs(intTbl).Name 
            End If 
      Next intTbl 

      For intTbl = colTbl.Count To 1 Step -1 
            strTblName = colTbl(intTbl) 
            DoCmd.TransferDatabase acLink, “Microsoft Access”, _ 
                  strNewPath, acTable, strTblName, strTblName 
            Debug.Print “connection made to ‘” & strTblName & “‘” 
      Next intTbl 

      Set dbs = Nothing 
      Set colTbl = Nothing 
      Debug.Print “DONE!” 
      ChangeLinkPath = “DONE!” 

Else
      Debug.Print “New path not provided. No changes made!”
      ChangeLinkPath = “New path not provided. No changes made!”

End If

Exit Function 
End Function

ImportExportSpecification

This section of the Access VBA contains documentation for all the objects, properties, methods, and events contained in the access object model.

ImportExportSpecification object contain all the information that access needs to repeat an import/export operation without providing any input.

You have to Use the Add method of the ImportExportSpecifications collection to create a new ImportExportSpecification object. Use the Execute method to run saved import or export operation.

ALSO READ:

5 Fixes To Repair Access Database That Is In An Inconsistent State

ConcatenateFieldValues

Well this function is based on a function name concatenate. It is used to create a concatenated string from a single field’s values from the entire records desired to be included in the concatenated string.

The records to be included, plus the field to be used for creating the concatenated string, are given by the SQL statement that is passed to the function.

Passed SQL statement must return only one field in order for this function to work correctly. The value in the concatenated string is given by the string that is provided to the function as a delimiter string.

Public Function ConcatenateFieldValues(pstrSQL As String, _
      Optional pstrDelim As String = “, “) As String

Dim strConcat As String

‘======For ADO comment next 2 lines and =======
‘====== uncomment out ADO lines below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error Resume Next
strConcat = “”
‘======For ADO comment out next 2 DAO lines and =======
‘====== uncomment ADO lines below =======
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

‘======For ADO uncomment next two lines and =====
‘====== comment out DAO lines above and below ======
‘ Dim rs As New ADODB.Recordset
‘ rs.Open pstrSQL, CurrentProject.Connection, _
‘       adOpenKeyset, adLockOptimistic
With rs
      If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                  strConcat = strConcat & .Fields(0) & pstrDelim
                  .MoveNext
            Loop
      End If
      .Close
End With
Set rs = Nothing
‘====== Comment next 2 lines for ADO ========
db.Close
Set db = Nothing
If Len(strConcat) > 0 Then strConcat = _
      Left(strConcat, Len(strConcat) – Len(pstrDelim))
ConcatenateFieldValues = strConcat
Exit Function
End Function

DeleteAllRelationships()

DeleteAllRelationships() function code is basically used to delete all relationships in the current database.

In some circumstances, access database fails to display relations between tables in the relationship window. Since you could not view the relationship nor you  could not delete them, which will take your database to an inconsistent state. So for such cases , this code is provided as a fix.

Function DeleteAllRelationships() As String
‘ WARNING: Deletes all relationships in the current database.
Dim db As Database ‘ Current DB
Dim rex As Relations ‘ Relations of currentDB.
Dim rel As Relation ‘ Relationship being deleted.
Dim iKt As Integer ‘ Count of relations deleted.
Dim sMsg As String ‘ MsgBox string.

sMsg = “About to delete ALL relationships between tables in the current database.” & vbCrLf & “Continue?”
If MsgBox(sMsg, vbQuestion + vbYesNo + vbDefaultButton2, “Are you sure?”) = vbNo Then
DeleteAllRelationships = “Operation cancelled”
Exit Function
End If

Set db = CurrentDb()
Set rex = db.Relations
iKt = rex.Count
Do While rex.Count > 0
Debug.Print rex(0).Name
rex.Delete rex(0).Name
Loop
DeleteAllRelationships = iKt & ” relationship(s) deleted”
End Function

Just copy and paste this function into a module. Press Ctrl+G to open the Immediate Window. Enter:

? DeleteAllRelationships()

Conclusion:

Hopefully, you all must have grabbed much knowledge on Access VBA code. EXPLORE YOUR IDEAS ON PROGRAMMING WITH ACCESS….!

Learn All About Microsoft Access VBA Code – VBA Functions, Shortcuts And Much More…!