MS Access Tutorial: Calling sub routine in MS Access VBA


Sometime it needs calling sub routine in MS Access VBA on another form or in same form to perform certain tasks on the current intended command execution as a pre-requisite of the process. There might also be needed to call a subroutine on the main from the sub-form. In addition to that calling the sub-routine from another sub-routine within the same form.

Procedure of calling sub routine in ms access:

Lets say the below sub routine reside in the “Main_Form” which will populate the CLASSES table from the STUDENTS table :

Public Sub Populate_Classes()

      Dim a as Integer
      a = 10;
      Dim db As Database
      Set db = CurrentDb
      Dim SQL As String
      Dim qdf As QueryDef

      SQL = "INSERT INTO CLASSES(id,name,dob,class,roll,updatetime)" & _
            " SELECT sid, lname, sdob, sroll, format(Now(),'yyyy/mm/dd') FROM STUDENTS "

      Set qdf = db.CreateQueryDef("", SQL)
      qdf.Execute dbFailOnError

End Sub

  1. Calling the Populate_Classes sub-routine form another sub-routine named as “Execute_First” in the same MS access form “Main_Form”:
    Private Sub Execute_First()
     Call Populate_Classes
    End Sub
  2. Calling the Populate_Classes sub-routine form another sub-routine named as “Calling_From_Another_Form” from another MS access form:
    Private Sub Calling_From_Another_Form()
    End Sub
  3. If the MS Access application is using the Navigation Form:
  • In the Navigation form the “Navigation Button” command calling a form to be loaded on the “NavigationSubform” container. Hence the original form is retaining the Sub-routine but as the “NavigationSubform” is representing the thing so instead of mentioning the original form we mention the “NavigationSubform” in the code.
  • For example, if Populate_Classes is retaining in the “Class” form under navigation button; we will not call as “Forms![Navigation]![Class].Form.Populate_Classes “ rather we will call as below:
  • Private Sub Calling_From_Another_Form()
    End Sub


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.