subscript out of range error

[Guest]

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?

 

[Ken Snell [MVP]]

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

 

[Guest]

"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?

 

[Ken Snell [MVP]]

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?
--

Ken Snell
<MS ACCESS MVP>

 

[rkc]

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

What happens if you comment out

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

and just debug.print MyOpenArgs?

 

[Ken Snell [MVP]]

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?

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

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?

 

[Duane Hookom]

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

 

[Guest]

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"