Solved Number inceament based on other table value

john_gringo

Registered User.
Local time
Today, 21:57
Joined
Nov 1, 2011
Messages
87
Hi, I'm using a query to append data to a table for both invoices and credit notes in the same list.
On this table, I need to have a document number field increased based on the document type.
In this table, I have fields, DocType (values INV or CRN) and DocNumber
For DocType Invoice, I need numbering 1,2,3,4,5,6,7.........increased every time invoices are appended and
for DocType CreditNote, I need to have again numbering 1,2,3,4,5,6........increased every time credit Notes are appended.
There is a table DocumentType in the DB where I could store the last number for each type but as soon as I call it in my query DocNum+1 it stores the same value to all of the lines it appends.
Is there away to do this?
Any help will be much appreciated.
Thanks
Jiannis
 
custom numbers need to be calculated in VBA so you would need to create two functions. One for each series. Or one function that returns the value for an INV or a CRN.

The function sill do the typical dMax() to find the highest existing value and add one to it.

Normally you would be using a bound form (don't know why you're not) and you would be generating the sequence number in the form's BeforeUpdate event. I've included a sample that shows how to generate custom numbers based on other values. You can use the same concept to develop your function. However, I would really reconsider where you generate this number because you should probably be doing it the way this sample does.
 

Attachments

make sure that DocumentType table has at least a record (both INV and CRN = 0).
then create a function that will return the serial for INV or CRN:
Code:
Public Function newSerial(var As Variant) As Long
    Static rs As DAO.Recordset
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset("DocumentType", dbOpenTable)
    End If
    With rs
        .MoveFirst
        .Edit
        .Fields(var) = Nz(.Fields(var), 0) + 1
        .Update
        newSerial = .Fields(var)
    End With
End Function

on your query call the function (either pass 'INV' or 'CRN' depending on the docType:

Insert into yourTable ( [dateField], docType, documentNo) select date(), 'CRN', newSerial('CRN');"
 
Thank you both.
As I am new to access I am trying to understand the workaround of your proposals.

@Pat Hartman.
I am not using any form. Just after orders from different clients are made and are ready to be invoiced the Append query inserts multi records to the table invoice and needs a unique number for each. Till now I was using an auto number as the table included only invoices.
Now I need to include also Credit notes in the same table with their numbering sequence. Credit Notes will be entered manually as it is rear so yes
I can use a form for them. The issue is for the invoices query to give the correct number.

@arnelgp. Here is my table (DocNum) is the maximum number in the table Invoices for each DocType)
DocumentType

DocIDDocNameDocAbbDocNum
1​
InvoiceINV
3245​
2​
Credit NoteCRN
4​
3​
Purchase InvoicePINV
0​
So as I mention above Invoices are prepared automatically and need a code to create the next number so I will make a function for INV.
I did what you propose but i receive an error item not found in this collection for .Fields(var) = Nz(.Fields(var), 0) + 1
 
Yes, I manage to implement your code/module in my DB but the issue is that the query that Appends data to the table returns multiple records (depending on the ready orders at the time, it can be 1 or 2 or 3 or...101) and it gives the same next number to all (!docnum = Nz(!docnum, 0) + 1. ) and it will increase the number to the next batch of orders. It seems I have to run a loop for every record, right?
Maybe something like open a recordset for the new records that need to be appended and run an append query for each individual record till EOF?
I'm I, right? Trying to learn :)
 
can you upload a sample db to work with?
which table/tables hold the records to be inserted?
and to which table to insert?
 
I'm not sure you are going to be able to do this in a query. The problem is that a query runs inside a transaction and so the updates are not committed one at a time, they are committed all together at the end. So using the dMax() to find the highest value sequence number will return the same value for every row in the query because the new ones have not yet been committed.

My assumption was that you were adding the records one at a time and for some reason using an append query rather than a bound form. To do what you want to do, you need to use a DAO update loop. Start by using the two dmax's to find the last value and save the result to variables. Then you need to open a recordset of all the records you want to invoice using whatever criteria is necessary. There will be no seeks or find first or find next. The recordset will contain ONLY the records you want to invoice. Then one by one, increment the saved value and use it in the .addnew as the sequence number.

You might be able to use a query if your function defines a static variable that will retain its value between executions. I don't have time now to try it for you. Maybe later.
 
@Pat Hartman
That is what I was thinking about if you check my previous post.....
Maybe something like open a recordset for the new records that need to be appended and run an append query for each individual record till EOF?
I'm I, right? Trying to learn :)

Just trying to do it because I end up with the issue you mention, all records with the same inv number till the next batch.
I will try. it is a great exercise for me to make it work.
 
you use your Insert query, but don't add the Invoice Number yet (make it Null).
i added another query (update query, addSerial) that will insert the number.
see the query and the modified newSerial() function.
also see your form how the queries are run.
 

