VBA

Overview

  • Access Database Tables Manipulation with VBA

    Sub ReadQuantity() 'Variable Declarations 'Orders is database table name Set myR = CurrentDb.OpenRecordset("Orders") 'Loop Could be used to iterate over column record = myR![Quantity] End Sub Read Values of Whole Column 'Make sure their are no null values in the table Private Sub Command26_Click() Dim myR As Recordset Set myR = CurrentDb.

  • Add VBA Procedure to Button

    Chrome dev tools pannels Chrome dev tools pannels Chrome dev tools pannels Chrome dev tools pannels

  • Comments in VBA

    'this is comment

  • Conditions in VBA

    Option Compare Database Sub IfTest() Dim curTotal As Currency, curComm As Currency curTotal = InputBox("Please Enter the Sale Amount") if curTotal >= 10000 Then curComm = curTotal * 0.08 ElseIf curTotal >= 1000 Then curComm = curTotal * 0.

  • Data types in VBA

    Data Type Description Byte 0 to 255 Boolean True or False(1 or 0) Integer -32768 to 32767 Long (long integer) -214,483,648 to 2147,483,647 Single (Six decimal places) Double (Fourteen decimal places) Currency (Four decimal places) Decimal (28 places to right of the decimal point0) 7.

  • Debugging in VBA

    Step Through a Subroutine function use the debug menu options or shoucuts like F8 Set Breakpoints Immediate Windows in Code window Hower over variable name or write following statement Debug.Print lngResult

  • Error Handling in VBA

    Sub CheckError() 'On Error GoTo 0 'On Error Resume Next On Error GoTo Handler: Dim longResult As Long, longgInteger As Long longInteger = InputBox("Enter Your Number") longResult = longInteger + 3 MsgBox(longResult) Exit Sub Handler: MsgBox("Not a Number") End Sub

  • Functions in VBA

    Option Compare Database Function MultiplyPi(meter As Double) MultiplyPi = meter * 3.15 End Function Sub GetCirc() Dim userValue As Double userValue = InputBox("Enter your number") MsgBox (MultiplyPi(userValue)) End Sub

  • Loops in VBA

    Option Compare Database Dim iCaseCount As Integer, curCost As Currency curCost = 47 'For iCaseCount = 5 to 10 'For iCaseCount = 5 to 10 Step 2 For iCaseCount = 10 to 5 Step -2 MsgBox(iCaseCount & " Cases Costs PKR" & curCost * iCaseCount ) Next 'iCaseCount Loop Over Collections/Arrays Database Object organise everything in collections.

  • Manipulate Database Objects Using DoCmd in Microsoft Access

    Open Form Private Sub SaleOrdersDesignViewButton_Click() DoCmd.openForm "Sale Orders", acDesign, , , acFormPropertySettings End Sub Open Report Private Sub printPreviewReport_Click() DoCmd.OpenReport "Sale Orders", acViewPreview, , , acWindowNormal End Sub Open Table Private Sub SaleOrdersDesignViewButton_Click() DoCmd.

  • Subroutines

    Subroutines and functions are not same, generic name for both is procedures. Subroutines operates internally but does not return a value. Below is an example of code in which two subroutines are created one has data and other show this in dialog box.

  • VBA IDE

    Open IDE To open visual basic IDE first open MS Access Database and then press alt+F11. Create Module Insert -> Moudle Object Browser Press F2 and search for objectname of your choice.

  • What is VBA

    VBA is a programming language, programming language is system of statements that are used to control computer. Accept store data manipulate produce output Object Oreiented programming Properties methods Events Access Object Model Its absolutely huge, hundreds and thousandas of objects, also their huge properties and methods.

  • Scope of Variables Local Variables Global Variables Persist Values of Variables Simple Variables Constant Variables Static Variables Object Variables mainipulate object of databases by assigning object to variables. 'load the form first Dim saleOrders As Form Set saleOrders = Forms("Sale Orders") MsgBox saleOrders.

I will get back to you ASAP.