"stLinkCriteria" from a Sub Form button to open another Form

[Guest]

I would like to open a form from a Sub Form record within the main Form (using a command button) linking TWO fields from the Sub Form to the other form.

Example 1 code works Ok for ONE field but example 2 gives a Type Match error for TWO fields and example 3 works Ok if run from just the Sub Form but prompts for parameters if run from within the Main Form.

Example 1

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "QMS_FRM_620_37_JOB_COST_CODE_PopUp"
stLinkCriteria = "[JOB_TASK_H_NO] = " & Me.[Task No]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Example 2 - Error (Type Match)

stLinkCriteria = "[JOB_NUMBER_2] = " & Me.[JOB_NUMBER_2] And [JOB_TASK_H_NO] = " & Me.[Task No]"

Example 3 - Does not work if run from the Main Form ?

'stLinkCriteria = "[JOB_NUMBER_2] = Forms![QMS_FRM_620_37_Task_Header_Subform]![JOB_NUMBER_2] and [JOB_TASK_H_NO] = Forms![QMS_FRM_620_37_Task_Header_Subform]![Task No]"

Thanks in advance for your help it is much appreciated.

Steve F

 

[Guest]

Are all datatypes Text or the same datatypes??

If not can you try an explicit cast?

stLinkCriteria = "[JOB_TASK_H_NO] = " & Clng(Me.[Task No])

For example:

-----Original Message-----
I would like to open a form from a Sub Form record

within the main Form (using a command button) linking TWO
fields from the Sub Form to the other form.

Example 1 code works Ok for ONE field but example 2

gives a Type Match error for TWO fields and example 3
works Ok if run from just the Sub Form but prompts for
parameters if run from within the Main Form.

Example 1

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "QMS_FRM_620_37_JOB_COST_CODE_PopUp"
stLinkCriteria = "[JOB_TASK_H_NO] = " & Me.[Task No]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Example 2 - Error (Type Match)

stLinkCriteria = "[JOB_NUMBER_2] = " & Me.[JOB_NUMBER_2]

And [JOB_TASK_H_NO] = " & Me.[Task No]"

Example 3 - Does not work if run from the Main Form ?

'stLinkCriteria = "[JOB_NUMBER_2] = Forms!

[QMS_FRM_620_37_Task_Header_Subform]![JOB_NUMBER_2] and
[JOB_TASK_H_NO] = Forms!
[QMS_FRM_620_37_Task_Header_Subform]![Task No]"