Append Query with added intel

[MC42015]

Hi! can you help?
I am looking to write a parameter in an append query to assign a value to one of the fields I leave blank.
I need to replace CHID with IID
Example - I have an NB (not billed)order, CHID 10, with three lines, description, price, etc:
CHID 10,1,Tomato Sauce,2.00,EA
CHID 10,2,Spaghetti, 1.00,EA
CHID 10,3,Chianti,7. 99,EA
When my corporate office generates an invoice for this, the invoice has it's own IID, which i identify before I append the query.
Is there a way to write into the SQL what number to use in IID?
I am only appending one CHID at a time.

INSERT INTO ChinaOrderDetails 
    ( CHQTY, CHPN, CHPN2, CHPNREV, CHPNNOTE, CHPRICE, CHUNIT )
SELECT ChinaOrderDetailsNB.CHQTY, 
    ChinaOrderDetailsNB.CHPN, 
    ChinaOrderDetailsNB.CHPN2, 
    ChinaOrderDetailsNB.CHPNREV, 
    ChinaOrderDetailsNB.CHPNNOTE, 
    ChinaOrderDetailsNB.CHPRICE, 
    ChinaOrderDetailsNB.CHUNIT
FROM ChinaOrderDetailsNB
WHERE (((ChinaOrderDetailsNB.CHID)=[What Order ID are you moving?]));

[twinnyfo]

MC42015,

I am not exactly sure what you are asking, because you are not INSERTing either the CHID or the IID into your table, so I am not sure why you need it.

Also, how are CHID and IID related? Is IID a field in your Table ChinaOrderDetai lNB? If not, then there is no point in incorporating it into your code.

Perhaps we need a few more details in order to help you out with this one.

Thanks!

[MC42015]

OK - sorry for the confusion! You are not seeing that because i am manually going into the table and typing it in.
This is the reason for my question.
Two tables make an Order - tblOrder CHID - date, order number, order date, etc; and tblOrderDetails relates to CHID with qty,line item,price,etc.
My process is an order is recorded with CHID and I monitor it there until it becomes an Invoice IID. Two tables make an Invoice, exact structure as the order, so my append query is inserting the tblOrderDetails from CHID into the tblInvoiceDetai ls, but now needs IID.
When I run my append, I want it to ask me: [what is the IID?] and insert it into each line that is going into tblInvoiceDetai ls.

Hope that makes sense

[twinnyfo]

Why not add IID to tblOrders--then when you identify the IID, that IID will be associated with CHID.

Again, if you have a proper relationship established between your two tables, you need merely to have main form, bound to tblOrders, with corresponding controls for your required fields. Then, you have a sub-form, bound to tblOrderDetails . In the properties for that subform (once it is on the Main form), you must set the Parent-Child relationship to be CHID. Then, every time you add a new CHID, and add items to the order, tblOrderDetails will populate with new records based upon the new CHID.

There should (almost) never be a need to create forms the way you have them set up now. Not to mention that I can't see how your code would have ever produced satisfactory results. Hence your question.

Hope this hepps.