Solved Open form and filter (1 Viewer)

theinviter

Registered User.
Local time
Today, 04:58
Joined
Aug 14, 2014
Messages
193
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

I’m here to help
Staff member
Local time
Today, 04:58
Joined
Oct 29, 2018
Messages
19,134
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

Well-known member
Local time
Today, 07:58
Joined
Apr 9, 2015
Messages
4,120
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

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2002
Messages
36,837
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:
Code:
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.
Code:
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.
 
Last edited:

Users who are viewing this thread

Top Bottom