This would be the way I might do it. I think you'll get some different answers though.
1. I have an autonumber field in my main Invoice data table (say it's called InvoiceID). And InvoiceNumber would be a primary key to not allow duplicates.
2. I have a "search" form which has a combobox (ie. MyInvoiceCombobox) with the InvoiceID and InvoiceNumber. InvoiceID being the bound column (or InvoiceNumber - either one). When the user selects an invoicenumber from the combobox, I open the form....
i.e. Docmd.openform "MyInvoiceForm",,,"[InvoiceID] = " & me!MyInvoiceCombobox & ""
InvoiceID would be a field on my MyInvoiceForm.
3. On the MyInvoiceForm, I have a button which when they click it, it generates a new invoice number (i.e. in the field InvoiceNumber on the form) or a popup form asking them to enter the new InvoiceNumber and then runs an append query to the main Invoice data table which has criteria in it where InvoiceID = Forms!MyInvoiceForm!InvoiceID (left unchecked in the query so it doesn't append this field - since it's an autonumber field) and also has an expression column (ie. InvoiceNum: Forms!MyInvoiceForm!InvoiceNumber (or from my popup form) which appends to the InvoiceNumber field.) and all the other fields would be in the query for the append (except of course InvoiceNumber because it's appending the expression to the InvoiceNumber field.) (This all also could easily be done via the "search" form where the user selects an invoiceNumber to "clone" (InvoiceID being the bound column in the combobox/listbox) and another field for the "new" InvoiceNumber - which the expression in the query will point to.)
The point here is that the append query has criteria pointing to some InvoiceID (my autonumber field) on a form and an expression from some form with the new InvoiceNumber.
OR I would write it out in code verses doing a query (depends on how many fields and if I'm in the mood to write some code verses a query.)
4. From there, I might reopen the form with criteria of the new InvoiceNumber or do a gotorecord or find....etc..
Last edited by pkstormy; 07-08-07 at 02:17.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)