Script out of Range error

pdvsa
pdvsa used Ask the Experts™
on
Experts, I keep getting this "Script out of Range" error in the below load Event.   It seems to be something with the extra quote at the very end ((Me.OpenArgs, ";")(4) & "").  I delete this extra quote at the end but Access puts it back.  Maybe there is a syntax elsewhere.  Pay attn to the part after the LCNo in the Me.Filter part (because I added what is after it ;).  

Do you see an issue with the Me.Filter part?  I think it is in this section.  
Private Sub Form_Load()

    If Nz(Me.OpenArgs, "") <> "" Then
    If Not Me.NewRecord Then
    Me.Filter = "[letterofcreditID_Auto] = " & Split(Me.OpenArgs, ";")(0) & " AND [EndUserID] = " & Split(Me.OpenArgs, ";")(1) & " AND [LCNo] = " & Chr(34) & Split(Me.OpenArgs, ";")(2) & Chr(34) & " AND [PROJID] = " & Split(Me.OpenArgs, ";")(3) & " AND [Amount] = " & Split(Me.OpenArgs, ";")(4) & ""
       Me.FilterOn = True
     Else
        '/this is a new record
        MsgBox Split(Me.OpenArgs, ";")(4)

        Me.LetterOfCreditID_Auto = Split(Me.OpenArgs, ";")(0)
        Me.EndUserID = Split(Me.OpenArgs, ";")(1)
        Me.LCNo = Split(Me.OpenArgs, ";")(2)
        Me.ProjID = Split(Me.OpenArgs, ";")(3)
        Me.Amount = Split(Me.OpenArgs, ";")(4)
        
     End If
End If

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
pdvsa,

The subscript out of range error makes me think that the problem is with OpenArgs: specifically, that there are fewer than five items.

Please try this, and report back what you see:

Private Sub Form_Load()

    If Nz(Me.OpenArgs, "") <> "" Then
'begin new code
MsgBox "There are " & (Len(Me.OpenArgs) - Len(Replace(Me.OpenArgs, ";", ""))) & " semicolons"
'end new code
    If Not Me.NewRecord Then
    Me.Filter = "[letterofcreditID_Auto] = " & Split(Me.OpenArgs, ";")(0) & " AND [EndUserID] = " & Split(Me.OpenArgs, ";")(1) & " AND [LCNo] = " & Chr(34) & Split(Me.OpenArgs, ";")(2) & Chr(34) & " AND [PROJID] = " & Split(Me.OpenArgs, ";")(3) & " AND [Amount] = " & Split(Me.OpenArgs, ";")(4) & ""
       Me.FilterOn = True
     Else
        '/this is a new record
        MsgBox Split(Me.OpenArgs, ";")(4)

        Me.LetterOfCreditID_Auto = Split(Me.OpenArgs, ";")(0)
        Me.EndUserID = Split(Me.OpenArgs, ";")(1)
        Me.LCNo = Split(Me.OpenArgs, ";")(2)
        Me.ProjID = Split(Me.OpenArgs, ";")(3)
        Me.Amount = Split(Me.OpenArgs, ";")(4)
        
     End If
End If

End Sub

Open in new window


If there are fewer than 4 semicolons, then that is your problem.

Patrick
pdvsaProject finance

Author

Commented:
Patrick,  thank you.  I will test when i return to my computer.   Not sure if i will today though.    
Most Valuable Expert 2014

Commented:
I've never seen Split used the way your syntax is written.
For readability and troubleshhoting I would try this
Private Sub Form_Load()
Dim MyOpenArgs() as string
MyOpenArgs() = Split(Me.OpenArgs, ";")

