How do I add records from a filtered table to a junction table?

[paturdc]

Updated to add a sample database:

TransactionOrder.odb

The form is “Filter_to_Parts_to_Transactions” - the other one doesn’t work at all.

Below is a simplified version of the database that I’m trying to create.

I have a list of electronic parts in a table “tbl_Parts” and a list of orders in the table “tbl_Orders.” I wish to add parts to orders in my junction table, called “tbl_Transactions:”

It is imperative that the parts are selected from a Table Control, and with the possibility of filtering with wildcards, so I’ve added a filter table, which then comprises my Main Form:

The first and most important problem I have is that if I select an Order from the listbox, it gets blanked out whenever I select a part from the table. I am forced to select the Order again every time I add a record to tbl_Transactions. This takes too many mouse clicks, and adds the possibility of user error. I’m wondering if having three layers of forms is the correct way to go? I’ve tried adding an intermediate form for holding the Order.ID but it changes nothing.

Additionally, I’d love to have the Order.ID way up top in the Main Form, so that I could filter out parts that are already added to the order. But I cannot for the life of me figure out how to pass the fields down through the forms.

---

---

UPDATED TO ADD SCREENSHOTS OF LATER ISSUE:

---

---

---

---

[frofa]

I suspect the problem lies with your form hierarchy. Shouldn’t the orders table be at the top level (under the filter table)? The filter will determine which order (ID) is in play. Your junction table, transactions, will record all the parts associated with a particular order (i.e. an order can comprise one or more parts). If you set-up your FORM correctly you should be able to choose the ‘in play’ order, and see a list of parts for that order - adding new parts as needed. (They would all be automatically assigned to the ‘in play’ order ID in the transactions table). I think the parts list could be implemented as a list-box in the junction table list (a ‘grid’) writing the correct part ID to the tbl_transactions. Hope I’ve got this right. Would it be possible for you to post a ‘sanitized/sample’ version of your Base file for us to look at? That might make it easier to see what the problem is and fix it. Post comment/s back anyway

[frofa]

As a footnote to the above, I assume all elements (rows) of the parts table must be available for selection/inclusion in the list of parts for a given order. Things get a bit more complicated if this is NOT the case.

[paturdc]

I’ve tried putting tbl_Order at the top, but couldn’t figure how to make the parts appear in a filtered Table Control. I’ll try to add the watered down version of the database to the original question (note that the filter is only partially implemented).

To your second comment, I really need to have wildcard as well as drop down filters for the parts table on the form. The parts library could potentially be enormous and comprise very different types of parts. Other than that it would be nice if you couldn’t select parts that are already in the “current order.” Don’t know how complicated that would be.

[paturdc]

So, asking this question finally helped me overcome my basic misunderstandings. Frofa’s first comment really helped. I had originally tried making the order a part of the filter, but I couldn’t make it work. I originally thought the problem was one of hierarchy (hence this question), but it turns out the hierarchy proposed by Frofa is correct, and my original SQL was wrong. I’ll post the answer below once I’ve had time to complete a working form.

[frofa]

Excellent answer!

[paturdc]

So I found a solution, that involves expanding the Filter Table to include criteria fields from both tbl_Parts and tbl_Orders:

And below I use SQL to filter my parts according to the search terms and whichever order the user selects:

SELECT "tbl_Parts".*, "tbl_Orders"."ID" AS "OrderID", "tbl_Orders"."Name" AS "OrderName"
FROM "tbl_Orders", "tbl_Parts"
WHERE 
( ( ( UPPER ( "MPN" ) LIKE '%' || UPPER ( :Var_MPN ) || '%' OR :Var_MPN IS NULL ) AND ( UPPER ( "Description" ) LIKE '%' || UPPER ( :Var_DESC ) || '%' OR :Var_DESC IS NULL ) AND ( "PrimaryLabel" = :Var_LAB OR :Var_LAB IS NULL ) )
AND (
"tbl_Orders"."ID" = :Var_OrderID ) )
AND (
"tbl_Parts"."ID" 
NOT IN (
	SELECT "tbl_Transactions"."PartID"
	FROM "tbl_Transactions"
	WHERE "tbl_Transactions"."OrderID" = :Var_OrderID ) )

A result of this query returns parts matching the search terms only if they are NOT IN the selected order.

Now I can simply link the ID from tbl_Parts and OrderID directly to the corresponding fields in the tbl_Transactions junction table. The form now looks like this:

The middle form contains the filtered results from the above query, and the bottom form shows the parts currently recorded for the selecter OrderID. One problem that I cannot solve is that Base will not allow me to add parts to an order unless I edit at least one field in tbl_Transactions directly. This is not ideal - a user friendly design would have the possibility of adding parts just by clicking and editing the amounts later. As it is, I am only allowed to move to a new record if I manually type in an amount.

[frofa]

Regarding:

One problem that I cannot solve is that Base will not allow me to add parts to an order unless I edit at least one field in tbl_Transactions directly. 

The reason for the problem you experience might be that the content of a FORM/SUBFORM is not written to the actual database until by a button-press or by click somewhere else (like on a new record in a table grid) - so the form will ‘get stuck’ until that is done. Maybe that’s the reason for the ‘funny’ behaviour you mentioned above. I think a macro can solve that problem (I’m no macro expert). Come to think of it, when using Firebird, the FORM data must first be be written to a temporary cache in memory, because the user must later do an explicit SAVE to write the data to disk. QUESTION: Is your transactions tbl ID auto-value?

[paturdc]

So I’m still on HSQLDB and transactions has autovalue ID.

I see I can’t add images to comments, but what happens is that unless I edit some field in tbl_Transactions both “NEW RECORD” and “SAVE RECORD” are grey and inactive. If I type anything in the “amount” field, both buttons become active, and I can now add records to the underlying table.

[frofa]

Interesting. Not sure what the problem is at this stage, nor whether I quite understand it. Logically, you shouldn’t be able to SAVE the current record if it does not have any content. Once you have SAVED a record, you should then logically be able to create a NEW record. I am assuming that the AMOUNT to be added is the only field that can be populated by the user; the others in the TRANSACTIONS tbl are ‘set’ by the cascade of the form hierarchy.

[paturdc]

I am assuming that the AMOUNT to be added is the only field that can be populated by the user; the others in the TRANSACTIONS tbl are ‘set’ by the cascade of the form hierarchy.

This is exactly correct. For the end user, it is much faster to add the desired parts to the desired order and then work out the amounts later (for instance in a table control filtered by the current OrderID). If I add a default value to the Amount field (say default = “1”), the user first has to delete the default value, then type something else, and then save.

Logically, you shouldn’t be able to SAVE the current record if it does not have any content.

The table has content, but it is populated by the user clicking on the parent form. I’ve added some screenshots to the original question.

Thanks for taking the time to help me along

[frofa]

Logically, you shouldn't be able to SAVE the current record if it does not have any content.

I think I solved a similar problem to yours myself, like you, by setting an SQL DEFAULT (like, say $1) for the field in question. The position of the SAVE button in your form hierarchy is still a bit of a question in my mind. I have a form where the SAVE button is at the TOP level of the form hierarchy, and it never get ‘greyed-out’. Maybe you might try that option. You could probably dispense with the SAVE button by using a macro to SAVE when the user clicks elsewhere.

[paturdc]

I guess I’ll have to go over to macros at some point, I already need to make one for updating the forms. Unfortunately default values don’t help, so I’m stuck with some unnecessary clicking for now.

I doubt moving the SAVE button could help, it needs to connect to the correct table, which is in the subsubform.

Thanks for all the help

[frofa]

See 3 comments above (revised).