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….!