Solved Open form and filter

[theinviter]

Dear all;
I have a form that upon double click on list open another form and filter it,
i tried below code but not working

DoCmd.OpenForm "MRN_Query1_Delivered", acNormal, , "[Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = MRN_Query1_subform.[Location_Code] & "

item code is text
and location code is number

can you please advise me

 

[theDBguy]

Hi. Is location code in a subform? If so, try adding "Form" to the syntax. For example:

... AND [Location - Clinics Code] = Me.MRN_Query1_subform.Form!Location_Code

 

[Ranman256]

if you are trying to open a form using another forms' subform value then, note the [subform Name], then [.Form], then [form object]

sFltr ="[field]='" & me.subform.form.txtLineCode & "'"
docmd.openform "fMy2ndForm",,,sFltr

use the BUILDER, it always gets the path correct.

 

[Pat Hartman]

DoCmd.OpenForm "MRN_Query1_Delivered", acNormal, , "[Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = MRN_Query1_subform.[Location_Code] & "

Two problems with this:
1. There is a trailing ampersand.
2. Not clear what MNR_Query1_subform is referring to or why it is embedded in the string. Should the string be:

DoCmd.OpenForm "MRN_Query1_Delivered", acNormal, , "[Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = " * Me.MRN_Query1_subform.[Location_Code]

ADVICE: Whenever you are building a string like this, especially if it contains embedded values surrounded by quotes, always build it into a variable so you can easily print the variable before executing the instruction. It will almost always help you to identify syntax errors.

Dim strSQL As String

strSQL = "[Item Code] = '" & Me.List6 & "' and [Location - Clinics Code] = MRN_Query1_subform.[Location_Code] & "
DoCmd.OpenForm  "MRN_Query1_Delivered", acNormal, , strSQL

And finally, for your sake and the sake of anyone who ever has to look at your application, please, please, please give all controls rational, meaningful names BEFORE you reference them or add event code. Me.List6 is meaningless as a name.