Hi,
I am trying to design a form that allow me to see a list of a stock information when I select a production record. Please allow me to describe my objective briefly so that you can give me your best.
Product definition
A product (electronic PCB in my case) consists of many components and each product may have one ore more of a specific component. For example a PCB may have 5 of the same component; say a resistor or transistor. So a product table holds a record of needed parts and how many of each.
Production definition
A production table holds an ID, the timestamp of the production date and the quantity of the production.
Stock
As usual, the stock stores the records of the available components for production.
In the form I want to select the date of the production so that it supply the production fields to the subform and I show them. Then I use another filter table with a subform of this subform in order to apply the production quantity to a view table so that in the view table some calculations are made to see what parts and how many of them are missing in stock for the relevant production.
I can filter the production successfully but I cannot trigger the view table query using a filter table. The last filter table is used to hold the production quantity value and is used in the view table to calculate the remaining stock after production and also missing quantity of components (if any). But the production quantity is not loaded to the filter table, hence the sql query cannot make any calculations and show me a summary list.
I think I better upload a copy of the file so that you all can see the every detail you need. I am using the embedded HSQLDB. Unfortunately all names except the form names are in Turkish, sorry for this because changing the names of the tables and field would imply to update everything. So I translated only the form files. Please excuse me for this. Here is the odb file:
PCB-Inventory.odb (411.7 KB)
Edit
Here is an annotated screenshot in which I describe my goal:
Please instruct me how could I done this correctly or better, let me know if this can be done in another way.
Thanks in advance.