Category Archives: Access Blog

Database Functions

In this post, we will be taking a look at Database Functions (D-Functions) and providing some examples of their usage. Database Functions are an extremely useful feature in MS Access. They allow you to make quick queries of the database without having to write complicated SQL or open up recordsets and can help you to […]

Logical Operators

Logical Operators Logical operations work with Boolean expressions to yield an answer for expressions. Individually they are quite straightforward but can be combined to create complex expressions. And Operator The And operator requires 2 Boolean values, gives a True answer when both sides of the argument are also True, otherwise False. A logic table demonstrates […]

Multidimensional Arrays

Multidimensional Arrays In Access it is possible to create an array with more than one dimension. Dim myIntegerArray() as Integer ReDim myIntegerArray(7,52) In the above code, we tell Access to create an array of 364 elements (7×52). In the code below, we create a 3×5 array, fill certain elements with values and print it to […]

Updating, Adding And Deleting Records In a Recordset

In this blog post we will be showing you how to update, add and delete records in a recordset. Please download Updating, Adding And Deleting Records Updating A Recordset DAO Sub DAOUpdating() On Error GoTo ErrorHandler ‘This sub-produre will add ‘z’ to the first name of ‘the record that corresponds to TeacherID 5 Dim sql […]

Looping Through a Recordset

In this blog post we will be demonstrating how to loop through a recordset. Recordsets act like a cursor or a ruler underneath a row of data.  They only operate on one row at a time so to access the data returned by the database we must Move the cursor Next or Previous, First or […]

What are Recordsets

In this blog post we will be introducing recordsets and providing some examples of their use. So, what is a recordset? You can think of a recordset as a table or query that we can utilise (read, update, delete, insert) but cannot see. When we open a recordset, the recordset itself is stored in memory […]

DoEvents

In this blog post we discuss the DoEvents method in VBA. DoEvents is a simple command that pauses a loop and allows the operating system to carry out any tasks that have been queued. If you have a loop that can take a significant time to fire, DoEvents enables the loop to pause at periodic […]

Nested Loops

In this blog post we will be discussing nested loops and how they can be used. A loop inside a loop is termed a nested loop. We’ll make a grid of numbers to illustrate. Sub nestedLoop1() Dim y As Integer Dim x As Integer Dim xString As String For y = 0 To 9 ‘We […]

Loops – Do Until / Do While / Do

In this blog post we will be discussing the Do..Until/While loop. The Do…While loop keeps executing the loop as long as a certain condition is met. Sub doWhile1()          Dim i As Long     Dim kitchenItems(0 To 5) As String     ‘We have created an array that can hold 6 elements     […]

Loops – While Wend

In this blog post we take a look at the While…Wend Loop. The basic syntax of a While loop is: While(someExpression) … Wend The loop will continue to operate as long as someExpression is equal to true. When it becomes false, the while loop exits. Sub whileLoopArray()     Dim i As Long     Dim kitchenItems(0 […]

Loops – For Each

In this blog post we will be explaining the For…Each loop and providing some examples of its usage. Demonstrating The For Each Loop with An Array We can use the For Each loop over a standard array. Sub forEachArray()          Dim element As Variant     Dim animals(0 To 5) As String     ‘We […]

Loops – For Next

In this blog post we will introduce the For…Next Loop and provide some examples of its usage. The standard syntax of a For…Next loop is: For counter = start To end … next i In the code above counter is a variable that keeps track of the number of loops performed. start is the starting […]

Working With Form Objects

In this blog post we will be taking a look at how you can work with form objects in MS Access. Using the collection Controls of a form, all controls can be cycled through and only those of a particular type can be targeted.  In the following example all controls of the form are checked […]

Collections

In this blog post we will be discussing collections and how they can be used in VBA. Sub carParts()     ‘A collection is an object that has the ability     ‘to store other objects.     ‘     ‘Collections have 4 methods:     ‘Add: Used to add an item to the collection     ‘Remove: Used to […]

Working With Arrays 2

In this blog post we will be expanding on the capabilities of arrays in VBA. One of the headaches with arrays is that they are static blocks of memory and are not designed to change in size.  If we want to take an array that can hold 10 items and modify it so that it […]

Working With Arrays 1

In this blog post we introduce arrays and give you some examples of them in action. You can think of an array as a row of boxes with a number on each, 0 to n.  When we first declare an array we must at least state its type and may also state its size (we […]

Artithmetic Operators

In this blog post we will be examining Operators and how they can help you to construct if statements in VBA. An expression is a single or collection of variables and operators that ultimately evaluate to True or False.  Here we will list all the arithmetic operators with example code and introduce some operators you’ll […]

The If Statement

In this blog post we discuss the If…Then…Else statements that will allow you to add conditional logic to your posts. The Simple If Statement Sub standardExpressions() Dim a As Integer a = 10 If a = 10 Then Debug.Print “a = 10” ‘The If statement is a very simple statement that asks a ‘straightforward question […]

Compilation Explained

In this blog post we will examine compilation and the effect it has on the code you write. Compilation is the act of converting our human readable code ( VBA) into code the computer understands.  It may also be that your code is compiled into an intermediary format often called object code.  Either way, this […]

Visual Basic Editor Options

In this blog post we take a look at some of the options available in the Visual Basic Editor (press ALT + F11 to open). VBA has a concise set of options and tools which you can set to change behaviour of the editor and debugger.  All are useful tools to help make coding easier […]

Immediate Window

In this blog post we look at how you can use the immediate window whilst coding. The immediate window is a fantastic tool for testing and debugging code. Here are a few simple commands (open the immediate window and type these in directly, then press return): Debug.print “Hello World” Print “Foobarbar” ? “bar foo foo […]

Debugging

In this blog post we will discuss debugging and provide you with some simple tips for debugging your code. In VBA when we write code, it often doesn’t work how we expect it to or we think it is working fine but need to be sure before handing it over to a client. For this […]

Writing A Custom Function

In this blog post we will create a custom function that returns someone’s age based upon their date of birth. To write a custom function we need to consider the following: A returned value is needed, so we must use a function. The value returned will be somebody’s age, so we should return an Integer. […]

Built-In Functions

In this blog post we cover some commonly used built-in functions that VBA coders need to know. String Functions Len(s) – returns the length of String s. Left(s, n) – returns a substring of s that is n chars long from the left of the string s. Right(s, n) – returns a substring of s […]