By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Row Numbering Code?

P: 2
I have an Access Table that contains the following:

InvoiceNbr
InvoiceLnNbr
ItemNbr
Quantity
Price

The table contains values in all the fields except InvoiceLnNbr and I'm trying to figure out how to populate this using a Query, Module, etc.

I basically want to assign a number to each line item in the invoice starting at 1 and incrementing by 1. So for example my data set looks like:

InvoiceNbr InvoiceLnNbr ItemNbr Quantity Price
ABC1234 JAD234 3 $9.95
ABC1234 QDH288 5 $12.75
ABC1234 HDY239 1 $6.25
ABC3486 DER823 2 $17.20

And what I want the Query, Module, etc. to do is make it look like this:
InvoiceNbr InvoiceLnNbr ItemNbr Quantity Price
ABC1234 1 JAD234 3 $9.95
ABC1234 2 QDH288 5 $12.75
ABC1234 3 HDY239 1 $6.25
ABC3486 1 DER823 2 $17.20

I cannot seem to find any function like this for Access, but can do a simple =if statement in excel to handle this however, I prefer to have the entire solution in Access.

Any ideas?

Thanks,

Wally
Feb 27 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,315
You'll have to do it through code, do you want to continue?
Feb 28 '07 #2

P: 2
No problem with the code, can the code be executed as part of a macro or some other function as the table isn't populated until after a set of queries runs via a macro.

I'm not that sophisticated with code (VBA, etc.) just the basics generally, so if you have an example that would be helpful or if you can point me in the right direction to view a sample I could likely modify it for my use.
Feb 28 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
Something along these lines.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. Dim rst As Recordset
  4. Dim Old As String
  5. Dim i As Integer
  6.  
  7. Old = ""
  8. Set rst = Me.RecordsetClone
  9.  
  10. With rst
  11.     Do While Not .EOF
  12.         If Old = .Fields("InvoiceNbr") Then
  13.             i = i + 1
  14.         Else
  15.             i = 1
  16.         End If
  17.         .Edit
  18.         .Fields("InvoiceLnNbr") = i
  19.         .Update
  20.         Old = .Fields("InvoiceNbr")
  21.         .MoveNext
  22.     Loop
  23. End With
  24.  
  25. End Sub
Feb 28 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
The other solution is not to store this data at all. If the Invoice is being printed as a report then you can simply have a running sum to show the line number of each item.

Mary
Feb 28 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.