Unique Invoice# generatation

[Geekette]

I need a reliable way to generate an invoice#. Autonumbering is not the answer, as an invoice can have more than one item, and I want each assoicated item to have the same invoice#.<br>
My current solution is pretty klugy [and ineffective!]: I have a separate Invoice# table which (when a button is clicked on the Invoice form) will increment the current number and place it in the Invoice# field on the Invoice form. Unfortunately I can't make it work properly: I resorted to &quot;pasting&quot; the number and since the clipboard now holds the last 15 items, the pasted Invoice# remains the same after the first action.<br>
A better way would be to have the invoice item(s) entered first and then generate an Invoice# for the item(s).<br>
Any suggestions? Thanx in advance.

 

[JimHorton]

You are on the right track...we need to emulate Oracle's sequence here.<br>
Make a table 'tblSequence'--2 fields: ID, NextVal.<br>
In Validation for ID set it to 1, and set the first and only record ID to 1. No more records can be added to this table.<br>
<br>
Now, on the BeforeInsert of any forms using Invoice num., set Me!Invoicenum =<br>
GetNextId()<br>
<br>
Then, you need a public module with procedure:<br>
<br>
Function GetNextID() as long<br>
dim id as long<br>
ID = dlookup(&quot;Nextval&quot;,&quot;tblSequence&quot<br>
docmd.runsql &quot;update tblsequence set nextval = nextval + 1&quot;<br>
GetNextID = ID<br>
end function<br>
<br>
Put some error trapping if you like, and if you see a concurrency problem you can instead use DAO and retrieve/increment in an Edit block (instead of dlookup and SQL), with the table locked.<br>
--Jim<br>
<br>

 

[elizabeth]

You can use Autonumber if you use 2 tables, tblInvoice and tblInvoiceDetail. You can put the individual items into tblInvoiceDetail in a 1:n relationship.

 

[DougP]

Yeah have a look at the &quot;Orders.mdb&quot;<br>
It's in the sub folder under where Access is installed &quot;Office\samples&quot; folder. Use it or get ideas from it.<br>
It is set up for Invoices and Details and has just what Liz is refering to.<br>
Look at the table &quot;Orders&quot; and &quot;Order Details&quot;<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[Geekette]

thank you all!