Re-ordering items, DAO, and bound forms

[mjdemaris]

I am working on a re-order process for a new app. I am combining our inventory app with our request for purchase app. Many users have requested a 'click to re-order' function, but with the new setup, I have some difficulties.

Attached is a picture of the tables in question.

What I am experimenting with now is this:

• using a check box on the original RFP (request for purchase) form
• check box is unbound, when checked a temp table is created and data from related records in the RFP_Request and RFP_Item_Request are copied.
• then the RFP form is closed
• then I would like it to reopen, with a new Request ID, and insert the data from the temp table into the form and sub form.
• currently both the main form and sub form are bound and linked via ReqID
• since I cannot insert data into the sub form without a related record in main form, and the ReqID is a primary key, auto-number, I am not sure how to go about this.
• I was attempting to insert data in the RFP form load event, and of course errors occur...unless I attempt to insert into RFP form first then the sub form?
• I am using DAO recordsets to move the data around

Here is my reasoning on the tables, if you happen to ask why I structured them this way.
For Inventory - each item could be purchased from multiple vendors, but they are essentially the same item.
For the Item_Request table - instead of storing the item information and part numbers in multiple tables, I came up with this.

Thoughts?
Thank you,
Mike

 

[The_Doc_Man]

since I cannot insert data into the sub form without a related record in main form, and the ReqID is a primary key, auto-number, I am not sure how to go about this.

Typically, you do a SAVE of the main record in the main form but then have a form AfterUpdate event to detect the assigned ID. AND, if you had a defined relationship between main and sub table (and used the appropriate linkage in the main and sub forms), attempting to create a record in the sub form would automatically pull in that link and pick up the main's ID to become a foreign key in the sub.