Two continuous subform (for inventory management)

[Babycat]

Hi everyone,

I am making an inventory management db which is mainly to track the import and export items. For import form, my idea is as below picture.

There are 2 subfroms (continous form). Left one is to show filtered items (in case table TBLPRODUCT has huge number of item).
User can select item they want by clicking "select" button, the selected item (ProductID and ProductName) will be then shown in right subform. They will later select Vendor name and key in Cost, Quantity for each item.

Left subform is working well with following filtering code

    SQL = "SELECT *  FROM TBLPRODUCT " _
         & " WHERE " _
         & ProductID_Criteria _
         & ProductName_Criteria _
         & " ORDER BY TBLPRODUCT.ProductName;"
      
        Forms!FrmImport!SubFrmTable.Form.RecordSource = SQL

But for right subform, I have not configured out the good way to implement. I tried to make TBLTEMP to temporary store selected items until the session finish. but I am struggling when insert record into table TBLTEMP...

Anyone can help me to suggest the way to handle this right subform?

P/S: I have attached the db zip file

Appreciated and regards.

 

[June7]

What have you attempted? Code in button click would just run INSERT action SQL to TBLTEMP. Or even directly to actual data table. Apply a filter on right form to only show records that don't have vendor/cost/quantity entered.

Or instead of SQL, code moves to new record row of right form and inputs info to fields.

Really not sure why you need to filter left form.

 

[Babycat]

Really not sure why you need to filter left form.

I need left form because I dont want customer scrolling down hurdred lines (in case there are so many item in TBLPRODUCT). So the filter will help them quickly address item they want.

What have you attempted?

Customer might cancel the import session anytime, even after they click "select" button, so I dont want to write data to db before customer confirm the session (or transaction). There will be confirm and cancel button on main form ( i have not added them in yet)

Code in button click would just run INSERT action SQL to TBLTEMP

I tried, but there is error, I dont know how to fix it. Could you please take look the code in attached db?

 

[mike60smart]

Hi

You need to look at redesigning your tables.

Currently all of your Primary & Foreign Keys are Text Data Types.

It is recommended that all tables should have an Autonumber Primary Key.

Then in any Related table the Foreign Keys should be Long Integer.

 

[Pat Hartman]

I don't understand where the records are going? What is the purpose of the temp table? If the whole point is to select the items the user wants to update, then just use the double click event or the select button you have to open the edit form.

In addition to mike60smart's comments, your relationships are incorrect. They should be PK to FK, NOT data field to FK. Not making the FK name the same as the PK name just confuses everyone. If you want to identify FK's (I don't since I only use the ID suffix for autonumber PK's so FK's automatically become obvious), then use a suffix. i.e. TypeID_FK

The second subform, has unbound controls so I have no idea where you are going with this.

Please try to tell us in words the business function you are trying to implement.

 

[Babycat]

I don't understand where the records are going? What is the purpose of the temp table? If the whole point is to select the items the user wants to update, then just use the double click event or the select button you have to open the edit form.

Sorry make you confused, the main purpose of this action is not to update qty and cost of item. It is for tracking. For exampe, transaction ID "12" tells me Product ID "VT01" was import on 27/07/2021 with qty 30pcs...
Each import session have an transaction ID, one transaction ID may contain more than 1 item.

All item in right subform will have same transaction ID (after customer click confirm button)

 

[Pat Hartman]

Then you are missing a table. The Transaction table would be the group "header" and hold the TransactionID, the vendor, and the date and whatever else is common. Then you would have a child table with the details for that transaction.

Rather than the filtering subform you are currently using, you might consider a single subform using cascading combos if you want to help to filter the detail item lists.