Full Version: hide parent records based on subform field
UtterAccess Forums > Microsoft® Access > Access Forms
spokey
Hello. I am hitting a wall trying to clean up a form. There is a parent form with a subform. The parent form shows information about a purchase req, the subform shows the line items on each req. in the subform, each line item has a check box to indicate that it was recieved. I have a radio button on the parent form that adds and removes a filter on the subform to either show all line items or only the ones waiting to be recieved. That part works fine. Where I am stuck is trying to get the parent form to only show reqs that have at least 1 line item waiting to be recieved (if all of the line items have been recieved, I don't want to be bothered seeing the req). The parent table has an ID field as a PK, the detail table has a parent_ID field as a FK. The checkbox is in the detail table. If I add the detail table, instead of 2 reqs with 3 line items each, I get 6 reqs with 1 line item each. How should I solve this? Can I count the 'visible' records in the subform as a filter for the parent form?
JVanKirk
Don't know if you could count the line items in the subform, but you could count the records in the detail table where the ID(FK = the ID(PK) from your Request Table and where the combobox is unchecked.
o say your tables are:
tblRequests
ReqID
RequestStuff
tblDetails
DetailID
ReqID
LineItem
Received
You could use this to return how many line items are not recieved per request:
DCount("[DetailID]", "tblDetails", "[Received] = False AND [ReqID] = " & Forms!YourParentFormName.ReqID)
Now, if you wanted to add that to query criteria to return records where that number is greater than 0 just add something like this in your criteria line:
DCount("[DetailID]", "tblDetails", "[Received] = False AND [ReqID] = " & Forms!YourParentFormName.ReqID)>0
That shoudl return only those records where there are line items not yet received.
spokey
Hi Jason- Thank you for the advice. I tried to add this idea on both the parent form AND the subform, I couldn't get either to work. I have the following table fields (others exists, but this is the chopped down list for this part):
bl_po
ID Autonumber (PK)
tbl_poLineItems
ID Autonumber (PK)
po_ID Number (FK)
checkIssued Yes/No
The main form (frm_po) has a subform (frm_poLineItems) which currently works. If I have 2 po's (main form), each with 3 line items (sub form) I will get 'record 1 of 2' and each main form will show the 3 line items for it in the subform. The Record source for the main form is based on tbl_po. Once I add the DCount function as a filter, I get an error saying that Access can't find the table 'tbl_poLineItes'. If I add the DCount as a field in the Record Source, I get 'record 1 of 6', and records 1,2 & 3 of the main form will each show the 3 line items for the first PO, then 4,5 & 6 will each show the 3 line items of the second PO.
Any ideas what I may be doing wrong?
PS- there is also a radio button showing/hiding the line items that have been received, here is the code I am using:
Private Sub Frame128_AfterUpdate()
If Me.Frame128.Value = "1" Then
Form_frm_poLineItems.FilterOn = False
Form_frm_poLineItems.Requery
Else:
Form_frm_poLineItems.Filter = "checkIssued = false"
Form_frm_poLineItems.FilterOn = True
Form_frm_poLineItems.Requery
End If
JVanKirk
I've never seen the underscore work to identify entities.
ypically it would be typed out Forms!frmpoLineItems.Filter....etc.
Also, was tbl_poLineItes a typo in your post? If not, then that is the problem..your DCount is looking for a table that doesn't exist, need to check that M in your table name. A little foot note while we are talking names...ID as the ID field for your tables is going to give you headaches...how do you know which ID field is which when you are reading through code? OK, maybe you preface each with the table name, but something like PO_ID in tbl_po and LineItemID in tbl_poLineItems would be more relevant and I think make things easier for you down the road.
Now, for the DCount. Try just adding an unbound text box to your parent form and set it's control source to this:
=Nz(DCount("[ID]", "tbl_poLineItems", "[checkIssued] = 0 AND [po_ID] = " & Forms!frm_po.ID), "No Line Items")
If you copy/paste that from above it should work. Now, on each record, you should get a count of line items still not issued for each record. If that works, then we know it can find the info we need to filter your form. The RecrodSource of your form will need to be done using a query, but we will get to the query in a bit, let's see if we get numbers for above.
spokey
Good morning Jason.
Thanks for the pointers:
Odon't know the difference between underscores and dots and bangs, but it works
yes, po_LineItes was a typo
Some of what I work on gets put into MySQL and the relationships are built with ASP pages, so I usually end up spelling out table names anyway, but you make a good point
The code you supplied does work on the parent form and gives me the correct number of non-recieved line items for each record. How should I use this to filter the recordset?
JVanKirk
Well, if your recordsource is a query, built from tbl_po, add all the fields from tbl_po which you will need on your form, then add one more field:
o to the next blank column after your last field and type in LineItems: and then after the colon, paste that DCount function WITHOUT the = sign. Now, if you look at your query results, you should see your records with that field at the end labelled LineItems. Back in the design view, in the criteri for line items put >0 and now you shouldonly see records returned where there are existing line items. Save this query as qryReqsWithLineItems and then set your parent forms record source to this query.
Hope that helps.
spokey
Well, that seems to take me back to the earlier problem.: "If I add the DCount as a field in the Record Source, I get 'record 1 of 6', and records 1,2 & 3 of the main form will each show the 3 line items for the first PO, then 4,5 & 6 will each show the 3 line items of the second PO".
JVanKirk
Do you only have the one table in your query builder or is there more than one table?
spokey
There are 2 tables in the maiin query (the po table and another for vendor information). There is also a dropdown control that looks up values from an employee table, but that tabel is not part of the query that the form is based on.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.