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:
To Make Changes To The Form Property On An Open Form:
Forms![formName].propertyName = newValue
Get value from a control on an open form:
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:
To display a simple message
To ask yes/no question onscreen.
variable = MsgBox(“yourMessage“, vbQuestion + vbYesNo)
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.
|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 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|
|Get help with currently selected word||F1|
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.
|Switch between Access and the VBA editor||Alt+F11|
|View Object Browser||F2|
|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.
|Run to cursor||Ctrl+F8|
|Clear all breakpoints||Ctrl+F9|
Important Access VBA Code Along with the Examples
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
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 & “‘”
Set dbs = Nothing
Set colTbl = Nothing
ChangeLinkPath = “DONE!”
Debug.Print “New path not provided. No changes made!”
ChangeLinkPath = “New path not provided. No changes made!”
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.
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
If Not .EOF Then
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
Set rs = Nothing
‘====== Comment next 2 lines for ADO ========
Set db = Nothing
If Len(strConcat) > 0 Then strConcat = _
Left(strConcat, Len(strConcat) – Len(pstrDelim))
ConcatenateFieldValues = strConcat
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”
Set db = CurrentDb()
Set rex = db.Relations
iKt = rex.Count
Do While rex.Count > 0
DeleteAllRelationships = iKt & ” relationship(s) deleted”
Just copy and paste this function into a module. Press Ctrl+G to open the Immediate Window. Enter:
Hopefully, you all must have grabbed much knowledge on Access VBA code. EXPLORE YOUR IDEAS ON PROGRAMMING WITH ACCESS….!