Attachments

hi arnelgp
No, It is not working, instead of your previous method that works perfectly. I add a loop. Check the code attached.
Furthermore, the new function adds the same number to all invoices.

But I do really appreciate your precious help.
 

Attachments

No, It is not working, instead of your previous method that works perfectly
did you Run your Form?
because on my test this is what i get?
Inkedinvoice.jpg
 
Yes!
I see you use [InvoiceType] (1.1 but this in not an index of table InvoiceType) in the query.
Invoices!EidosParas should be = InvoicesType!Abbreviation in my opinion and if I understand the code well.
But I get null.
If I change the creteria in query 0 instead of Is null I get an empty field
Did you send me the right DB?
 

Attachments

  • Screenshot_2.png
    Screenshot_2.png
    85.5 KB · Views: 380
i am using InvoiceType? (the left most = 1) and look it up in Invoicetype.ID?
what is InvoiceType anyway?

i also tried EidosParas field, but all i get is "???" (may mean it does not recogized it as "TIM").
 
what is InvoiceType anyway?
It is for the Greek TAX system 3 kind of Invoices Domestic, Abroad, etc......

i also tried EidosParas field, but all i get is "???"
It was In the Greek language I should change it to English. The connection should be
Invoices!EidosParas = InvoicesType!Abbreviation
 
Maybe something like open a recordset for the new records that need to be appended and run an append query for each individual record till EOF?
You would NEVER run a separate insert query for each record. You would have two open recordsets. Read from one, add to the other using .addNew/.Update
 
if you already "converted" to English the content of EidosParas, you
can pass this to the function (see addSerial query, on how i passed the field).

note, edit your table structure first and add index to Abbreviation field in InvoiceType table:

Indexname: Abbrev
IndexField: Abbreviation

modify the code to:
Code:
Public Function newSerial(typ As Variant) As Long
    Static rs As DAO.Recordset
    Dim lngSerial As Long
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset("InvoiceType", dbOpenTable)
        rs.Index = "Abbrev"
    End If
    With rs
        .Seek "=", typ & ""
        If Not .NoMatch Then
            lngSerial = !lastnumber + 1
            newSerial = lngSerial
            .Edit
             !lastnumber = lngSerial
            .Update
        End If
    End With
End Function

now run your Form.
 
You would NEVER run a separate insert query for each record. You would have two open recordsets. Read from one, add to the other using .addNew/.Update
Hi Pat.
Not to do this you mean

Code:
            With Rst
            Do While Not .EOF
            lngSerial = newSerial("Timologio")
            DoCmd.RunSQL "INSERT INTO Invoices ( TOT_VALUE, DA_NUM, InvoiceNumber ) SELECT APOST_NUM.TOT_VALUE, APOST_NUM.DA_NUM," & lngSerial & "  FROM EkptosiSD RIGHT JOIN (InvoiceDetails INNER JOIN APOST_NUM ON InvoiceDetails.ApNumID = APOST_NUM.ID) ON EkptosiSD.ID = APOST_NUM.PH_ID WHERE (((APOST_NUM.PAR_OK) = Yes) And ((APOST_NUM.STATUS) = 2)) GROUP BY APOST_NUM.TOT_VALUE, APOST_NUM.DA_NUM, APOST_NUM.ID, " & lngSerial & " HAVING (((APOST_NUM.DA_NUM) Is Not Null) AND ((APOST_NUM.ID)=" & Rst!ID & "));"

            .MoveNext
   
                Loop
            End With

Else How????????
Can you explain this more as I didn't understand what you mean? Thanks
 
Last edited:
if you already "converted" to English the content of EidosParas, you
can pass this to the function (see addSerial query, on how i passed the field).

note, edit your table structure first and add index to Abbreviation field in InvoiceType table:

Indexname: Abbrev
IndexField: Abbreviation

modify the code to:
Code:
Public Function newSerial(typ As Variant) As Long
    Static rs As DAO.Recordset
    Dim lngSerial As Long
    If rs Is Nothing Then
        Set rs = CurrentDb.OpenRecordset("InvoiceType", dbOpenTable)
        rs.Index = "Abbrev"
    End If
    With rs
        .Seek "=", typ & ""
        If Not .NoMatch Then
            lngSerial = !lastnumber + 1
            newSerial = lngSerial
            .Edit
             !lastnumber = lngSerial
            .Update
        End If
    End With
End Function

now run your Form.
??????? e What. If I understand correctly, I entered the table and to Abbreviation Field in its properties i change index to Yes(duplicates OK)
Where to add
Indexname: Abbrev
IndexField: Abbreviation
Why not use Table's Primary insted?
 

Users who are viewing this thread

Back
Top Bottom