Type Mismatch using OpenArgs

[Emma]

Hi all,

I am using Open Args to pass values to linked forms.
In the main form I have the following code

Private Sub Frame_Contract_Type_AfterUpdate()

Dim MyOpenArgs As String

MyOpenArgs = Me!Job_ID & "," & Me!cbo_Account_No & "," &
Me!cbo_Account_No.Column(1)

Select Case Form_frm_Tbl_Master_Jobs!Frame_Contract_Type
Case 1
DoCmd.OpenForm "Frm_Tbl_Contracts_Int", , , , acFormAdd, MyOpenArgs
Case 2
DoCmd.OpenForm "Frm_Tbl_Contracts_Biopsy", , , , acFormAdd
Case Else
DoCmd.GoToControl "Comments"
End Select
End Sub

Me!Job_ID is a number field
Me!cbo_Account_No is a text field
Me!cbo_Account_No.Column(1) is text

In the linked form is the following code:

Private Sub Form_Load()

Dim txt_Contract_Main_No As String
Dim FK_Job_ID As Integer
Dim Contract_Account_Name As String


FK_Job_ID = Split(MyOpenArgs, ",")(0)
Contract_Account_Name = Split(MyOpenArgs, ",")(2)

End Sub

I am getting a type mismatch error when I open the linked form. Is it
because OpenArgs converts any numeric fields to string? If so, how do I get
the FK_Job_ID field populated? I think I need to convert it back to an
integer. How would I do that?

Any help is appreciated!

 

[Douglas J. Steele]

While you may be calling what you're passing MyOpenArgs in the calling form,
in the form that was called, you refer to what you passed as Me.OpenArgs
(OpenArgs is a property of the form)

If you're not always passing a value when you open the form, you should
check whether or not there is a value for OpenArgs:

Private Sub Form_Load()

Dim txt_Contract_Main_No As String
Dim FK_Job_ID As Integer
Dim Contract_Account_Name As String

If IsNull(Me.OpenArgs) = False Then
FK_Job_ID = Split(Me.OpenArgs, ",")(0)
Contract_Account_Name = Split(Me.OpenArgs, ",")(2)
End If

End Sub

Seems to me I've already commented on this issue. The fact that the code
isn't complaining that MyOpenArgs hasn't been declared makes me suspect you
haven't told Access to insist that you declare all variables, or else you've
declared it as a global or module-level variable outside of the Form_Load
routine.

 

[Jeff Boyce]

Emma

Take a look at the CInt() (or CLng()) function in Access HELP. I believe
you could "wrap" one of those around the Split() function you are using to
get the value back out of the OpenArg.

 

[Emma]

Hi Douglas,

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



End Sub

 

[Douglas J. Steele]

You forgot the Me. in front of OpenArgs: take a closer look at what I
suggested.