VBA - MS Access

Visual Basic for Applications (VBA) runs behind Microsoft Access, Excel, PowerPoint, and Word. I have specifically used it with MS Access. This page has some code examples that can be used or further developed for your specific needs.


Two ways to view VBA IDE (Integrated Development Environment):
1) Click on Database Tools, click on VBA in the Macro group
2) Open the Properties window for an object, click on the Events tab, click in any event, and then click the ellipsis that will appear. This will open a window, click on Code Builder and then click OK.


VBA can be written either within the class object that will open on an event property or within a module. For me, modules seem to work best for processes that will run multiple times within a database. Modules can be written for a Function or Sub Routine and called from a class object.


Add a comment in VBA
Simply mark your line with apostrophe: 


'This is proper format for a VBA comment


If you write some code and aren't sure you want to use it, it's buggy, etc. Comment out all code by selecting it and then clicking the Comment Block button. This can be found if you click on View/Toolbars/Edit -- then find the Comment Block button. Code blocks can also be un- commented!


DoCmd
This is the code that will help you do a lot -- open, close forms, reports, run queries, etc.


To open a form - 
DoCmd.OpenForm "frmName", acnormal

To close a form - 


To run a Query or SQL statement -
DoCmd.RunSQL " qryName"




Recordset
A recordset can be used search through a selection of records one by one, an update, deleted, or edit parts of records if they meet certain criteria, or just hold sets of data.








QueryDef
Now this is awesome! If you have a query that is the record source of your form or report you can set a query def in VBA. This adjusts the criteria that you want to show in a form or report and really comes in handy! :) What i've found though, if your code breaks in the middle of the query def,  its important to remember that the VBA deletes the query and recreates it. I've had to re-create the query def query that sits in the navigation pane in order to have my form open/close properly due to broken code or error in my code.








ListBox
Show a box of information, use single or multi-select.




Button
Click-Event!




What Event should I use?