Home

Introduction to Record Sets

 
 

A Record Set Object

 

Introduction

After creating a table, you can fill it with records as we have done in the previous lessons. Here is an example:

 

 

We also saw in the previous lessons that you could create a query that selects a few records from a table. The records that a table or a query holds are referred to as a set of records or a record set. To consider all of them as an entity, Microsoft Access provides an object called a Recordset. Most of the time, you use a Recordset object without being aware because the database engine handles all or most of the necessary routines behind the scenes. In some other cases, you must create or initiate a Recordset object in order to use it. To do this, you have various alternatives. In fact, the way you create a Recordset object may depend on the library you are using at the time you need to "grab" the set of records.

 

The Type of Recordset Objects

When creating a record set as we will see in the next few sections, you can specify whether to use one or more tables and what record(s) would be included. How the record set is created, the number of tables, the number of records, and the types of operations that can be performed on the records lead to various types of record sets:

  • If your Recordset object includes only one table, it is referred to a Table record set
  • If a record set includes one or more tables, it is called a Dynaset. This type allows adding, editing, updating, or deleting records that are part of the Recordset object
  • A record set is called a Snapshot if it allows you to view the records of one or more tables, navigating back and forth in the set, but you cannot make changes on the records
  • A Recordset object is referred to as Forward-Only if you can view its records without changing them but you can only move forward. This means that, if you get to a record, examine it, and move to the next record, you cannot refer back to a record you left behind
  • If you create a query based on data from one or more tables and allows the users to navigate back and forth with the ability to make changes to records, this type of record set is referred to as Dynamic.
 

Creating a Recordset Object

Normally, you firstly create a record set by populating a table with the desired values. Besides this technique, we mentioned in the first lesson that there were various libraries you can use to create your database. Each one of these libraries provides a means of creating a record set. To be an effective database developer, you should know how and when to create a Recordset object.

In the strict sense, you cannot visually create a Recordset in Microsoft Access, that is, from the Database window. Instead you must write code. To programmatically create a Recordset object using the Microsoft Access Object Library, you can start by declaring a variable of type Recordset. Here is an example:

Private Sub cmdRecordset_Click()
    ' Create a recordset
    Dim rstVideos As Recordset
End Sub

This is essentially the same technique you use in DAO except that you must declare the variable as being of type DAO.Recordset.

To create a record set using ADO, start by declaring a variable of type ADODB.Recordset. Here is an example:

Private Sub cmdSetOfRecords_Click()
    Dim rstVideos As ADODB.Recordset
    
End Sub

Before using the record set, use the New operator to allocate memory for it. Here is an example:

Private Sub cmdSetOfRecords_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = New ADODB.Recordset
End Sub

After declaring the variable, you must define the source of its records. Of course, you have various alternatives.

 

The Recordset Object of an Object

In previous lessons, we saw that some controls, such as the combo box or the list box, were meant to hold a list of values. We also know that a form or a report is primarily created to show one or more records. Such controls hold their own record set. If you create a Recordset object and want to initialize it with the values held in the form where it is called, you can simply assign it Me.Recordset. Here is an example:

Private Sub cmdRecordset_Click()
    ' Create a recordset
    Dim rstVideos As Recordset
    
    ' Specify that the record set points to the records of this form
    Set rstVideos = Me.Recordset
End Sub

When a form is equipped to display the values of a list, that form has a Recordset object that represents its records. Once again, remember that there are various other ways you can initialize a Recordset object. For example, if you are using ADO, to use the records of the form that is calling it, you can assign Me.Recordset to your Recordset object. Here is an example:

Private Sub cmdSetOfRecords_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = Me.Recordset
End Sub
 

The Clone of a Form's Recordset

We saw that the Windows controls on a form could be used to display data from a table. This is done by specifying a list of values in the RecordSource property of the form. To get the set of records that the RecordSource property of a form holds, you can access its RecordsetClone property.

 

Characteristics of a Record Set

 

The Source

To work on a record set, you must communicate to the database engine what rules your set will follow to be considered as such. In Lessons 8 and 9, we saw how to create a table and its columns. In Lesson 12, we saw how to populate a table with data. We saw that a table in Lesson 10 or a query in Lesson 15 could be used as the record source of a form (or as that of a report). Once the table has been created, it is considered a record set. You can use it as the contents of a Recordset object.

 

The Number of Records of a Record Set

