Will this database be hosted over a network so that multiple users might be needing to create invoice numbers at the same time? (If there is even the most remote chance that this might be necessary in the future, answer yes.)
There's a probable chance, yes and futureproofing it will be easier now than in a year or so.
What do you suggest?
We can use a related field with an auto-entered serial number to generate the numeric part of your invoice number. That method will not produce duplicate values if two or more users are generating invoice numbers at the exact same time.
Define a serial number field, QuoteID in your original table if you do not already have such a field defined. (If you don't have such a field, also use Replace Field contents with the serial number option to put a serial number in this field for all existing records and make sure to select the update data entry option when you do so.)
Define a new table, InvoiceNumbers, with two fields:
Define InvoiceNumber as an auto-entered serial number field.
Relate your current table to this new table like this:
Quotes::QuoteID = InvoiceNumbers::QuoteID.
Enable "Allow creation of records via this relationship" for InvoiceNumbers.
Now, add a button on your Quotes layout that performs this simple script:
Set Field [InvoiceNumbers::QuoteID ; Quotes::QuoteID ]
This button will create a related record in InvoiceNumbers for the current Quote record if one does not already exist. (If one already exists, nothing gets changed.)
Now a calculation field such as: "INV & Right ( "000" & InvoiceNumbers::InvoiceNumber ; 4 )
can be defined in Quotes to display your invoice number.
Thanks so much for your reply, I'm nearly there.. How do I edit the script so that I can just paste in that line? I can add Set Field [InvoiceNumbers::QuoteID] but can't get the ; in for the rest of it.
Click the second specify button. The upper specify button allows you to choose the target field. The lower one opens up specify calculation where you can enter a calculation for the calculated result.
Ok, when I put in InvoiceNumbers::QuoteID ; Quotes::QuoteID into the Specify Calculation it returns "An operator (e.g. +,-,*,...) is expected here."
Click the upper specify button and select InvoiceNumbers::QuoteID from the dialog that pops up.
Click the lower specify button and enter Quotes::quoteID
Do not enter the ; at all.
Ok, so close, got the value working and relationships are ok. Just the calculation field now. Invoice Number & Right ( "000" & InvoiceNumbers::InvoiceNumber ; 4 ) is what's in the calculation field but I'm getting ? as the value implying my calculation isn't working correctly..? Any ideas?
Scrub that, got it, did the same as i did with the specify calculation.. Removed the Invoice number & bit from the start... Thanks so much for your help! You've made my tax invoicing a lot easier..