Hi Richard,

I've been spinning my wheels about how to (correctly) go from quote to a job.  In our business this is a many to many relationship (1 Job might have multiple quotes, ie. additional work was quoted and added to the job, and 1 quote could be associated with multiple jobs, ie. 2 separate services on one quote could be ordered on separate POs, creating separate jobs).

I have created a join table for this many to many relationship, called quote lines.  This way, each line item on a quote can be treated individually.  This quotelines Table includes all information specific to the line only.

I have created a great form and subform to create quotes and the quote lines.  Now, I need to be able to transfer this information to a job.  

On my job form, I envision a JobID (autonumber) and other items to be entered specific to the job in the header.  In the detail I'd like a drop down box of all quotes.  When a quote is selected I'd like details for the quote(customer name, etc) and the quotelines associated with that quote to appear.  Once the quotelines appear I would like to be able to add them, individually, to my job form in a list below.  I'd also like to be able to change my quote number in the drop down box, and view other quote details to add quote lines from multiple quotes onto the job, if necessary.  Am I explaining this well?  I'd like to purchase your work order seminar - which I think will help me with the rest of my database, but this item (or any similar example) does not appear to be covered.

Thanks!!! Alyssa

---

Reply from Richard Rost:

Yeah, this is a tricky one. It sounds like you have the many-to-many relationship set up properly. You just need help transferring data from one table to another one. Now, normally, I recommend you DON'T do this because you can always look data up (for example, if you know the CustomerID from the job table, you can always look up the customer's address). THE EXCEPTION is if you want to capture that data at the time the job is created, i.e. you need to know the customer's address AT THE TIME the job was created. That's when you need to store duplicate data.

To do this, I would recommend using a combo box (like you said) with an AfterUpdate Event that performs a DLOOKUP to pull in whatever information you need. I show this in my Access 302 class (for AC 2003 - it's older, but the VBA code is identical). I haven't gotten to this point yet in the new Access 2013 lessons, but I'll cover it when I get to the advanced material. There are also free AfterUpdate and DLOOKUP tip videos that you can watch.

Hope this helps.