After creating a record set, you may want to know the actual number of records that it contains. This information is stored in a property named RecordCount. On a form, to get the number of records it contains, you can access the RecordCount property of its RecordsetClone object. This could be done as follows:

RecordsetClone.RecordCount

This would produce the total number of records that a form holds. In the same way, the Recordset object of the ADO library provides the RecordCount property.

A Cursor

So far, that is, in the previous lessons, we have been concerned with one database accessed by one user. In many environments, a database will need to be accessed by more than one computer. This means that, when creating a Recordset object, you need to keep different factors in mind. For example, you (actually your user) may be accessing a record or a series of records at the same time with someone else from another computer. In some cases, there may usually be only one person using a database and there might occasionally be someone else but unlikely, although possible. In some other cases, such as on the Internet or in a big enterprise, there might be many people accessing, or trying to access, a database, or a specific set of records, at the same time.

Imagine you are working on a large database such as a bank application that has thousands or millions of records (such as thousands or millions of customers). If you want to perform an operation on the customers, you may have to deal with many or all records. You may also have to deal with the fact that other people are accessing the same records like you, at the same time.

Normally, some operations don't require you to have access to all records, at least not all the time. When working on records, thus when creating a Recordset object, you can specify a way to isolate a range of records and deal only with that range. The range of records that you select is called a cursor.

Because a cursor plays a tremendous role in a record set, there are different options when using it. To support these options, there are various types of cursors:

  • A static cursor holds a constant set of records. Suppose you create a record set and open it. Also suppose that either you only or other people besides you are working on the same record set. You get to a record and start viewing it (or even working on it). After using that record, you move to another record, and you can do this back and forth as you wish. Suppose that, while doing this back and forth navigation (we will learn later on how to programmatically navigate through a record set), another person has accessed a record that is part of your record set and made a change. If using a static cursor, every time you visit the record set, it shows the same records the way they were when you opened the record set. It would not show the changes that have taken place. This is why it is called a static cursor. A static cursor is appropriate if you are not interested to know what changes have taken place ever since you opened the record set.
    In ADO, a static cursor is represented with the adOpenStatic constant.
  • A cursor is referred to as forward-only if it allows you to move forward through the records. Here is how it works. Suppose that you create a Recordset object and specify its cursor. Suppose that, while using the record set, you get to a record that was set as the start point of your cursor. Also, suppose that either you only or other people besides you are working on the same record. If you make a change on the current record, the other people will be notified. If other people make a change on the current record, you also would know. After using that record, you move to the next. With the forward-only cursor, you cannot move back to a record you left already. This means that, even if you are still working on the record set, if there are changes performed on a record you left behind (for example, if another person who is working on the same record changes something on a record that you passed already), you cannot know and you cannot find out because you cannot go back to a record left behind. If this becomes a necessity, you can close the cursor and re-open it. A forward-only cursor is appropriate if you don't need to navigate back and forth among the records of a record set. Because of the way it works, if you access the RecordCount property of a forward-only cursor, it would produce -1.
    In ADO, this type or cursor is represented by the adOpenForwardOnly constant.
  • A cursor is called dynamic if it detects and shows all changes that are occurring in a record set, whether the changes are caused by you or by other people who are accessing the record set at the same time. This type of cursor is appropriate if you want to know, live, what is going on with the record set you are working with.
    In an ADO database, to specify a dynamic cursor, use the adOpenDynamic constant.
  • A key set cursor creates and saves a key for each record that has been modified since the record set was opened. If you access the record, the key is used to check the data of the record set.
    A key set cursor is created using the adOpenKeyset constant.
  • If you don't want to specify a cursor when creating a record set, you can use the adOpenUnspecified constant.

The Connection

In Lesson 4, we introduced database connections and showed different ways of creating on. We have used these techniques ever since Lesson 4. When creating a record set, in some cases, especially when using ADO, you may need to specify what connection the Recordset object would use.

The Lock Type

