The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
September 2009

Versioning 102 -- You'll Never Get Where You Want To Be If You Don't Know Where You Are

A tourist was lost in the back woods of Maine. He had taken a series of lefts and rights, and had got himself all turned around and hopelessly lost. He finally came to an old farmer, and said, "Hey, can you help me? I'm lost and trying to get to Route 95."

The farmer said to him "Ayuh. Well the first thing you do is you turn around and go back over the little bridge and then you take..."

At that point, the tourist interrupted the farmer to say, "I didn't come over any little bridge."

With that, the farmer turned and started to walk away. The tourist started yelling, "Hey! Wait! Where are you going?"

The farmer turned and said, "I can't talk to you. If you didn't come over the bridge, you're not here yet."

Although the story is somewhat apocryphal, it makes the point that if you don't know where you are, it makes it very difficult to figure out the best way to get to where you want to be. This month, we'll continue our versioning process and use the table that we created in the last Wizard to bring the current version to our menu form.

In this Issue
  • Tip of the Month - Control What Happens When You Open A Form
  • Recap
  • Code for Manipulating the Label Control
  • Conclusion
  • Trap of the Month - Sort Order of Tables is Dynamic

  • Recap

    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.

    Code for Manipulating the Label Control

    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
    rv = !VersionID & "." & !MajorRevision & ! MinorRevision & _
    vbCr & vbLf & Format(!dtmBuild, "MMMM D, YYYY")
    rv = "Version " & rv
    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

    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.

    Trap of the Month - Sort Order of Tables is Dynamic

    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.

    Tip of the Month - Control What Happens When You Open A Form

    Each form in Microsoft Access has a series of events that take place. Using code, you can manipulate what happens in those events. This month, we used the on-open event to manipulate a caption.

    Not surprisingly, the on-open event occurs every time a form is opened. If you want your form to change depending on what is happening with your application, you can use this event to control elements within the form.

    To do this, take the following steps:

    • Open the form in design view.
    • Click on properties.
    • Click on the events tab.
    • Click on the space to the right of the "on open" line. You'll see 'Choose Builder.'
    • From the builder list, select 'Code Builder.'
    • When you see an ellipsis appear in the right hand side, click on the ellipsis.

    You'll be brought to the code window with preformatted code such as:
    Private Sub Form_Open(Cancel As Integer)

    End Sub

    Put whatever you want within the body of the code to manipulate the opening of your form.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz by jim@custom-software.biz.

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886