MS Access Tutorial: Calling sub routine in MS Access VBA

Background:

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()
    
     Forms!Main_Form.Populate_Classes
    
    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()
    
       Forms![Navigation]![NavigationSubform].Form.Populate_Classes
    
    End Sub

 

Leave a Reply

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