Imagine that, after creating a record set and working on it, you want to control who else can have access to the records of the set you are using. To exercise this control, you can create a "lock". This allows you, for example, to prevent other people from changing the records until you have finished with them. To support locking, the ADO library provides various options:

  • When a computer connects to a database, its user may need to make changes to various records at the same time, such as deleting a range of records or changing many records at the same time (such as giving a raise to many employees), instead of making one change, then another, then another. For this type of scenario, when the user accesses the records, instead of monopolizing them and waiting for the user to finish an operation that could take long, you can download the records on the user's computer, and disconnect the user from the database. The user would then make the necessary changes. When the user is ready to commit the changes, you can then reconnect to the data source and submit the changes. This type of lock is referred to as batch optimistic. ADO supports this through a constant value named adLockBatchOptimistic
  • You may have a database that a few different people access at the same time. If the database is small enough, which is the case for restricted environment, the likelihood of two people editing or updating the same record (at the same time) may be low. In this case, you can indicate that you want to lock the record only when necessary. In this case, you use what is referred to as optimistic locking. This is implemented using the adLockOptimistic value
  • The above two options assume that you would lock many records to apply the indicated scenarios. If you prefer to lock one record at a time, you can use what is referred to as pessimistic locking. This is done using the adLockPessimistic constant
  • The above three scenarios allow a user to edit and/or update the records that are included in the set. In some cases, you may want to prevent any editing or update on the records while the set is being accessed. In this case, you can set the records to read-only. To do this, you can lock the set using the adLockReadOnly lock
  • If you don't want to specify the type of lock system to use on a record set, use the adLockUnspecified value

Opening a Record Set

 

Using the Microsoft Access Object Library or DAO

With the Microsoft Access Object Library or DAO, to create a Recordset object using a table or a query that is associated with a database, you can call the OpenRecordset() method of that database. The syntax of this method is:

Set Variable = Database.OpenRecordset(Source[, Type [, Options [, Lockedits ]]])

The Variable factor can be an Object variable you would have declared as a placeholder for a Recordset object. The Database factor must represent a valid database. It can be the current database or another one. the only required argument of this method is the Source, which is passed as a string. This can be the name of a table or a query. Here is an example:

Private Sub cmdGetVideos_Click()
   Dim dbVideoCollection As Object
   Dim rstVideos As Object

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("Videos")

End Sub

When this method executes, it retrieves the records stored in a table or a query named Videos and creates a record set from it.

Instead of a whole table or query, that is, instead of including all columns of a table or query, you may want to select only one or a few columns for the record set. To do this, create a SELECT SQL statement and pass it to the OpenRecordset() method. Here is an example:

Private Sub cmdGetVideos_Click()
   Dim dbVideoCollection As Object
   Dim rstVideos As Object

   Set dbVideoCollection = CurrentDb
   Set rstVideos = dbVideoCollection.OpenRecordset("SELECT Title, " & _
                            "CopyrightYear, Rating FROM Videos")
End Sub

This time, only a few columns would be considered in the Recordset object.

Using a Table or a Query

The above code supposes that you would go through a database to create a record set. It can be used to create a record set from the current database or from a closed database. If you are working in a database that has its own objects and you want to create a record set using one of these objects, you don't have to pass by the database object. Each database object that can act as a data source, including tables and queries, is equipped with an OpenRecordset() method. Its syntax is:

Set Variable = object.OpenRecordset([Type [, Options [, Lockedits ]]])

Notice that, this time, you specify neither the database nor the name of the object. Here is an example

Private Sub cmdRstNames_Click()
    Dim curDatabase As Object
    Dim rstCustomers As Object
    Dim tblCustomers As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblCustomers = curDatabase.TableDefs("Customers")
    ' Create a Recordset object from the specified table
    Set rstCustomers = tblCustomers.OpenRecordset
End Sub

When this code runs, it retrieves the records of a table named Customers and creates a record set from it.

Using ADO

Consider the following table:

Earlier, we saw that, when creating a record set, you may need to take various factors into consideration. To consider those factors, the Recordset object of the ADO library is equipped with the Open() method. Its syntax is:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

All arguments are optional. Instead of calling it and provide the argument, you can first specify each factor using the appropriate property as we will see shortly. Then, after specifying the properties, you can call the method without any argument using the following syntax:

recordset.Open

The recordset factor can be a Recordset variable that you have previously declared. The first argument, Source, can be a SQL statement that will be used to select the records. Here is an example:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
End Sub

The source can also include an optional WHERE condition that specifies what records would be considered.

Instead of calling the Open() method version that expects the argument, you can first specify the source of data. To support this, the Recordset object is equipped with a property named Source that is of type String and that can receive the string of the data source. Here is an example of using it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    
    rstVideos.Open
End Sub

The second argument of the Recordset.Open() method, ActiveConnection, specifies the connection through which the record set will be accessed. It can be a connection as those we have seen in the previous lessons. Here is an example that uses the connection to the current database:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection
End Sub

