Copying data from Table to other table (different levels)

[db-ches-one]

Well basically what I'm trying to do. I have 2 tables quote and quote items linked by quote id. when quote is approved i have(well want to lol) have a button that will take items (for that particular quote) and copies it to table invoice items...

now i've used 2 parametrized append queries to transfer 1st ID as an invoice ID and the another one to transfer/append as invoice items.
Trouble is the moment someone adds invoice before quote was produced auto id is increased and no longer i can append existing id (from quotes) to invoices... (kinda makes me wish i'd never really tested it haha)

I know that there are possibly other ways how to do it with perhaps using yes/no to mark quote as an invoice an go that way but then, what if some items would need to be added to invoice)(suppose some sort of underlying yes/no options to include individual items but that would be too much for the end user)

So to sum up does anyone know of a way to grabbing bunch of records (in this case quote items) and copying them to invoice items whilst creating (new)invoice id (essentially a new master record if you like) and matching them together so they are in (parent-child) relationship, cause i don't know and I'm loosing will to live

thanks
(phewww my first post lol)

posted a pic of relationships which should make it clear(er)

 

[bparkinson]

You can do it in one INSERT statement, like this. First, get the InvoiceID and put in a variable, iInvoiceID int.

Then make a SQL Statement like this:

"INSERT InvoiceItem (InvoiceID, Items, ProcePerItem, Quantity, TotalPerItem) SELECT " & iInvoiceID & ", Items, ProcePerItem, Quantity, TotalPerItem FROM QuoteItems WHERE (some appropriate WHERE claus here)"

This will insert the number of rows the WHERE clause returns, each with the same InvoiceID.

 

[db-ches-one]

oh right cheers for that,

i've never done vb before at all, so how would i make the variable and how would I make it as a query or something else>?

 

[bparkinson]

I would stop right here, buy a book on using MS Access, and learn VBA. If you have never written any VB code, it's going to be hard to communicate how to do things.

 

[db-ches-one]

I know access just avoided vb as much as possible

 

[db-ches-one]

On the other thought is there any other way to do that?