Re-ordering items, DAO, and bound forms (1 Viewer)

[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.

 

[Pat Hartman]

--An unbound checkbox to initiate the action is OK but I would use a button since a button implies some action whereas a checkbox implies some status.
--Always save the current record if it is dirty before initiating any action based on it.
--I would use DAO ti insert the new request because that way it is easy to obtain the new autonumber. Pickup the fields you want to copy from the form and fill in the ones that have to change such as the order date and who placed the order with the new values.
--Capture the autonumber generated by the .AddNew action so you can use it in the append query for the details.
--Create an append query to copy the details from the OLD order and use the new OrderID you captured above as the OrderID.
--IN the append query, join to the product table so you can pick up the current price.
--Run the append query
--Reposition the form to the new record