Increment Change On Invoice Number? 1

[supportsvc]

Hello,
So there's an odd incrementing that needs to occur for change on invoice number

Need to increment in a 14 "digit" required LineSeqNo field like so:
00000100000000
00000200000000

Invoice Numbers repeat for each line and the LineSeqNo increments for each line detail per invoice but not the typical 1, 2, 3, 4, etc ...

but as shown, it starts in the middle of a 14 "digit" LineSeqNo field.

How do I do a Make Table to auto assign the LineSeqNo for each change on InvoiceNo?

 

[MajP]

I am not sure if this is what you are after but taking your before data and assuming you have a unique sequence number to sort on I built a query sorted on sequence number. Then ran this code. I stored the lineSeqno as a normal number as Andy suggests, but formatted it for display purpose like you suggest.

Public Sub AddSequence()
  Const qryName = "qrySequence"
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset(qryName, dbOpenDynaset)
  Dim oldDocNo As String
  Dim docNo As String
  Dim counter As Integer
  Do While Not rs.EOF
    docNo = Nz(rs!documentNo, "Null")
    If docNo = "Null" Then
      counter = 0
    ElseIf oldDocNo <> docNo Then
      counter = 1
      oldDocNo = docNo
   Else
     counter = counter + 1
   End If
     rs.Edit
     rs!LineseqNo = counter
     rs.Update
    rs.MoveNext
  Loop
End Sub

My output displayed in a query

SELECT demoline.sequenceno, 
       demoline.postingdate, 
       demoline.batchno, 
       demoline.registerno, 
       Format([lineseqno] * 100000000, "00000000000000") AS lineSeqFormatted, 
       demoline.lineseqno, 
       demoline.documentno, 
       demoline.debitamount, 
       demoline.creditamount 
FROM   demoline 
ORDER  BY demoline.sequenceno;

[tt]
seqNo	PostingDate	BatchNo	RegisterNo lineSeqFormatted LineSeqNo	DocumentNo DebitAmount	CreditAmount
1	4/24/2018	MULTI	004903	00000000000000	0		$233,299.38	$0.00
2	4/24/2018	00424	004903	00000100000000	1	E000136	$0.00	$11.75
3	4/24/2018	00424	004903	00000200000000	2	E000136	$0.00	$11.75
4	4/24/2018	00424	004903	00000300000000	3	E000136	$0.00	$11.75
5	4/24/2018	00424	004903	00000400000000	4	E000136	$0.00	$11.75
6	4/24/2018	00424	004903	00000500000000	5	E000136	$0.00	$11.75
7	4/24/2018	00424	004903	00000600000000	6	E000136	$0.00	$11.75
8	4/24/2018	00424	004903	00000100000000	1	E000137	$0.00	$11.75
9	4/24/2018	00424	004903	00000100000000	1	E000138	$0.00	$11.75
10	4/24/2018	00424	004903	00000200000000	2	E000138	$0.00	$11.75
11	4/24/2018	00424	004903	00000300000000	3	E000138	$0.00	$11.75
12	4/24/2018	00424	004903	00000100000000	1	E000139	$0.00	$11.75
13	4/24/2018	00424	004903	00000100000000	1	E000140	$0.00	$11.75
14	4/24/2018	00424	004903	00000200000000	2	E000140	$0.00	$11.75
15	4/24/2018	00424	004903	00000100000000	1	E000141	$0.00	$11.75
16	4/24/2018	00424	004903	00000200000000	2	E000141	$0.00	$11.75
17	4/24/2018	00424	004903	00000300000000	3	E000141	$0.00	$11.75
18	4/24/2018	00424	004903	00000400000000	4	E000141	$0.00	$11.75
19	4/24/2018	00424	004903	00000100000000	1	E000142	$0.00	$11.75
[/tt]

 

[supportsvc]

Trying to add the Module

here's my query

