Partial serial number?

[EntryLevel]

Ok this should be easy...

I have invoices table there are 2 ID's there, Invoice ID and then the shipping ID. These must be separate ID.

Every invoice contains Invoice ID, so I just set that to auto serial number. However only some invoices have shipping ID.

How can I get the shipping ID to be either empty or next value... the ID should autofill if I type something in the shipping address box. It should also autofill when clicked...

How can I get this kind of serial to work, where it doesn't auto fill it and looks up the last record with some other than empty value in it and plusses one?

[comment]

Is it important for the ShippingIDs to be consecutive? If not, you could simply use the InvoiceID - or nothing, if there's no shipping for that invoice.

[EntryLevel]

Is it important for the ShippingIDs to be consecutive? If not, you could simply use the InvoiceID - or nothing, if there's no shipping for that invoice.

Yes they have to be consecutive.

[comment]

Then I would suggest you add a Shipping table and create a related record there whenever an invoice is shipped. You can do this automatically, for example by keeping the shipping address in the Shipping table and allowing creation of related Shipping records in the relationship's definition.