Kumar-
I would bet SESNum is a number, not text. Get rid of the quotes. You should end up with:
(SESNum = 100278098)
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 5:23 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: RE: RE: RE: RE: [MS_AccessPros] Search data of subform from Main Form
John-
strWhere shows
strWhere="(SESNum="1000278098")"
1000278098 is the SESNum that I selected in the search combo box.
Thanks and Regards,
Kumar
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote:
Kumar-
What is in strWhere?
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 1:44 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: RE: RE: RE: [MS_AccessPros] Search data of subform from Main Form
John, Thanks I tied your coding and when I click search command button, it is giving error
"Run time error 3464
Data type mismatch in criteria expression"
When I click debug, the following line is showing highlighted.
Me.Filter = "ID IN (SELECT LinkID FROM T_SES WHERE " & strWhere & ")"
Regards,
Kumar
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote:
Kumar-
Assuming ALL of your filters are on fields in the subform, like this:
If Not IsNull(Me.cboSearchSEnum) Then
strWhere = strWhere & "( SESNum = """ & 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 = "ID IN (SELECT LinkID FROM T_SES WHERE " & strWhere & ")"
Me.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 11:06 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: RE: RE: [MS_AccessPros] Search data of subform from Main Form
John, you have understood the requirement correctly. I would request you to give the proper syntex for VBA coding. I am giving below the required details:
1) Record source of the main form : T_serviceRegister
2) Record source of subform: T_SES
3) The primary key of subform table is SESid and Foriegn key is LinkId
4) Primary key in main form record source is simply named as ID.
Thanks and Regards,
Kumar
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote:
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@...
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
(Message over 64 KB, truncated)
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (10) |
Tidak ada komentar:
Posting Komentar