Sequential field

[dan-cat]

Hope you can help... I am trying to create an append query which will paste selected records from one table(x) to another table. No problem there... however I have a field called "InvoiceNo" in both tables. I want to be able to update the field "InvoiceNo" in table Y with sequential numbers running on from the last Invoice Number in table X.
ie. if the last Invoice number in table X is 1001 and the query selects 5 records to past into table Y Im looking to update the Invoice No for these 5 records in table Y as 1002,1003,1004,1005 and 1006 in turn. Here is what I have done so far:

INV: nz(DMax("InvoiceNo","tblX","Status='Invoiced'")+1,1001) - append to InvoiceNo in tblY.

This manages to update all the fields in tableY with the next invoice number - ie all 5 records will show 1002 as the InvoiceNo but Im looking for the InvoiceNo to scroll on one for each record.
Is this possible at all?
Thanks
Dan

 

[Adrianna]

I would create a seperate field that will track the additional entries. Having the field increment is great, but in order to allow the users to view all of the records with a base number, you'll need to allow them to have that common field.

So, have an invoice field and an increment field seperate. You can set them to Multi-index and still allow users to query by the Invoice number and view all of the incremental records!

 

[dan-cat]

ThankYou I will give that a go