Dim myFilter as string
    If Nz(Me.OpenArgs, "") <> "" Then
    If Not Me.NewRecord Then    
    myFilter = "[letterofcreditID_Auto] = " & MyOpenArgs(0)
    myFilter = myFilter & " AND [EndUserID] = " & MyOpenArgs(1)
    myFilter = myFilter & " AND [LCNo] = " & Chr(34) & MyOpenArgs(2) & Chr(34)
    myFilter = myFilter & " AND [PROJID] = " & MyOpenArgs(3)
    myFilter = myFilter " AND [Amount] = " & MyOpenArgs(4)

    Me.Filter = myFilter
       Me.FilterOn = True
     Else
        '/this is a new record
        msgbox MyOpenArgs(4)

        Me.LetterOfCreditID_Auto = MyOpenArgs(0)
        Me.EndUserID = MyOpenArgs(1)
        Me.LCNo = MyOpenArgs(2)
        Me.ProjID = MyOpenArgs(3)
        Me.Amount = MyOpenArgs(4)
        
     End If
End If

End Sub

Open in new window


Now you can drop a breakpoint on every line and see where it's going wrong.
And you call Split once, which is more elegant and efficient
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

pdvsaProject finance

Author

Commented:
Patrick:   it says there are 2 semicolons.  Maybe this indicates there is a issue somewhere.  I am thinking there needs to be more?  

Nick:  I seem to have a syntax error on this line:
myFilter = myFilter " AND [Amount] = " & MyOpenArgs(4)

let me know what is the next step.  
pdvsaProject finance

Author

Commented:
I have ul the db.   It might be easier to see instead of attempting by text.  
It seems as though I still have the out of script range error even after copy and paste of Patrick's code.  It might have something to do with the LCNo because you can see the number of digits this LCNo is once I hover over the yellow highlighted area in the code after the error.   There seems to be many digits that should not be there.  Ex:  theh LC No is CPCS-787109 (just made it up) but it will add many digits after.  

In the form that autoexec opens, please click on the buttton "evergreen" and you will get the error.  If you dont get the error then maybe go to another record and click the button.  

I am trying to add a record in tblAutoExtend_Evergreen after clicking on the button..

thank you
 New---use-this-for-EE---Filter.zip
pdvsaProject finance

Author

Commented:
it is 2k7.  
Top Expert 2010
Commented:
pdvsa,

My suggestion was never intended to fix the problem; rather, it was to diagnose the problem.

So, the problem is in:

Private Sub cmdAutoEXT_Click()

If DCount("LetterOfCreditID", "tblAutoExtend_Evergreen", "LetterOfCreditID_Auto=" & Me.txtLCID) = 0 Then

        DoCmd.OpenForm "frmAutoExtend_Evergreen", , , , acFormAdd, , OpenArgs:=Me.txtLCID & ";" & Me.txtEndUserID & ";" & Me.LCNo & Me.ProjectID & Me.Amount

    Else

        DoCmd.OpenForm "frmAutoExtend_Evergreen", , , , , , OpenArgs:=Me.txtLCID & ";" & Me.txtEndUserID & ";" & Me.LCNo & Me.ProjectID & Me.Amount
    
    End If
        
    
End Sub

Open in new window



In both cases, you provide an OpenArgs string that has only three elements.  Yet, your code in the Load event for frmAutoEXTEND_Evergreen assumes that there will be five elements.  That is why you are getting a subscript out of range error.

To fix it, either modify cmdAutoEXT_Click to make sure you are passing five elements, or modify that load event to only expect three elements.

Patrick
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"I've never seen Split used the way your syntax is written."
Remember, Split () is an array. That syntax is referring to the 5 element (zero based).  So, the syntax is ok.  That's something I learned here about 3 years ago ... the first time I saw that also.

mx
pdvsaProject finance

Author

Commented:
Darnit.... I missed that.  I will test after awhile.   Not at a computer now.  Thank you...   I did realize that the first answer was to diagnose and that it was a first step to a soln.    
pdvsaProject finance

Author

Commented:
I see now.... The fewer arguments were due to no separation between the fields with the ;  (semicolon).  I was missing the semicolon.  

Will follow up if have more questions.  Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial