Create Sequential Line numbers Query
Posted on 2014-09-23
I am creating invoices on a Main Form, and Invoice Items on a SUB-FORM.
The user has the ability to add as many line items as they wish, and they have the ability to go back and delete certain lines they have created. So for example they may have originally added 8 items in the Invoice Detail (8 records), but then went back and deleted the 3rd and 7th items.
The user clicks a button on the main form and the VBA code clears out a TEMP table, then appends this invoice data to that TEMP table (I'm using a temp table so I can get the field names exactly like I want them). Then it creates an EXPORT CSV file from the TEMP table.
I'm exporting the data to be imported by PeachTree.... PeachTree has several mandatory fields... one of which is a challenge.
*** Each line item must have a unique sequential number starting at "1"
If I make the field in the TEMP table an AUTONUMBER it doesn't go back to "1" until you compress the file.
Using something like the following works when the records are created on the FORM, in BEFORE UPDATE...;
DMax("lineNumber", "tblItemTable", "InvID = xx") +1
But they can become "un-sequential" if the user deletes lines.
Wish there were something as simple as "RECORD COUNT" or "RECORD NUMBER" that I could put in the QUERY.... but assuming there isn't based on my unproductive googling.
Thanks for any help in advance.