Kumar-
Ah, you're trying to filter the outer form based on something in the subform. To do that correctly, you need to build an IN clause. It'll look something like:
OuterPKey IN (SELECT FKey From SubformTable WHERE SESNum = "<some value>")
The above is basically saying "Show me the records on the outer form where some related record on the subform has a value of "<some value>" in the SESNum field."
I would need to know the Record Source of the outer and subform and the linking fields to give you a more specific answer.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of access_kri@yahoo.com
Sent: Monday, December 02, 2013 10:10 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: RE: [MS_AccessPros] Search data of subform from Main Form
Thanks John. I am giving the rest of the code for your review and advise.
If Not IsNull(Me.cboSearchSEnum) Then
strWhere = strWhere & "(Me.child59.SESNum Like """ & Me.[cboSearchSEnum] & """) AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
Regards,
Kumar
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote:
Kumar-
You don't show the rest of your code, so it's hard to see how you're trying to apply the filter.
BUT you are constructing the filter incorrectly. A filter must always reference just the field name:
SESNum LIKE "<some search>"
Or
SESNum = "<some search>"
It doesn't look like you are adding any wildcards (*), so you probably should be using equals.
After you get the filter built, and as long as the filter isn't referencing any fields on the outer form, you should do:
Me.child59.Form.Filter = strWhere
Me.child69.Form.FilterOn = True
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of access_kri@...
Sent: Monday, December 02, 2013 6:46 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Search data of subform from Main Form
Dear group,
I have search combo boxes in the main form header to search and filter data contained in the main form fields. The main form also contains sub-form and I am not able to construct a search string to search for data contained in the subform. Require help for the same.
I tried with the following code but it is throwing up some error and not filtering the sub-form
If Not IsNull(Me.cboSearchSEnum) Then
strWhere = strWhere & "(Me.child59.SESNum Like """ & Me.[cboSearchSEnum] & """) AND "
End If
Child 59 is the subform name and SESNum is the field in the sub-form which I am trying to find, cboSearchSENum is the search combo box on the main form header which lists the SESnumbers of the Subform.
Regards,
Kumar
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar