Subscript out of Range, TypeMismatch, what am I doing wrong!!

[RodgerDJr]

All,

I have created an audit trail form my database, which seems to work sometime and then other times I just cannot figure why I am getting errors. The error that I am geting right now is Runtime Error 9 Substring Out of Range. Another error I am getting is Runtime Error 13 Type Mismatch.

The line I am getting an error on is: (myHistory)

ElseIf Nz(myValue, 0) <> myArray(X) Then

I have two modules that I will list below.

myCurrent runs on the OnCurrent Event for the main form and all the subforms.

myHistory runs on the BeforeUpdate Event for the main form and all the subforms.

Function myCurrent(myForm, mySubForm)
    Dim myText As Control, C As Control, X
    Dim form1 As Form, form2 As Form
 
    If Nz(mySubForm, " ") >= " " Then
        Set form1 = Forms(myForm)
        Set form2 = form1(mySubForm).Form
    Else
        Set form2 = Forms(myForm)
    End If
 
    ReDim myArray(form2.Controls.Count - 1)
 
        X = -1
        For Each C In form2.Controls
 
        X = X + 1
        Select Case C.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup 'Skip Updates field.
                'If C.Name = "Updates" Then GoTo TryNextC
            myArray(X) = C.Value
        End Select
 
TryNextC:
    Next C
 
    Set form1 = Nothing
    Set form2 = Nothing
End Function
 
Public Function myHistory(myForm, myID, mySubForm)
    Dim D As Control, form1 As Form, form2 As Form
    Dim myDB, myRS, myNewRecord, myTable, myValue, myArrayValue
    Dim X As Integer
    Set myDB = CurrentDb()
    Set myRS = myDB.OpenRecordset("HISTORY")
 
    'Check each data entry control for change and record old value of Control.
    'Set the Array Counter
 
    If Nz(mySubForm, " ") >= " " Then
        Set form1 = Forms(myForm)
        Set form2 = form1(mySubForm).Form
    Else
        Set form2 = Forms(myForm)
    End If
 
    X = -1
    For Each D In form2.Controls
    ' Only check data entry type controls.
    X = X + 1
        Select Case D.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton
               ' Skip Updates field.
                myValue = D.Value
 
            'If D.Name = "Updates" Then GoTo TryNextD
                If form2.NewRecord = True Then
                    myNewRecord = "New Record"
                        myRS.AddNew
                            myRS![HIS_USER] = useUserName
                            myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
                            myRS![HIS_FIELD] = D.Name
                            myRS![HIS_FORM] = form2.Name
                            myRS![HIS_TABLE_ID] = myID    'CHANGE THIS
                            myRS![HIS_TABLE_NAME] = form2.RecordSource
                            myRS![HIS_OLD_VALUE] = "This is a new record"
                            myRS![HIS_NEW_VALUE] = D.Value
                            myRS![HIS_DATE_CHANGE] = Date
                            myRS![HIS_TIME_CHANGE] = Time()
                        myRS.Update
                    GoTo TryNextD 'Exit Sub
                End If
 
                ' If control was previously Null, record "previous value was blank."
                'myArrayValue = myArray(X)
                If IsNull(Array(X)) Then
                    myRS.AddNew
                        myRS![HIS_USER] = useUserName
                        myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
                        myRS![HIS_FIELD] = D.Name
                        myRS![HIS_FORM] = form2.Name
                        myRS![HIS_TABLE_ID] = myID    'CHANGE THIS
                        myRS![HIS_TABLE_NAME] = form2.RecordSource
                        myRS![HIS_OLD_VALUE] = "Previous value was blank."
                        myRS![HIS_NEW_VALUE] = D.Value
                        myRS![HIS_DATE_CHANGE] = Date
                        myRS![HIS_TIME_CHANGE] = Time()
                    myRS.Update
                [COLOR=red]ElseIf Nz(myValue, 0) <> myArray(X) Then[/COLOR]
                    myRS.AddNew
                        myRS![HIS_USER] = useUserName
                        myRS![HIS_MACHINE_NAME] = Environ("COMPUTERNAME")
                        myRS![HIS_FIELD] = D.Name
                        myRS![HIS_FORM] = form2.Name
                        myRS![HIS_TABLE_ID] = myID    'CHANGE THIS
                        myRS![HIS_TABLE_NAME] = form2.RecordSource
                        myRS![HIS_OLD_VALUE] = myArray(X)
                        myRS![HIS_NEW_VALUE] = D.Value
                        myRS![HIS_DATE_CHANGE] = Date
                        myRS![HIS_TIME_CHANGE] = Time()
                    myRS.Update
                End If
            End Select
TryNextD:
         Next D
    Set form1 = Nothing
    Set form2 = Nothing
End Function

TIA,
Rodger

 

[MarkK]

I don't see anywhere that myArray() is ever declared or assigned a value. If this is the problem you may want to add Option Explicit to your modules, forcing Access to throw an error if it finds undeclared variables. Otherwise Access interprets your typos as a new variable and implicitly declares it for you, which is almost always unexpected.

Option Compare Database
[COLOR="DarkRed"]Option Explicit[/COLOR]

In a code window you can set this as the default at Menu->Tools->Options->Editor Tab->Require Variable Declaration.

 

[MarkK]

No, that's not it is it. Maybe it's that acCheckBox and acOptionButton are not included when you construct myArray() in myCurrent. This will yield a different count of elements when you execute this loop in myHistory...

For Each D In form2.Controls

 

[gemma-the-husky]

ElseIf Nz(myValue, 0) <> myArray(X) Then

if you try to compare (inadvertently) numbers and texts with this, you are likely to get a type mismatch error.

 

[RodgerDJr]

OK I added the two additional controls, I cannot believe I missed them. Here is the coed I have now

            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton
               ' Skip Updates field.
                myValue = D.Value

Now I am getting a runtime 2424 error on the myValue = D.Value

What have I done now???? I did check to make sure that the Name of the control that is having a problem is named corectly.

TIA,
Rodger

 

[boblarson]

OK I added the two additional controls, I cannot believe I missed them. Here is the coed I have now

            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton
               ' Skip Updates field.
                myValue = D.Value

Now I am getting a runtime 2424 error on the myValue = D.Value

What have I done now???? I did check to make sure that the Name of the control that is having a problem is named corectly.

TIA,
Rodger

Where does D come from? Is that the name of a control? If so where is it? I don't see you assigning it to an object. I see a Dim C as Control but I don't see a Dim D as Control anywhere.

 

[RodgerDJr]

I checked and I do have D dimed as a control. It is the first line in myHistory. I was hoping it was going to be that.

 

[boblarson]

I checked and I do have D dimed as a control. It is the first line in myHistory. I was hoping it was going to be that.

Still sounds like it is that. Sounds like you have not set the variable up to be in scope within MyCurrent procedure. If you declared it in myHistory by using Dim D As Control then it has scope ONLY within that procedure. You would have to declare it either at module level (not procedure level) if myCurrent and myHistory are in the same module, or you would have to declare it as a public variable ( Public D As Control) in a standard module so that it can be accessed from more than one procedure.