Instead of passing the connection to the Recordset object, you can specify it as its own object. To support this, the Recordset object is equipped with a property named ActiveConnection and that is of type Connection. It also can be a valid connection as those we have used so far. Here is an example:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    . . .
    rstVideos.Open
End Sub

The third argument specifies the type of cursor that will manage the access to the record set. The available cursors that we reviewed earlier are members of an enumerator (a list of constant values) named CursorTypeEnum. The cursor argument can hold one of the values of cursors we saw earlier. Here is an example:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic
End Sub

If you want, you can specify the cursor separately. This is possible because the Recordset object provides a property named CursorType that is of type CursorTypeEnum. Here is an example of using it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    
    rstVideos.Open
End Sub

The fourth argument, LockType, represents the type of locking system that will be applied on the record set. The available types are stored in an enumerator named LockTypeEnum. The members of this enumerator are those we reviewed earlier. Here is an example of passing the fourth argument:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
End Sub

The last argument is used to identify the type of the Source argument. The values of this argument are members of an enumerator named CommandTypeEnum.

If the source is a SQL statement as we have used it so far, this argument can be pass as adCmdText. Here is an example:

Private Sub cmdAnalyzeVideos_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "SELECT Title, Director, CopyrightYear, Rating FROM Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdText
End Sub

So far, we have specified the Source factor as a SQL statement. ADO allows you to use the name of a table as the source. If you do this, the database engine would complete the Source with a SELECT statement. For example, if you pass the source as a table named Videos, the database engine would convert it into

SELECT * FROM Videos;

If you pass the Source factor as the name of a table, then the last argument of the Open() method can be passed as adCmdTable. Here is an example:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
End Sub

Closing a Record Set

After using a Recordset object, you should (strongly) close it. To close a record set, you can call its Close() method. Here is an example:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
    
    rstVideos.Close
End Sub

After closing the record set, you should release the resources it was using and make them available to other applications that would need them. To do this, assign the Nothing value to the Recordset object. Here is an example:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

In Lesson 4, we saw that, after using a connection, you should also close it by calling its Close() method and you should release its resources. If you were using the ActiveConnection property to connect to the current database, to close it, the Recordset object is equipped with a property named ActiveConnection. Before closing the Recordset object, you should first assign Nothing to its ActiveConnection property.

The Fields of a Recordset

 

The Fields Collection

Any of the techniques we have used above, whether using the OpenRecordset() method of an object, a SQL statement or the name of a table passed to the Open() method of a Recordset object, is used to select the records. The records are primarily identified by the columns that organize them., To recognize the columns of a record set, each Recordset object of the libraries we have so far is equipped with a property named Fields, which is a collection of the columns of the record set.

To visit a Fields collection, you can use a For Each...In...Next conditional statement.

A Field of the Fields Collection

Each column of the Fields collection is an object of type Field, which is the common name of a column in database systems. In our introduction to columns in Lesson 9, we saw that a name was its most fundamental characteristic. To identify the name of a column, the Field object is equipped with a property named Name. Here is an example of accessing it:

Private Sub cmdVideoData_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Open "Videos", _
                   Application.CodeProject.Connection, _
                   adOpenStatic, adLockOptimistic, adCmdTable
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Name
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

When this code executes, it will display the name of each column in a message box, one at a time.

The Value of a Record

In Lesson 12, we saw that a record was created by entering a value under a column in a table or in a Windows control of a form. To identify the value held by a column, the Field object is equipped with a property named Value. Here is an example of using it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    rstVideos.LockType = adLockOptimistic
    rstVideos.Open
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Value
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

When this code executes, it visits each column, retrieves its value, and displays it in a message box.

Record Navigation in a Record Set

 

Introduction

Navigating through a record set consists of visiting its records. This is similar to using the navigation buttons at the bottom of a table, a query, a form or a report. We saw how to do this manually. We also saw how to do this programmatically using the DoCmd object. The Recordset object of the ADO library supports record navigation through various methods.

Moving Among Records

Whenever performing an operation on a record, you should know your position in the set. Whenever in doubt, you can reset your position by moving to the first record. To support this, the Recordset object is equipped with a method named MoveFirst. This method takes no argument. Here is an example of calling it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    rstVideos.LockType = adLockOptimistic
    rstVideos.Open
    
    rstVideos.MoveFirst
    
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Value
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

Besides the first record, another extreme position you can move to is the last record. To do this, you can call the MoveLast() method of the Recordset object.

