• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1049
  • Last Modified:

Access form filter based on query (VB)

I have an Access 2010 form based on a table (tblPurchaseReview)

This table is a list of items to be review for purchasing.  There is a check box for each line to indicate that it need to be purchased.

The problem is that the same item may be listed multiple times.  We may check all or none of the lines.  However, if at least one line has a check I want to be able to filter all of the ID_ITEM for the entire form, causing all ID_ITEMS to filter out if there is at one one check.

I have a query (qryFilterCheck) to  find the unique ID_ITEMs that need to be filtered.  

I have tried the following code, which is not working.

Private Sub cmdFilterChecked_Click()

DoCmd.ApplyFilter "qryFilterCheck"

'Move focus to an open field
Me.cboFilterVendor.SetFocus

'Hide the Filter Bittpm
Me.cmdFilterChecked.Visible = False

'Unhide the Unfilter Button
Me.cmdShowAll.Visible = True

End Sub


Any ideas?
0
Scamquist
Asked:
Scamquist
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this query

select ID_ITEM
from tableName
where exists(select ID_ITEM from tableName as T where T.ID_ITEM=TableName.ID_ITEM and T.checkbox=-1)


post the sql of your query "qryFilterCheck"
0
 
ScamquistAuthor Commented:
qryFilterCheck sql

SELECT distinct tblPurchaseReview.ID_ITEM
FROM tblPurchaseReview
WHERE (((tblPurchaseReview.LineBuy)=True));
0
 
Rey Obrero (Capricorn1)Commented:
try this query

select ID_ITEM
from tblPurchaseReview
where exists(select ID_ITEM from tblPurchaseReview as T where T.ID_ITEM=tblPurchaseReview.ID_ITEM and T.LineBuy=-1)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
ScamquistAuthor Commented:
Capricorn1
Close.  This filter is giving me all of the items if at least on of them were checked.  I am trying to get the opposite.  

If any ID_ITEM has a check I want it excluded.  I am using this as a Purchse Order review form.  The check mark means the item has been reviewed with no need to view any longer.  

I tried T.LineBuy=0 but only three records were excluded.
0
 
Rey Obrero (Capricorn1)Commented:
ok, here is the reverse


SELECT ID_ITEM
FROM tblPurchaseReview
WHERE not exists(select ID_ITEM from tblPurchaseReview as T where T.ID_ITEM=tblPurchaseReview.ID_ITEM and T.LineBuy=-1);
0
 
ScamquistAuthor Commented:
Thank you for the assist.  Worked well
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now