Custom Autonumber in Append Query

[David M C]

I use an Append query to create an invoice using the relevant details from an
order. The append query is run on a button click. Now I would like to get the
next available invoice number (using DMax). However, the InvoiceNumber field
has its Required property set to true, which I assume to mean, at the time
the record is created, this field must have a valid input (duplicates aren't
allowed either).

How then, at the time of my append query, do I populate a field with a
specific value?

Here is my current Appen query:

INSERT INTO ResponseInvoice ( JobNumber, OrderDate, Address, Address2, City,
County, PostCode, ClientJobNumber, PriorityID, CompletionDate )
SELECT WorkOrder.JobNumber, WorkOrder.OrderDate, WorkOrder.Address,
WorkOrder.Address2, WorkOrder.City, WorkOrder.County, WorkOrder.PostCode,
WorkOrder.ClientJobNumber, WorkOrder.PriorityID, WorkOrder.CompletionDate
FROM WorkOrder
WHERE (((WorkOrder.WorkOrderID)=[Forms]![WorkOrderOverview]![WorkOrderID]));


And here is the formula I would like to use to update the InvoiceNumber
field in the ResponseInvoice table:

=DMax("InvoiceNumber", "ResponseInvoice", "[JobNumber] =
WorkOrder.JobNumber") + 1

Is this formula correct? And how do I use it in my append query?

Thanks,

Dave

 

[David M C]

Done it:

INSERT INTO ResponseInvoice ( JobNumber, OrderDate, Address, Address2, City,
County, PostCode, ClientJobNumber, PriorityID, CompletionDate, InvoiceNumber )
SELECT WorkOrder.JobNumber, WorkOrder.OrderDate, WorkOrder.Address,
WorkOrder.Address2, WorkOrder.City, WorkOrder.County, WorkOrder.PostCode,
WorkOrder.ClientJobNumber, WorkOrder.PriorityID, WorkOrder.CompletionDate,
DMax("InvoiceNumber","ResponseInvoice","[JobNumber] =
[Forms]![WorkOrderOverview]![JobNumber]")+1 AS NextInvoiceNumber
FROM WorkOrder
WHERE (((WorkOrder.WorkOrderID)=[Forms]![WorkOrderOverview]![WorkOrderID]));


Is there any potential here for non-sequential InvoiceNumbers?

Thanks,

Dave