multiple stLinkCriteria problem? 2

[glxman]

Hi can someone advise what is wrong with the following - I have fiddled with it and just cannot get it to work.

Private Sub cmdViewTxns_Click()

On Error GoTo Err_cmdViewTxns_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmKE5ZList"
    
    stLinkCriteria = "[Workstream]=" & "'" & Me![Workstream] & "' and [Period] =" & "'" & Me![Period] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewTxns_Click:
    Exit Sub

Err_cmdViewTxns_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewTxns_Click
    
End Sub

I began with the wizard linking the [Workstream] and tried to add [Period] - unsuccessfully!

Many thanks
Richard

 

[Remou]

Are both workstream and period text fields? Numeric fields do not need delimiters, date fields need hash (#) delimiters.

 

[glxman]

Hi Remou

Yes the period is a numeric field so i removed the delimiters which solves some of the problem

The click button is on a subform and I can get it to work correctly by referencing the form control like:

forms![myform subform]![period]

But this only works when the subform is open directly - otherwise I get parameter value prompt. Any ideas?

Thanks
Richard

 

[Remou]

If both the button and the controls are on the subform, you would be better off with Me. Did it not work?

 stLinkCriteria = "[Workstream]='" & Me![Workstream] & "' and [Period] =" & Me![Period] 
    DoCmd.OpenForm stDocName, , , stLinkCriteria

You cannot refer to subforms contained in a subform control in the same way as you refer to forms, here are the rules:

http://www.mvps.org/access/forms/frm0031.htm

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glxman]

The Me did not work, but this information is useful and I have solved my problem. I instead referenced to a listbox control with the same value on the main form (that dictates the value on the subform). I realise I can refer to the value on the subform with the correct syntax, which is useful for future reference.

Many thanks for your help.

Richard