Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create Sequential Line numbers Query

Posted on 2014-09-23
4
Medium Priority
?
1,385 Views
Last Modified: 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.
0
Comment
Question by:TechGuise
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 40339769
You could update the sequential number field in the table, after creating the table for export.  Start with saving 1 to the first record, then incrementing the number by 1 for each subsequent record.

Public Sub SeqNos()

   Dim rst As DAO.Recordset
   Dim lngCounter As Long
   
   lngCounter = 1
   
   Set rst = CurrentDb.OpenRecordset("tblTest")
   
   rst.MoveFirst
   
   Do While Not rst.EOF
      rst.Edit
      rst![SeqNo] = lngCounter
      lngCounter = lngCounter + 1
      rst.Update
      rst.MoveNext
   Loop
   
End Sub

Open in new window

0
 
LVL 58
ID: 40339812
Somewhat along the lines of what Helen suggested, but:

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.
0
 
LVL 58
ID: 40339817
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.
0
 

Author Closing Comment

by:TechGuise
ID: 40339828
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!!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question