Create Sequential Line numbers Query
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I should also add that you can do this with straight SQL assuming each row has a unique value from low to high (you can get a count of all rows prior to the one your on with a subquery), but I find the other technique far faster.
Jim.
Jim.
ASKER
Just got through implementing Helen's suggestion, it worked great.
May try other method Jim when the pain of messing with this for several hours wears off.
Thank You Both!!!
May try other method Jim when the pain of messing with this for several hours wears off.
Thank You Both!!!
1. Declare a global long variable.
2. Before executing your query, set that variable to 0.
3. In the query, call a function that takes a field name as an argument. Increase the count by one and return the value.
RowCount: GetLineCounter([MyField])
Doesn't matter which field nor will you do anything with it. Just needs to be there so the query engine calls the function for each row.
This works best when appending records. If tied to a form and you move back and forth through the recordset, the numbers will be goofed up.
Jim.