To move from one record to the next, you can call the MoveNext() method of the Recordset object. Here is an example of calling it:

Private Sub cmdVideoAnalyze_Click()
    Dim rstVideos As ADODB.Recordset
    Dim fldEach As ADODB.Field
    
    Set rstVideos = New ADODB.Recordset
    rstVideos.Source = "SELECT Title, Director, CopyrightYear, Rating FROM Videos"
    rstVideos.ActiveConnection = Application.CodeProject.Connection
    rstVideos.CursorType = adOpenStatic
    rstVideos.LockType = adLockOptimistic
    rstVideos.Open
    
    rstVideos.MoveFirst
    rstVideos.MoveNext
    For Each fldEach In rstVideos.Fields
        MsgBox fldEach.Value
    Next
    
    rstVideos.Close
    Set rstVideos = Nothing
End Sub

When this code executes, the record position is first moved to the first. Then it immediately moves to the second record. Then it visits each column, retrieves its value corresponding to the second record and displays it in a message box.

To move to the previous record in the set, call the MovePrevious() method of the Recordset object.

The MoveFirst() and MoveLast() methods allow you to navigate one record at a time until you get to a certain record. If you are positioned at a certain record and you want to jump a certain number of records ahead or you want to move back by a certain number of records, you can call the Move() method. Its syntax is:

recordset.Move NumRecords, Start

The first argument is required. Its specifies the number of records to jump to. If you pass a positive value, the position would be moved ahead by that number. Here is an example:

Private Sub cmdEditRecord_Click()
   Dim dbCustomers As Object
   Dim rstCustomers As Object

   Set dbCustomers = CurrentDb
   Set rstCustomers = dbCustomers.OpenRecordset("Customers")

   rstCustomers.Move 4
End Sub

When this code executes, it would jump 4 records ahead of the current record of a table named Customers. You can also pass a negative value. In this case the position would be moved behind the current record by the value passed. If the record set doesn't contain any record when you call the Move() method, you would get a 3021 error:

Don't Move Beyond the Extremes

Some, if not most operations require that you remain within the range of values of the record set. If you move below the first record record, you (actually the user) may receive an error. In the same way, if you move beyond the last record, you would receive an error. To assist you with checking whether you are in the first record, the Recordset object provides the BOF() method. This method returns a Boolean value as follows:

  • If it returns TRUE, then you are currently positioned before the first record
  • If it returns FALSE, then you are at or above the first record

On the other hand, if you want to check whether you are at the highest position of the records, you can call the EOF() method of the Recordset object. It also returns a Boolean value as follows:

  • If it returns TRUE, then you are currently positioned after the last record
  • If it returns FALSE, then you are at or below the last record

