I do hoip you are not using 'invoice #' as your column name... # is a reserved symbol and may well cause you problems.
001 is a formatting issue, not a numbering issue, 001 is a text representation of a number.
Assuming you want contiguous numbers starting from 1, short of leaving this up to your users there is not an easy fix for this.
presuming that only one person will be entering order details on a specific invoice at the same time (you can have multiple people entering invoices at the same, BIT ONLY one per invoice), this should work
you have a column called 'LineNo' which is mapped to a text box control called tbLineNo.
tbLineNo is locked (so users cannot add/edit or change
your invoice number in the parent form (MyInvoiceForm) is in a control called tbMyInvoiceNo)
in the first control that a user would enter data place some code that finds the next available number for that invoice number place soem code which calls the function below to get the next available line no for the current invoice
eg in the forms before insert event
tbLineNo.value = GetLineNo(forms!MyInvoiceForm!tbInvoiceNo)
replace any reference to my.... with whatever the correct name for your columns/tables
Private function GetLineNo(InvoiceNo as integer) as integer
'function to return the next available line no for a specified invoice number
if the invoice number is invalid or not found then return NULL,mas the user is playing silly.....
'OK so how can we validate the invoice number
if not isnull(dlookup("MyInvoiceNo","MyInvoices", "MyInvoiceNo =" & tbInvoiceNo)) then 'we found the invoice in the invoices table
'ok so we found the invoice in the invoices table, now we need to find the current maximum line number for this invoice
getlineNo = dmax( "MyLineNo", "MyOrderDetails","MyInvoiceNo = " tbInvoiceNo)
if isnull(getlineNo) then this is the first line for this invoice
GetLineNo = 1 'set up or default return value
GetLineNo = GetLineNo + 1 'add one tot he current value
else 'we didn't find the invoice number in the invoices so return null (effectivley not found)
getlineno = null
if you have been unfortunate enough to use spaces in your column & table names delimit those using square brackets, eg:-
[my table].[my column]
or better yet do the right thing and redesign the db right now and get rid of this dirty habit.. spaces are a reserved symbol in table/column/form/report (in fact any object
you can use underscore or CamelCase to separate words
the abive code is air code, its untested, untried, will almost certainly contain spellign mistakes, typos, may contain logic errors, may not compile. you will need to work out how to clear thise faults yerself....
I'd rather be riding on the Tiger 800 or the Norton