Hi experts out there.
I've a Sales Order ID which is an autonumber. I convert this Sales Order to a Delivery Order many times. [One to Many] because of Partial Orders.
The first time I append, only one Delievry Order is created, but at the second time I append, two identical Delivery Orders are created. How to stop this duplication?
Can I create a suffix to the autonumber with a increment number, so the append query transfer only the latest Sales Order over. and marked the sales Order [DOconvert] "yes"
so the next time the append query will not append any Sales Orders with a "Yes" Any help is very much appreciated.

Actually you need a DeliveryID for the Delivery table. That will be unique.
In the Delivery table, your SalesOrderID is a Foreign key back to the Sales Order table.

Yes, I have a relationship between Sales Order and the Delivery Order. I have a DeliveryID in the Sales Order and a SalesOrderID in the Delivery Order.

Not sure I understand your setup here:



Because you have a one to many relationship, you only need a SalesOrderID in the Sales table, but you need both a DeliveryID and SalesOrderID (Foreign Key) in the Delivery table.

What does your Append query look like right now?

Hi Darnellk, thanks for your help. I found this codes shown below. How do you modify the to suit my usage.?
I want only the [CODOConvertID] to increment by 1, but it does a total increment of all the forms.
Me!CODOConvertID = Nz(DMax("[CODOConvertID]", "tblConfirmation", "ConfirmID = forms!frmConfirmation!ConfirmID"), 0) + 1

Much appreciate you help.

This is quite a bit different than your first question, and may need its own topic depending on what you are looking for. I don't understand if the question you're asking now is related to the first one or not, and it is not clear to me what you're asking.

Is this your code or an example you found that you are trying to adapt to your situation?



Please try explaining what you want accomplished a little more.

Yes, it is a sample which I like to adopt. Thanks for your patience with me. I have a Sales Order with an autonumber. The Sales Order can be converted to many Delivery Orders.
I just want another "autonumber" in the Sales Order to keep count how many times the Sales Order has been converted to Delivery Order. This is to prevent duplicates.

I activate the codes at the [Convert to DO] Icon - OnClick Event. Pleas ehelp

What George and I were suggesting is something like the following:

tblSalesOrder
SalesOrderID (autonumber)
SalesOrder
Customer (may be an ID number linked to a Customer table)
SalesDate
etc.

tblDeliveryOder
DeliveryOrderID (autonumber)
SalesOrderID (number - relationship linked to tblSalesOrder.SalesOrderID which is a Foreign Key)
DeliveryMethod
DeliveryDate
etc.

I think I see what you're asking now, and the way I could see doing what you want is this:

tblSalesOrderDelivery
SalesOrderDeliveryID (autonumber)
SalesOrderNumber (on click of new Sales Order this would be automatically numbered)
DeliveryNumber (on click of new Delivery Number this would be automatically numbered)
etc.

The expression you posted should work and would look like this:

A "New Sales Order" button on your form would have a click event as follows:



A "New Delivery" button on your form would have a click event as follows:

Wow, wow guys this is fantastic. Thats what I want, right at target. Thanks so much.