I am trying to automate a record ID field during the click event of a save
command button.  The record ID field is a concantenation of two other fields
on the form along with an integer component.  My code is not returning the
correct integer value.
I have tried the following code:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    Dim varProduct As String            'Product name field.
    Dim varVersion As String            'Product Version field.
    Dim varOriginalNum As String    'TestCaseID field
    Dim varNum As Integer              'Right five digits from TestCaseID
field
    Dim rst As DAO.Recordset
    Dim strSQL As String

    Set rst = Me.Recordset            'Set the recordset to the recordset of
the form.

    strSQL = "select max(TestCaseID)"
    'How do I run this statement and store the returned value as a variable?

    varProduct = Left(txtProductName, 2)
    'Strip off the right 2 characters from the txtProductName field.

    varVersion = cboVersion
    'Set the varVersion to the value of the cboVersion field value.

    varOriginalNum = Right(rst!TestCaseID, 5)
    'Strip off the right five characters of the TestCaseID field.  This
needs to come from the max value (strSQL).

    varNum = Val(varOriginalNum)
    'Convert the five characters that were stripped off of the TestCaseID
field to an integer.

    If Not TestCaseID Then
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
        'If TestCaseID has content then save the record.
    ElseIf txtProductName = "" Then
        MsgBox "Please choose a product."
        'If the txtProductName field is empty then display the message
    Else
        TestCaseID = varProduct & varVersion & "-" & varNum + 1
        'Set the value of the TestCaseID field to the product name (first
two letters) plus the version plus dash (-) plus
        'the number ID incremented by 1.
    End If

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'Save the record

Exit_cmdSave_Click:
    Exit Sub

Err_cmdSave_Click:
    MsgBox Err.Description
    Resume Exit_cmdSave_Click

End Sub
-------------------------------------
Thanks,
CB