Allen Browne's "Filtering on a field in the Subform"

[iamnu]

Re: http://allenbrowne.com/ser-28.html

I am using Allen Browne's code to filter a field in my Subform. It
works on some values I enter, but not all, and I have no clue as to
why it doesn't find all the values. The code finds all the values, or
none at all.

Also, how does one "clear" the cboShowSup Combo box once a value has
been entered, to then look for another?

I would appreciate it if someone can lead me to what I am doing wrong
here.

Thanks,
Bernie

I have modified his code to read as follows:

'tblProduct = tblProvider
'tblProductSupplier = tblPayDetail
'SupplierID = ServiceID

Private Sub cboShowSup_AfterUpdate()
Dim strSQL As String
If IsNull(Me.cboShowSup) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "tblProvider"
Else
strSQL = "SELECT DISTINCTROW tblProvider.* FROM tblProvider " & _
"INNER JOIN tblPayDetail ON " & _
"tblProvider.ProviderID = tblPayDetail.PayDetailID " & _
"WHERE tblPayDetail.ServiceID = " & Me.cboShowSup & ";"
Me.RecordSource = strSQL
End If
End Sub

 

[Tom van Stiphout]

On Mon, 29 Dec 2008 04:52:18 -0800 (PST), iamnu <[email protected]>
wrote:

Set a breakpoint and carefully inspect the value of strSQL in a case
that works, and in one that doesn't, and you should be able to
understand the problem. My guess? The dropdown doesn't have the values
you think it does.

-Tom.
Microsoft Access MVP

 

[iamnu]

Set a breakpoint and carefully inspect the value of strSQL in a case
that works, and in one that doesn't, and you should be able to
understand the problem. My guess? The dropdown doesn't have the values
you think it does.

-Tom.
Microsoft Access MVP

Excellent suggestion. I made a query that does what I want and it
works fine. The SQL Statement for that query is as follows:

SELECT tblProvider.ProviderID, zServiceDesc.ServiceID,
zServiceDesc.ServiceDesc
FROM zServiceDesc INNER JOIN (tblProvider INNER JOIN tblPayDetail ON
(tblProvider.PayeeID = tblPayDetail.PayeeID) AND (tblProvider.CoNameID
= tblPayDetail.CoNameID) AND (tblProvider.AcctID =
tblPayDetail.AcctID)) ON zServiceDesc.ServiceID =
tblPayDetail.ServiceID
WHERE (((zServiceDesc.ServiceID)=3));

I then copied that SQL statement to Allen's code for the strSQL
variable to get this:

strSQL = "SELECT tblProvider.ProviderID, zServiceDesc.ServiceID,
zServiceDesc.ServiceDesc FROM zServiceDesc INNER JOIN (tblProvider
INNER JOIN tblPayDetail ON (tblProvider.AcctID=tblPayDetail.AcctID)
AND (tblProvider.CoNameID=tblPayDetail.CoNameID) AND
(tblProvider.PayeeID=tblPayDetail.PayeeID)) ON
zServiceDesc.ServiceID=tblPayDetail.ServiceID WHERE
(((zServiceDesc.ServiceID)=" & Me.cboShowSup & "));"

The SQL query gives me five values when zServiceDesc.ServiceID=3.
This is correct!
Allen's code gives me five main form records, but none on the
subform. I quess I'm not referring to the subform "tblPayDetail"
properly.

Suggestions?

 

[Allen Browne]

If you are getting the right records in the main form, and none in the
subform, then either:
a) the subform's query is not right, or
b) the LinkMasterFields/LinkChildFields properties of the subform control
are not right.

For the code to unselect all items in a list box, see:
http://allenbrowne.com/func-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Set a breakpoint and carefully inspect the value of strSQL in a case
that works, and in one that doesn't, and you should be able to
understand the problem. My guess? The dropdown doesn't have the values
you think it does.

-Tom.
Microsoft Access MVP

Excellent suggestion. I made a query that does what I want and it
works fine. The SQL Statement for that query is as follows:

SELECT tblProvider.ProviderID, zServiceDesc.ServiceID,
zServiceDesc.ServiceDesc
FROM zServiceDesc INNER JOIN (tblProvider INNER JOIN tblPayDetail ON
(tblProvider.PayeeID = tblPayDetail.PayeeID) AND (tblProvider.CoNameID
= tblPayDetail.CoNameID) AND (tblProvider.AcctID =
tblPayDetail.AcctID)) ON zServiceDesc.ServiceID =
tblPayDetail.ServiceID
WHERE (((zServiceDesc.ServiceID)=3));

I then copied that SQL statement to Allen's code for the strSQL
variable to get this:

strSQL = "SELECT tblProvider.ProviderID, zServiceDesc.ServiceID,
zServiceDesc.ServiceDesc FROM zServiceDesc INNER JOIN (tblProvider
INNER JOIN tblPayDetail ON (tblProvider.AcctID=tblPayDetail.AcctID)
AND (tblProvider.CoNameID=tblPayDetail.CoNameID) AND
(tblProvider.PayeeID=tblPayDetail.PayeeID)) ON
zServiceDesc.ServiceID=tblPayDetail.ServiceID WHERE
(((zServiceDesc.ServiceID)=" & Me.cboShowSup & "));"

The SQL query gives me five values when zServiceDesc.ServiceID=3.
This is correct!
Allen's code gives me five main form records, but none on the
subform. I quess I'm not referring to the subform "tblPayDetail"
properly.

Suggestions?