subscript out of range error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Emma - 22 Nov 2005 19:25 GMT

I'm getting a subscript out of range error when trying to pass arguments via
OpenArgs. This is my code in my first form.

Private Sub Job_Type_AfterUpdate()
Dim MyOpenArgs As String

MyOpenArgs = Me!Job_ID & "-" & Me!Account_No

Select Case Me!Job_Type

Case "New Contract"
DoCmd.GoToControl "Frame_Contract_Option"

Case "Rental"
DoCmd.OpenForm "frm_Rental_Detail", acNormal, Qry_Rental_Detail, ,
acFormAdd, acWindowNormal, MyOpenArgs
Case "Evaluation"

DoCmd.OpenForm "frm_Eval_Detail", acNormal, Qry_Eval_Detail, ,
acFormAdd, acWindowNormal, MyOpenArgs
Case Else
DoCmd.GoToControl "Rep_name"

End Select

End Sub

In my second form I have:

Private Sub Form_Load()

FK_Master_Job_ID = Split(MyOpenArgs, "-")(0)
Rental_Acct_No = Split(MyOpenArgs, "-")(1)

End Sub

Anyone know why I am getting this error?

Reply to this Message

Ken Snell [MVP] - 22 Nov 2005 19:53 GMT

What are the variables "Qry_Rental_Detail" and "Qry_Eval_Detail" holding?
Are they text strings that contain the names of queries? Or are they meant
to be the actual names of the queries? If the latter, enclose them in "
characters so that you pass the names as text string to the OpenForm method:

DoCmd.OpenForm "frm_Eval_Detail", acNormal, "Qry_Eval_Detail", , acFormAdd,
acWindowNormal, MyOpenArgs

Signature

Ken Snell
<MS ACCESS MVP>

> I'm getting a subscript out of range error when trying to pass arguments
> via
[quoted text clipped - 34 lines]
>
> Anyone know why I am getting this error?

Reply to this Message

Emma - 22 Nov 2005 20:26 GMT

"Qry_Rental_Detail" is the name of an actual query in my database.

I've modified the OpenForm Method as follows:

Case "Rental"
DoCmd.OpenForm "frm_Rental_Detail", acNormal, "Qry_Rental_Detail", ,
acFormAdd, acWindowNormal, MyOpenArgs

Still getting "subscript out of range".

What else could it be?

> What are the variables "Qry_Rental_Detail" and "Qry_Eval_Detail" holding?
> Are they text strings that contain the names of queries? Or are they meant
[quoted text clipped - 42 lines]
> >
> > Anyone know why I am getting this error?

Reply to this Message

Ken Snell [MVP] - 22 Nov 2005 23:31 GMT

Do you get the error if you leave out the MyOpenArgs argument?

Is it possible that the error is occurring in the form's RecordSource query?
Perhaps there is a function there that is causing this error?

Signature

Ken Snell
<MS ACCESS MVP>

> "Qry_Rental_Detail" is the name of an actual query in my database.
>
[quoted text clipped - 60 lines]
>> >
>> > Anyone know why I am getting this error?

Reply to this Message

Ken Snell [MVP] - 23 Nov 2005 00:45 GMT

Thanks to rkc's post, I note that I didn't see the part about the second
form's code content in your original post.

I agree with rkc that the problem most likely is because the OpenArgs string
is not what you expect. Are you sure that the Me!Job_ID and the
Me!Account_No controls actually have values?

Signature

Ken Snell
<MS ACCESS MVP>

> Do you get the error if you leave out the MyOpenArgs argument?
>
[quoted text clipped - 65 lines]
>>> >
>>> > Anyone know why I am getting this error?

Reply to this Message

Emma - 29 Dec 2005 06:34 GMT

Hi Ken,

Just getting back to this. . . .

I know that MyOpenArgs has a string value in it. However, it looks like the
values are not coming over to my second form/report.

When I comment out those particular lines of code the form/report opens but
no values are passed.

Any ideas on what is keeping the values from beign passed?

> Thanks to rkc's post, I note that I didn't see the part about the second
> form's code content in your original post.
[quoted text clipped - 72 lines]
> >>> >
> >>> > Anyone know why I am getting this error?

Reply to this Message

Duane Hookom - 29 Dec 2005 12:46 GMT

Emma,
Have you set Option Explicit and tried to compile your code? I don't know
how you expect the variable "MyOpenArgs" to have a value in the newly opened
form. Try something like:

Private Sub Form_Load()
Dim MyOpenArgs as String
MyOpenArgs = Me.OpenArgs
FK_Master_Job_ID = Split(MyOpenArgs, "-")(0)
Rental_Acct_No = Split(MyOpenArgs, "-")(1)
End Sub

Signature

Duane Hookom
MS Access MVP
--

> Hi Ken,
>
[quoted text clipped - 90 lines]
>> >>> >
>> >>> > Anyone know why I am getting this error?

Reply to this Message

Emma - 29 Dec 2005 16:34 GMT

Hi Duane,

I've revised my code to reflect OpenArgs as the passed data and have set
the form as Option Explicit and recompiled the code. I am still not getting
any values passed to the OpenArgs in the called form. Why are the values not
being passed?

Private Sub Form_Open(Cancel As Integer)

Dim txt_Account_No As String
Dim FK_Job_ID As Integer
Dim Contract_Account_Name As String

If IsNull(OpenArgs) = False Then

FK_Job_ID = Split(OpenArgs, ",")(0)
txt_Account_No = Split(OpenArgs, ",")(1)
Contract_Account_Name = Split(OpenArgs, ",")(2)

End If

P.S.

If you don't mind, I'm going to re-post this under the correct subject "No
values being passed through OpenArgs"

> Emma,
> Have you set Option Explicit and tried to compile your code? I don't know
[quoted text clipped - 102 lines]
> >> >>> >
> >> >>> > Anyone know why I am getting this error?

Reply to this Message

rkc - 22 Nov 2005 23:52 GMT

> I'm getting a subscript out of range error when trying to pass arguments via
> OpenArgs. This is my code in my first form.
[quoted text clipped - 31 lines]
>
> End Sub

What happens if you comment out

'FK_Master_Job_ID = Split(MyOpenArgs, "-")(0)
'Rental_Acct_No = Split(MyOpenArgs, "-")(1)

and just debug.print MyOpenArgs?

Reply to this Message