In the last Wizard, we reviewed our final product (shown below), which shows the version number and the build date.

One important thing to note is a label control, named lblversion, where we see the version information.

We'll use the table below as the source for putting our version number on the menu.

For more the details about the table and form see the July issue of the Access Wizard.

The startup code for our menu form includes the line:
Me.lblVersion.Caption = GetVersion

Of course the critical element here is that we are manipulating the caption property of our label control by setting it to a function that we call GetVersion.

The getvision code is as follows:

Function GetVersion() As String
Dim dB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim rv As String
Set dB = CurrentDb
strSQL = "Select * from tblVersion order by VersionKey"
Set rst = dB.OpenRecordset(strSQL)
With rst
.MoveLast
rv = !VersionID & "." & !MajorRevision & ! MinorRevision & _
vbCr & vbLf & Format(!dtmBuild, "MMMM D, YYYY")
rv = "Version " & rv
.Close
End With
GetVersion = rv
Set rst = Nothing
Set dB = Nothing
End Function

In essence, this function takes the following steps:

Step 1 - Create variables

dB - the current database
rst - a record set
strSQL - string to hold our SQL statement
rv - a return variable

The rv variable is simply a mechanism we use to simplify the code. We'll ultimately set the version information we're returning to the rv variable, so it's simply a coding convenience.

Step 2 - Set the database, SQL statement, and recordset

Set dB = CurrentDb
strSQL = "Select * from tblVersion order by VersionKey"
Set rst = dB.OpenRecordset(strSQL)

Note here that we are using a SQL statement that has an 'order by' clause. We do this to ensure that our records are in the same order that we entered them into the table. This ensures that when we move to last record in the record set, we'll get the last record created. (See this month's trap for a problem if you do not explicitly set the order of a record set.)

From here, it opens the record set to our table, and then it uses the With/End With convention, which I'll explain in a future Wizard. The critical thing to know is that the "with/end with" structure makes coding faster, as it allows us to skip the "rst" statement before every dot (.) and bang (!) operator.

Step 3 - Manipulate the record set

With rst
.MoveLast

The heart of the code starts with the move last statement, which causes the record set to move to the very last record. The move last works because we explicitly set the order of the table to be the same as our primary key.

Step 4 - Populate our return variable

rv = !VersionID & "." & !MajorRevision & ! MinorRevision & _
vbCr & vbLf & Format(!dtmBuild, "MMMM D, YYYY")
rv = "Version " & rv

This section is the only really complex bit. Once we pull it apart you'll see that it's more exacting than truly tricky. First we have "rv =". This statement says to put whatever follows into our return variable.

Now we engage in a series of concatenation steps, as indicated by the "&" symbol. Essentially, we do the following: Grab what's in the version ID field with the statement !VersionID and then add a period. We add the major version and minor version contents, add a line break/carriage feed with the statements vbCr & vbLf, then add the contents of the build date field. We add a little nicety by formatting the date field in the month/day/year format. (Check out help for info on the Accesses date formats.)

Then we have the line:
rv = "Version " & rv

This line simply prepends the word version onto what we've constructed so far.

Step 5 - Fill the function content with our return variable and clean house

GetVersion = rv
Set rst = Nothing
Set dB = Nothing

Here we take the results of our rv variable and put it into our function name so it gets returned.

With this and the previous Access Wizard, we have shown a method of putting a version and build date onto our opening menu. This will help both the developer and users to know which version of the database they are dealing with.

As with many things in Access, there are a variety of ways to solve the problem of versioning. I like this approach because, once set up, it's easy to maintain and port from application to application. There are certainly easier as well as more complex ways to accomplish this goal. I urge you to play around and find a method that works for you.

The order of records in tables is not static. If you open a table, then choose to sort on a column and save the table, the default sort order is no longer the primary key, but rather whatever have you just sorted on.

For this reason, it's dangerous to assume that your tables will be in an expected order. So, when you're working with a table, whether directly, in a query, or with a SQL statement, if the order of records is important, explicitly set the sort order.