how to filter datasheet subform by clicking on row from other subform on navigation subform

[saudkazia]

title may seem confusing but i'll try to explain the problem to make sense of it..



i have a main form with navigation subform. this navigation subform links to an unbound form with two datasheet subforms.

table in question for both subforms work_card
query in question for subform a - qry_workcard_qty
query in question for subform b - qry_workcard

work_card table fields
workid, indate, materialID, laminateID, bodyID, faceID, modelID, polishID, DrawersID, Mirrors, Glass, categoryID, sizeID, pcs, estdate, outdate, photo, itemprice, complete

qry_workcard_qty
SELECT qry_workcard.Work_name, material.body, polish.polish, Count(qry_workcard.workid) AS CountOfworkid, qry_workcard.material, material.body_id, polish.polid
FROM polish RIGHT JOIN (material RIGHT JOIN qry_workcard ON material.body_id = qry_workcard.material) ON polish.polid = qry_workcard.polishID
GROUP BY qry_workcard.Work_name, material.body, polish.polish, qry_workcard.material, material.body_id, polish.polid;

qry_workcard
SELECT workcard.workid, workcard.indate, workcard.material, workcard.laminateID, workcard.bodyID, workcard.faceID, workcard.modelID, workcard.polishID, workcard.DrawersID, workcard.Mirrors, workcard.Glass, workcard.categoryID, workcard.size, workcard.pcs, workcard.estdate, workcard.outdate, workcard.photo, workcard.itemprice, [category_path] & [photo] AS photofullpath, [model] & " " & [category] AS Work_name, bill.billid, workcard.complete, category.category_path
FROM (model RIGHT JOIN (category RIGHT JOIN workcard ON category.catid = workcard.categoryID) ON model.ID = workcard.modelID) LEFT JOIN (bill RIGHT JOIN bill_work ON bill.billid = bill_work.billid) ON workcard.workid = bill_work.work_id;




subform a's record source and subsequent fields are - work_name (field that concatenates multiple fields as a display name for the work model), body (text field derived from bodyid), polish (similar to body), material (ditto), countofworkid, bodyid, polid, materialid. subform b's recorsource and subsequent fields are workid, indate, workname, polid, bodyid, materialID, body, polish

the whole point of two subforms is one to list out all the unique variances of a work_card. so lets say I have a 12 singapore chairs with black polish and teak body, i would like it to show only one row and a count of 12 in countofworkid when clicking on it i should see all 12 separate work cards. right now i am able to get the count but not able to filter the 2nd subform to show only those 12 that match what i have clicked.

i used the following in the after update of the work_name. i tried different variances substituting body_id to polid, work_name and even with hardcoded entries but nothing seems to work.

Forms!main.NavigationSubform![frm_activeorders].Form.Filter = "[body_id] =" & me.body_Id
Forms!main.NavigationSubform![frm_activeorders].Form.Filter = True


whats the best way to do this if what i propose is not logically possible due to design of access and sql. basically i want to list in one form all the unique work card variations based on model, bodyid, and polid with a count and on click of the row i want it to drill down to each individual of that group that is counted. if what i am proposing will not work how best can i tackle it.

i hope this is enough information to help deduce a solution.

[saudkazia]

well. after a bit (more) of googling i found a solution that works. and best part is that there is very very little vba

http://stackoverflow.com/questions/2...nother-subform