SELECT [00425].PostingDate, [00425].SOJournal, [00425].RegisterNo, [00425].AccountKey, [00425].SequenceNo, 
       [00425].SourceModule, [00425].PostingComment, [00425].DocumentTYpe, [00425].DocumentNo, [00425].DocSeqNo, 
       [00425].ReceiptNo, [00425].BatchType, [00425].BatchNo, [highlight #FCE94F]AddSequence() AS LineSeqNo[/highlight], 
       [00425].Date, [00425].Time, [00425].User, [00425].DebitAmount, [00425].CreditAmount, 
       [00425].APDivNo, [00425].APCustNo, [00425].AltDocType, [00425].ARDivNo, [00425].CustomerNo
FROM 00425;

I've attached the db with the Table and the Query

https://files.engineering.com/getfile.aspx?folder=81efe837-203a-4792-8477-72f70900d628&file=DTR.zip

 

[MajP]

The procedure is not a function to be used in a query. I ran the procedure, and it wrote the sequences in the table. Then I made a query just to apply the formatting. I will look at your db in the AM.

 

[Andrzejek]

I am a little lost, and coming in late.

Don't feel bad. I am here from the beginning and I am lost, too.
I've tried several times to get the requirements straight, but 'no cigar'. :-(



---- Andy

There is a great need for a sarcasm font.

 

[MajP]

See if this makes sense.
First, (as Andy previously stated) I strongly suggest you use numbers for sequence numbers. I do not understand the deal with all of these zeros. These are real numbers they are a sequence vs something like an SSN which is really not a number (you can not reasonably do arithmetic on it). So I changed LineSeqNo to a long integer. Then I ran the code. Then I added another field LineSeqNo_text, and did an update query to put it into the 9 digit text form. Only did this as a demonstration. If this was my db I would not store a text representation of a sequence number in a db because you can always format any number in a form, report, or query. So I would store 2 but could show 00000200000000

 

[Andrzejek]

Just as a side note to MajP's statement:

So I would store 2 but could show 00000200000000

All DBs use this approach already without users / developers even realizing it.
Date, for example. You see today's date as 24 May 2018, but the DB keeps it in the field as a number 43244
You can display it anyway you want and Access does it for you 'auto-magically':

Dim lng As Long

lng = 43244

Debug.Print Format(lng, "mm/dd/yyyy")
Debug.Print "It is the " & Format(lng, "d") & " day of the month"
Debug.Print "It is the " & Format(lng, "y") & " day of the year"
Debug.Print Format(lng, "dddd")
Debug.Print Format(lng, "dddd, mmm d yyyy")

So my point is: don't get stuck on "This is what users want". Do what you need to do, and you can still give them what they want.


---- Andy

There is a great need for a sarcasm font.

 

[supportsvc]

MajP,
Thank you for trying to figure out how to get the result sought here.

Looked at it and that can work. couple steps to achieve the goal, really doesn't matter as long as the result is in the format required.

That said, how do you get the LineSeqNo prior to the LineSeqNo_text?
Can skip the part of assigning the records without a DocumentNo (AKA: InvoiceNo) since if having to use an update query to get teh LineSeqNo_text result, handling the records to update on Null is easy enough.
Just need to get the LineSeqNo to increment when change on DocumentNo. Thank you

Side note with some of the comments:
I know what's best, however and unfortunately this is a back office system set that way. Can NOT change it to what's ideal for a db.

That is not what's being sought here. It is what it is and no one can change that other than the company changes to a different back office system with better table structure. That is NOT going to happen. Please stop. I've seen this.
I do not understand why people have a hard time when it's specified that it's a back office system or a db set this way. That cannot be changed.

SO this is what it is and in order to get the data into this particular format, though not ideal, need to find a solution to fit the required field format. I really do not understand why anyone would be lost.

It was laid out specifically to what needs to be obtained. To get data into this particular table with it's table settings as it is. Unchangeable. Can not be changed.
Thanks. Focusing on the result sought after laying out the issue and scenario is more productive. Thank you.

 

[supportsvc]

@MajP

Hello,
I did not hear about on the question from the last post

That said, how do you get the LineSeqNo prior to the LineSeqNo_text?

I saw your db but do not see how the lineseqno was first assigned, prior to doing the lineseqno_text

Thank you

 

[MajP]

See my post of 23 May 18 22:42. It clearly spells out what I did.

 

[supportsvc]

Hello,
Yes I saw it as well as the upload of your db

I have the Public Sub
I am sorry, but I can't figure out how to apply that

I do not see the "counter" in your qrySequence in the LineSeqNo
They are just the direct fields from the table
Not seeing how to apply the Public Sub in order to have the LineSeqNo "count" by InvoiceNo

 

[supportsvc]

Ah, i missed this part

MajP (TechnicalUser)24 May 18 04:09
The procedure is not a function to be used in a query. I ran the procedure, and it wrote the sequences in the table. Then I made a query just to apply the formatting. I will look at your db in the AM.

Thank you!

 

[supportsvc]

Hello again,
For some reason it won't update the entire table of over 33k records.
It only processed 8730 records.
Not sure why?

Here's your code I am using with the related queries
qryAddSequence
Then run code
then run an update query
all works, except what's causing it to not apply it to all records?

Public Sub AddSequence()
  Const qryName = "qryAddSequence"
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset(qryName, dbOpenDynaset)
  Dim oldDocNo As String
  Dim docNo As String
  Dim counter As Integer
  Dim strCounter As String
  On Error GoTo errlbl
  Do While Not rs.EOF
    docNo = Nz(rs!documentNo, "Null")
    If docNo = "Null" Then
      counter = 0
    ElseIf oldDocNo <> docNo Then
      counter = 1
      oldDocNo = docNo
   Else
     counter = counter + 1
   End If
     rs.Edit
     rs!LineseqNo = counter
     'strCounter = Format(counter * 100000000, "00000000000000")
     'rs!LineSeqNo_Text = strCounter
     rs.Update
    rs.MoveNext
  Loop
  Exit Sub
errlbl:
  MsgBox Err.Number & " Counter " & strCounter
End Sub

Stopped on record 8731

DocumentNo LineSeqNo DelSeqNo
B224324 1 00000100000000
B224325
B224326
B224327

 

[MajP]

Is there an error message? Does qryAddSequence contain all of the records? Does it hang or just kick out?

 

[supportsvc]

Counter prompts 3052
Not sure what that number's from

 

[MajP]

Not sure what that means. Can you take a screen shot?