Full Version: form and subform nested filter or query code
UtterAccess Forums > Microsoft® Access > Access Forms
valkster
Hello, again. I am having trouble getting a search screen to work as I had hoped. I have a Purchase Requisition database that I am building. It is very similar to a sales order database. I have a table containing basic information about a given requisition (reqID, who is requisitioning the items, what department is to be charged, etc). This table is linked one-to-many with a details table that contains the line items for each requisition (ie, the parts people want to order). I want to be able to search past requisitions based upon fields in either table, but prrocessed through the main requisition table. For example, I would like to be able to find all requisitions that were created by "someone" that has "ball bearing" in any of their line item descriptions. I can get my searches to work somewhat by creating a form that links the two tables but the record count for this form (and search results) is based upon the line items, not the requisitions. What I mean is that if I have 10 requisitions in my table with each having two line items then my form shows 20 records. What I was really looking for seems to require a nested ADO (or DAO?) code that searches the requisition table for all records matching my first search variable (the "someone" field). Then, searches only those records for the description variable ("ball bearings") and shows only those requisitions in my form. How wold I go about this? Most of my ADO/DAO code so far is to find single records not a group of them with a criteria in a linked table. I have never done nested ADO code before. Any help would be greatly appreciated.
Thanks,
Mark
valkster
Could this be done with a subquery instead of code? If so, how would I go sbout this and how would I display the results in the form?
incerely,
Mark
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.