Practical LearningPractical Learning: Navigating Among Records

  1. Open the ROSH database
  2. Open the Students form that you were working on in Lesson 15 and switch to Design View
  3. Heighten the Form Footer section to create some space below the existing controls.
    You will design it as follows:
     
  4. On the Toolbox, click the Combo Box and click to the left under the SortBy combo box. If the Combo Box Wizard starts, click Cancel
  5. Set the caption of the accompanying label to Show Records Where the:
  6. Change the following properties for the combo box:
    Name: cboColumnNames2
    Row Source Type: Value List
  7. Make sure the Control Wizard button of the Toolbox is down. On the Toolbox, click the Combo Box and click on the right side of the previously added combo box in the Form footer section
  8. In the first page of the Combo Box Wizard, click the second radio button (I Will Type In The Values That I Want) and click Next
  9. Click under Col1 and type =
  10. Press the down arrow key and type <>
  11. Press the down arrow key and type <
  12. Press the down arrow key and type <=
  13. Press the down arrow key and type >
  14. Press the down arrow key and type >=
     
  15. Click Next
  16. Accept the first radio button and click Next
  17. Click Finish
  18. In the Properties window, change the name of the new combo box to cboOperators
  19. Change the Column Widths and the List Width values to 0.35
  20. Click the Default Value field and type "="
  21. Delete the accompanying label of the new combo box
  22. On the Toolbox, click Combo Box and click on the right side of the newly added combo box. If/When the Combo Box Wizard starts, click Cancel
  23. In the Properties window, change the name of the new combo box to cboValues
  24. Set its Row Source Type to Value List and delete its accompanying label
  25. On the Toolbox, click Command Button and click on the right side of the previously added combo box
  26. Change its properties as follows:
    Name: cmdSubmitFilter
    Caption: Submit Filter

     
  27. On the Toolbox, click Text Box and click just on top of the previously added combo box
  28. Delete its accompanying label and change its properties as follows:
    Name: txtRegularString
    Visible: False
     
  29. Save the form
  30. On the form, click the lower left combo box
  31. In the Events tab of the Properties window, double-click After Update and click its ellipsis button
  32. Implement it as follows:
     
    Private Sub cboColumnNames2_AfterUpdate()
        On Error GoTo cboColumnNames2_Err
        
        Dim strValues As String
        Dim rstStudents As ADODB.Recordset
        Dim conStudents As ADODB.Connection
        Dim iCounter As Integer
        
        Set rstStudents = New ADODB.Recordset
        Set conStudents = CurrentProject.Connection
        
        rstStudents.Open "SELECT " & [cboColumnNames2] & " FROM Students", conStudents, adOpenKeyset, adLockOptimistic
        
        ' If the user selects a MI, display each letter of the alphabet
        ' in the Values combo box
        If cboColumnNames2.Text = "MI" Then
            For iCounter = 65 To 90 Step 1
                strValues = strValues & Chr(iCounter) & ";"
                Me.cboValues.Visible = True
                Me.cboValues.Enabled = True
                Me.cboOperators.Enabled = True
                Me.txtRegularString.Visible = False
            Next
            
        ' If the user selects DOB, then hide the Values combo box
        ' and display the text box so the user can enter a date
        ElseIf cboColumnNames2.Text = "DOB" Then
            Me.cboValues.Visible = False
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = True
            Me.txtRegularString.Enabled = True
        
        ' If the user selects Gender, then
        ' enter only the available options
        ElseIf cboColumnNames2.Text = "Gender" Then
            strValues = "Female;Male;Unknown;"
            Me.cboValues.Visible = True
            Me.cboValues.Enabled = True
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = False
        
        ' Addresses are too long
        ElseIf cboColumnNames2.Text = "Address" Then
            Me.cboOperators.Enabled = False
            Me.cboValues.Enabled = False
            Me.txtRegularString.Enabled = False
    
        ' If the user selects State, then
        ' enter only the state close to Washington, DC
        ElseIf cboColumnNames2.Text = "State" Then
            strValues = "DC;MD;VA;WV;"
            Me.cboValues.Visible = True
            Me.cboValues.Enabled = True
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = False
            
        ' If the user selects ZIPCode, then hide the Values combo box
        ' and display the text box so the user can enter a ZIP Code
        ElseIf cboColumnNames2.Text = "ZIPCode" Then
            Me.cboValues.Visible = False
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = True
        
        ' For all the other text-based options
        Else
            Do
                strValues = strValues & rstStudents.Fields(cboColumnNames2.Text) & ";"
                rstStudents.MoveNext
            Loop While Not rstStudents.EOF
            
            Me.cboValues.Visible = True
            Me.cboValues.Enabled = True
            Me.cboOperators.Enabled = True
            Me.txtRegularString.Visible = False
        End If
        
        cboValues.RowSource = strValues
        
        Exit Sub
    
    cboColumnNames2_Err:
        MsgBox "There was an error when filtering the records." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please contact the program vendor if " & _
           "he is not sleeping at this time."
        Resume Next
    End Sub
  33. Return to the form and click the lower right button
  34. In the Events tab of the Properties window, double-click On Click and click its ellipsis button
  35. Implement it as follows:
     
    Private Sub cmdSumbmitFilter_Click()
    On Error GoTo cmdSumbmitFilter_Error
    
        Dim strFilter As String
        
        ' If the user selected DOB, then the filter should use # symbols
        If cboColumnNames2 = "DOB" Then
            strFilter = "" & cboColumnNames2 & " " & cboOperators & "#" & Me.txtRegularString & "#"
    
        Else
            strFilter = "" & cboColumnNames2 & " " & cboOperators & "'" & cboValues & "'"
        End If
        
        Me.Filter = strFilter
        Me.FilterOn = True
        
        Exit Sub
        
    cmdSumbmitFilter_Error:
        MsgBox "There was an error when filtering the records." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Description: " & Err.Description & vbCrLf & _
               "Please contact the program vendor."
        Resume Next
    End Sub
  36. Return to Microsoft Access and switch the form to Form View
  37. Save the form
  38. Try filtering the records
  39. Close the form
 

Previous Copyright Yevol, 2007 Next