duplicate a record with few updated fields

[dmatlock]

I have a form "quote log" made on the "quote log" table. It is linked via 'quote number' field to "quoted parts" table and subsequently the subform displays quoted parts. If you manually enter the parts into the subform everything is wonderful. The intention is to pull the part from somewhere (I am very flexible on how this happens) and automatically insert info into the "quoted parts" table with the current quote number. This will eliminate entering part #, description, cost, etc. every time.
I have tried many approaches but most will not update the quote number field so the child and parent table can link.

I have the exact same problem with a purchase order system. The user needs to be able to select the partnumber and pull up all the other fields that go with it, but have the record be a new record with the PO# to link Child (parts ordered) and parent (PO log). Again manually everthing works wonderfully, but thats alot of typing and alot of errors.

Thanks in advance for the help. My brain hurts over this one by now.

 

[nickjar2]

If i have understood correctly try this.
In your query for the subform, include the id from the child table instead of the master table. eg.
With a customer - Order query linked by Cust_Id. You would add the Cust_Id field from the Order table (Not the Customer table). This way, you key in the Cust_Id and all the relevant fields you have chosen will populate ie. Cust_Name etc.

Hope this makes sense and I hope I have understood correctly.

Nick

 

[dmatlock]

These things are so complicated that it is hard to get all the details in without writing a book. The subform is not a query, but a table linked to the main form by Order ID (or quote ID for the quote tables). When an order is pulled up, the parts that are associated with that order by order # also are pulled up on the subform. If the user manually enters parts for a new order, the order # is automatically filled out in the child table even though it is not displayed on the subform. Because of the link, no query is needed. (I have no idea how access is doing this! I simply created the parent and child table, linked them, and made the form and subform and manually it works!)

My problem is that I can't automatically append a new or copied record to the child table while in the form and have the linking field update automatically as it does when entered manually.

to explain from the usere side, he actually will have a 3rd subform that is from a part# list (I have tried various options on this theme) and here he finds the part he wants, then somehow I need to allow him to select the part and copy it into the parts ordered subform (child table) thus avoiding typing all the part details. Im sure people have used this technique all over the place. My head aches from fighting with it by now.

thanks for the help,