Setting a variable to the largest record value. 
Author Message
 Setting a variable to the largest record value.

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
    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, ,
        '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
        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 Sub

    MsgBox Err.Description
    Resume Exit_cmdSave_Click

End Sub

Sun, 10 Mar 2002 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. How to set default value of combo box to value of record in database

2. Need VERY large numeric variables (maybe as large as 1MB)

3. Inserting large number of records into a large table with SQL/DAO

4. Save a record value as a variable to be used in a function

5. Set Variable = Field Value

6. Setting Bookmark to Variable value

7. Create an AutoTextEntry with it's value set to a document variable

8. Set many "values" to variables

9. How do I set a static variable value?

10. Set a text file as variable value

11. Setting the value of a server variable from a client script

12. Setting variable values


Powered by phpBB® Forum Software