My question is, how do I setup the Line_Number to count automatically + 1 per invoice_number. Right now Im having to manually enter Line_Numbers, which is pretty darn conducive to human error. Id also like to add something that doesnt allow duplicate line numbers per invoice if possible. I know that if I have a automatic count that shouldnt happen, but you know, accidents happen with human hands.
Thank you for any help, the 3 books I have do not address this and it's hard to find this kind of thing via Google.
P.S. I havent done a search on these forums yet, Im going to do that now while anxiously hoping for replies.
Theres no easy way of doing it, you are going to have to write some vba to sit behind the form.
An approach would be to query the table each time a new record is added and retrieve the max(itemno) where inoviceno=currentinvoice. This could be written as a function and called on the form (or controls) "beforeinsert" event.
the precise phrasing you woudl use depends on whether you are using ADO or DAO to read the recordsets.
The sql would be something like
"SELECT Max(MyItemNo) AS MaItemNo from tblInvoiceItems where InvoiceNo=CurrentInvoiceNo;"
You would need to add one to that and then move that value to the bound control in the item field.
You could modify the procedure to cater for cancelled items (eg if there was a cancelled item) by iterateing through a recordset looking for a gap (select ItemNo from tblInvoiceItems where InvoiceNo=CurrentInvoiceNo order by ItemNo;"
to protect agianst duplicate item numbers make the primary key in the item table a composite key (InvoiceNo+ItemNo). Put some error handling code in the forms before/afterupdate events and trap from a duplicate record error message - no doubt someone can tell us what that error code is. But theoretically at least if you do not allow the userr to put in the item number then providing the sql is correct then you shouldn't need to trap the error. Also I would suggets it is unlikely that you will get a write conflict (in practise it would be highly unlikely that 2 people would wat to update the same invoice at the saem time. But its always prudent to trap soemthing that could make you application headbut.