Auto Generate Number Error

[steve21nj]

I have specific numbers that are generated based on: [location],[year],[number] which is represented [LRS-13-60000]. Other locations may have [ABC-13-70000]. For each new purchase order, +1 is added to the number. Right now I am attempting to have my table [Codes] store the values of location, year, and number, as well as last number assigned to generate this number.

Any ideas?

Option Compare Database
Function NewRequisitionNumber() As String
    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim rstsource      As DAO.Recordset
    Dim LNewRequisitionNumber   As String
    On Error GoTo Err_Execute
 
    Set db = CurrentDb()
    Set rstsource = db.OpenRecordset("Codes", dbOpenTable)
 
    'Retrieve last number assigned for Requisition Number
    LSQL = "Select Code_Desc from Codes"
    LSQL = "Select Last_Nbr_Assigned from Codes"
    LSQL = "Select StartingRange from Codes"
    LSQL = "Select YearValue from Codes"
    Set rstsource = db.OpenRecordset(LSQL)
 
    'If no records were found, create a new Requisition Number in the Codes table
    'and set initial value to 1
    If rstsource.EOF = True Then
        LInsert = "Insert into Codes (Last_Nbr_Assigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "(1)"
 
        db.Execute LInsert, dbFailOnError
 
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange"
 
    Else
        'Determine new Requisition Number
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange" & -Format(rstsource("Last_Nbr_Assigned") + 1)
 
        'Increment counter in Codes table by 1
        LUpdate = "Update Codes"
        LUpdate = LUpdate & " set Code_Desc = " & rstsource("Code_Desc")
        LUpdate = LUpdate & " set Last_Nbr_Assigned = " & rstsource("Last_Nbr_Assigned") + 1
        LUpdate = LUpdate & " set StartingRange = " & rstsource("StartingRange")
        LUpdate = LUpdate & " set YearValue = " & rstsource("YearValue")
 
        db.Execute LUpdate, dbFailOnError
 
    End If
 
    rstsource.Close
    Set rstsource = Nothing
    Set db = Nothing
 
    NewRequisitionNumber = LNewRequisitionNumber
 
    Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    NewRequisitionNumber = ""
    MsgBox "An error occurred while trying to determine the next Requisition Number to assign."
 
End Function

 

[pr2-eugin]

I am not sure, but have a few things I would like to clarify..
First,

    LSQL = "Select Code_Desc from Codes"
    LSQL = "Select Last_Nbr_Assigned from Codes"
    LSQL = "Select StartingRange from Codes"
    LSQL = "Select YearValue from Codes"
    Set rstsource = db.OpenRecordset(LSQL)

The code will only execute the last LSQL statement.. If you can see..

LSQL = "Select YearValue from Codes"

Thus rstsource will only have the Column values 1 to N from table Codes.

Next, In this line of code..

LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange" & -Format(rstsource("Last_Nbr_Assigned") + 1)

Where do you get the values for Code_Desc, StartingRange, Also you have the as String values.. Literally the LNewRequisitionNumber will be.. Code_Desc-YearValue-StartingRange..... (the last part will not be executed) as you have not given a format..

Next, How do you find the last value of the Last_Nbr_Assigned if you have not traversed to the last record??

Finally, the error occurring field is not even present in this piece of code.. So the error is not happening in this piece of code..

 

[steve21nj]

Thank you for the response. I am attempting to concatenate strings together without any luck. I attached the error when running, basically comes from assigning a new requisition number.

I cleaned up the first two parts that you suggested but I’m not sure how to “fix” the line LNewRequisitionNumber = …..

My Current Code: (still getting the error saying it cannot generate a zero value)

Function NewRequisitionNumber() As String
    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim rstsource      As DAO.Recordset
    Dim LNewRequisitionNumber   As String
    On Error GoTo Err_Execute
 
    Set db = CurrentDb()
    Set rstsource = db.OpenRecordset("Codes", dbOpenTable)
 
    'Retrieve last number assigned for Requisition Number
    LSQL = "Select Last_Nbr_Assigned from Codes"
    Set rstsource = db.OpenRecordset(LSQL)
 
    'If no records were found, create a new Requisition Number in the Codes table
    'and set initial value to 1
    If rstsource.EOF = True Then
        LInsert = "Insert into Codes (Last_Nbr_Assigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "(1)"
 
        db.Execute LInsert, dbFailOnError
 
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange"
 
    Else
        'Determine new Requisition Number
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange" & -Format(rstsource("Last_Nbr_Assigned") + 1)
 
        'Increment counter in Codes table by 1
        LUpdate = "Update Codes"
        LUpdate = LUpdate & " set Last_Nbr_Assigned = " & rstsource("Last_Nbr_Assigned") + 1
        db.Execute LUpdate, dbFailOnError
 
    End If
 
    rstsource.Close
    Set rstsource = Nothing
    Set db = Nothing
 
    NewRequisitionNumber = LNewRequisitionNumber
 
    Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    NewRequisitionNumber = ""
    MsgBox "An error occurred while trying to determine the next Requisition Number to assign."
 
End Function

My Original Code: (works fine, but I want to use this database at other locations and want to pull data from Codes table instead of having static data in VBA)

Function NewRequisitionNumber() As String
    Dim db             As Database
    Dim LSQL           As String
    Dim LUpdate        As String
    Dim LInsert        As String
    Dim rstsource      As DAO.Recordset
    Dim LNewRequisitionNumber   As String
 
    On Error GoTo Err_Execute
 
    Set db = CurrentDb()
    Set rstsource = db.OpenRecordset("Codes", dbOpenTable)
 
    'Retrieve last number assigned for Requisition Number
    LSQL = "Select Last_Nbr_Assigned from Codes"
    LSQL = LSQL & " where Code_Desc = 'LRS'"
 
    Set rstsource = db.OpenRecordset(LSQL)
 
    'If no records were found, create a new Requisition Number in the Codes table
    'and set initial value to 1
    If rstsource.EOF = True Then
 
        LInsert = "Insert into Codes (Code_Desc, Last_Nbr_Assigned)"
        LInsert = LInsert & " values "
        LInsert = LInsert & "('LRS', 1)"
 
        db.Execute LInsert, dbFailOnError
 
        'New Requisition Number is formatted as "LRS-12-60001", for example
        LNewRequisitionNumber = "LRS-13-60000"
 
    Else
        'Determine new Requisition Number
        'New Requisition Number is formatted as "LRS-12-60001", for example
        LNewRequisitionNumber = "LRS" & Format(rstsource("Last_Nbr_Assigned") + 1, "-13-60000")
 
        'Increment counter in Codes table by 1
        LUpdate = "Update Codes"
        LUpdate = LUpdate & " set Last_Nbr_Assigned = " & rstsource("Last_Nbr_Assigned") + 1
        LUpdate = LUpdate & " where Code_Desc = 'LRS'"
 
        db.Execute LUpdate, dbFailOnError
 
    End If
 
    rstsource.Close
    Set rstsource = Nothing
    Set db = Nothing
 
    NewRequisitionNumber = LNewRequisitionNumber
 
    Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    NewRequisitionNumber = ""
    MsgBox "An error occurred while trying to determine the next Requisition Number to assign."
 
End Function

 

[pr2-eugin]

Okay you might have missed some of my other comments, as they still have not been addressed in your updated query.. such as..

Next, In this line of code..

LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange" & -Format(rstsource("Last_Nbr_Assigned") + 1)

Where do you get the values for Code_Desc, StartingRange, Also you have the as String values.. Literally the LNewRequisitionNumber will be.. Code_Desc-YearValue-StartingRange..... (the last part will not be executed) as you have not given a format..

Next, How do you find the last value of the Last_Nbr_Assigned if you have not traversed to the last record??

Finally, the error occurring field is not even present in this piece of code.. So the error is not happening in this piece of code..

 

[pr2-eugin]

Sorry misread your post.. disregard my previous post.. I will look into that further and post back..

 

[steve21nj]

Thank you for the help. I know that line is wrong, but thats the general idea of where I want to go to make a form of LRS-13-60000, that adds +1 each time a new event occurs. My previous code did that, but had embedded values of LRS, 13, and 60000. I want to make those values dynamic so when this program is used at other locations, they can have their own specific values such as ABC-13-70000 or XYZ-13-40000!

Thanks again!

 

[pr2-eugin]

Hello Steve, Sorry it took long, I had to do 'some' work at my desk.. So could not post back soon, I just have a small doubt.. Does your table Codes have only one ROW at all times, based on your code it does.. If that is the case why do you complicate with RecordSets simple to use DLookUp's.. If that is the the design of your system then your code will be easier.. just try the following..

Option Compare Database
Function NewRequisitionNumber() As String

    Dim LNewRequisitionNumber   As String
    On Error GoTo Err_Execute
 
    'Retrieve last number assigned for Requisition Number
    codeDesc_Var = Nz(DLookUp("Code_Desc","Codes"),0)
    lastNum_Var = Nz(DLookUp("Last_Nbr_Assigned","Codes"),0)
    startRange_Var = Nz(DLookUp("StartingRange","Codes"),0)
    yearValue_Var = Nz(DLookUp("YearValue","Codes"),0)
    
    'If no records were found, create a new Requisition Number in the Codes table
    'and set initial value to 1
    If lastNum_Var = 0 Then
        LInsert = "INSERT INTO Codes (Last_Nbr_Assigned, Code_Desc, StartingRange, YearValue) VALUES (1, 'LRS', 60000, 12)"
        
        CurrentDb.Execute LInsert, dbFailOnError
 
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = 'LRS' & "-" & 12 & "-" & 60001
    Else
        'Determine new Requisition Number
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = codeDesc_Var & "-" & yearValue_Var & "-" & startRange_Var & "-" & lastNum_Var + 1
 
        'Increment counter in Codes table by 1
        LUpdate = "UPDATE Codes SET Last_Nbr_Assigned = " & lastNum_Var + 1
 
        CurrentDb.Execute LUpdate, dbFailOnError
    End If
 
    NewRequisitionNumber = LNewRequisitionNumber
 
    Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    NewRequisitionNumber = ""
    MsgBox "An error occurred while trying to determine the next Requisition Number to assign."
End Function

However I still do not understand so many things, You wish to 'dynamically' use ID, but you perform the same update except for changing the Last_Number_used.. If the above is not what you are looking for.. could you please upload a stripped down version of your DB so I can look into.. also explaining what you would like to do..

 

[steve21nj]

This is a great alternative! The only issue I have with the coding is the format on how it is displayed based on the attached images.

The format I am looking to achieve is ABC-12-60002
The current code produces ABC-12-60000-2

The dynamic part comes from other businesses using the database, they would default their ABC to whichever code they have such as XYZ or DEF. As the next physical year comes, they would change [12] to [13] for 2013 and [13] to [14] for 2014 and so on. My plan is to create an update query for a form where they would go to once a year to set the year values.

And yes, there would only be one row in the code table at any given time.

 

[pr2-eugin]

Okay.. try this...

[B]:
:[/B]
    Else
        'Determine new Requisition Number
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = codeDesc_Var & "-" & yearValue_Var & "-" & startRange_Var + lastNum_Var
 
        'Increment counter in Codes table by 1
        LUpdate = "UPDATE Codes SET Last_Nbr_Assigned = " & lastNum_Var + 1
 
        CurrentDb.Execute LUpdate, dbFailOnError
    End If
[B]:
:[/B]

 

[RainLover]

Why are you not using the DMax plus 1 method.

It only requires three or four lines of code. Much simplier than what you are doing.

The attached Sample Database would require changes to suit your needs, however it is much simplier than what you have and can be adopted to so many different situations.

Hope I understood what you are searching for.

 

[steve21nj]

Pr2-eugin,
I am attaching my watered down version of the database so you can see what I see. The proposed solution of the code kinda works. It removes the extra [-] but now the value is ABC-14-500003 (one to many zeros).

The second issue comes from the assigning the first Requisition Number. The starting value for Last_Nbr_assigned will be [-1], thus the very first number when starting a purchase order based on the code will be ABC-14-50000. The second ABC-14-50001 and so on. When I assign either -1 or 0 as the starting value, when you open my form [AddPurchaseOrder], the starting Requisition is -12-60001. So creating the first Requisition leaves off Code_Desc, which doesn’t allow to assign future numbers.

Third, the code still has static variables such as [LRS] and [12]. What I am looking to do is: whatever is in the code table, that’s what the Module1 reads.

For example.
Codes Table:
Code_Desc [ABC]
Last_Nbr_Assigned [-1]
YearValue [14]
StartingRange [50000]
Or at another location

Codes Table:
Code_Desc [EFG]
Last_Nbr_Assigned [-1]
YearValue [14]
StartingRange [90000]

With the first example the first Requisition Number “should” read ABC-14-50000, the second number should be ABC-14-50001.
But the current code sees that ABC-14-50000 is not in the vba so the first entry is -12-60001. The second entry is also -12-60001. So it begins adding rows to the code table with these values.

If you were to delete all the values out of tblRequisitionOrder, where the values of the form are stored, and change the value of Last_Nbr_Assigned in the codes table to [1], it produces ABC-14-500001 with no problem but it has an extra zero.

 

[pr2-eugin]

Hi Steve,

Pr2-eugin,
I am attaching my watered down version of the database so you can see what I see. The proposed solution of the code kinda works. It removes the extra [-] but now the value is ABC-14-500003 (one to many zeros).

This is because you have not declare the variables by type which means it took it for Variant type.. I did not declare it because I was not sure what type they were in your table.. So to avoid this, do..

Dim startRange_Var As Long, lastNum_Var As Long, yearValue_Var As Long

Also change the table Data type as Number.

The second issue comes from the assigning the first Requisition Number. The starting value for Last_Nbr_assigned will be [-1], thus the very first number when starting a purchase order based on the code will be ABC-14-50000. The second ABC-14-50001 and so on. When I assign either -1 or 0 as the starting value, when you open my form [AddPurchaseOrder], the starting Requisition is -12-60001. So creating the first Requisition leaves off Code_Desc, which doesn’t allow to assign future numbers.

Okay, How do you wish to collect these information? I mean if there is no values in the table how to you wish to set code as ABC start value as 0 and range as 6000??? If thats the case you should thing of collecting information via InputBoxes??

Third, the code still has static variables such as [LRS] and [12]. What I am looking to do is: whatever is in the code table, that’s what the Module1 reads.

This will fall in line when you make changes to the code as mentioned.. I have also edited the code.. see the updated file.. Please go through what changes I have made, and then apply it to your system.. If you have any questions post back.

 

[pr2-eugin]

I use RainLover's DMax method in my current DataBase in my system it works perfect in my scenario.. However your system is not something as simple as that.. But you should have a look at it as well.. People like RainLover, boblarson, Pat, vbaInet are far better experienced and have been writing better code that I have, so if they have an opinion I would give it a second though..

 

[steve21nj]

Pr2-eugin,

Thank you for replying back. I’ve had lots of help from the various individuals mentioned from their replies and reading several posts, very thankful for that. I’m not 100% sure about DMax as I am still learning all of what Access offers, but I am comfortable with the current code.

I think we are almost there. I was planning on creating an Admin Form for the user to set up the initial values of ABC, 13, 70000, -1 instead of the prompt that you created. Also, the new physical year started October 1, 2012 for 2013, so I cannot go by the systems date. That’s why I have been reverting back to the static codes table to pull the values off of there instead of the prompt. I’ll create the admin form on a later date after the vba is clear. I just didn't want static values in the vba because of other locations that have different values.

When the new physical year starts, I will have another form that will have an update query to change the values to ABC, 14, 70000, -1 or whatever values the admin user inputs to replace that row. That way when the user opens AddPurchaseOrder, the starting value will be ABC-14-70000.

So basically, I want to store the values in the codes table on the initial set up from a separate form. Then when the user starts a new purchase order, it will be ready to go.

 

[RainLover]

Location, Year and Number should all be stored in separate fields.
You would concatenate them for display when you need them.
You therefore would use three different sets of code to find each value.

 

[steve21nj]

I'm confused. Location, Year, and Number are separate fields in my table. They are represented by:

Location = Code_Desc,
Year = YearValue,
Number = StartingRange
Last Number Used = Last_Nbr_Assigned

In my table:
Code_Desc = LRS
YearValue = 13
StartingRange = 70000
Last_Nbr_Assigned = 2 (or whatever the latest value is)

I am attempting to concatenate them in a specific text box on the form. From what I understand, the code does that now, no? I see what the concept of DMax does, but I do not want the user to have to type a value each time, I want to make it automated.

For me, it is important to have the code table as the static data on the initial setup for when I archive the records at the end of year and later query the results for a cost comparison and other reporting.
Basically post #12 does what I want, but I want to pull the data from the codes table rather than the list box. And that codes pulls the system date, which is not the correct physical year for my company October 1, 2012 – September 30, 2013 = Physical year 2013.

 

[steve21nj]

Updated. Works the way I want. Thank you for the suggestions and I will continue reading about DMax.

Option Compare Database
Function NewRequisitionNumber() As String
 
    Dim startRange_Var As Long, lastNum_Var As Long, yearValue_Var As Long
    'On Error GoTo Err_Execute
 
    'Retrieve last number assigned for Requisition Number
    codeDesc_Var = Nz(DLookup("Code_Desc", "Codes"), 0)
    lastNum_Var = Nz(DLookup("Last_Nbr_Assigned", "Codes"), 0)
    startRange_Var = Nz(DLookup("StartingRange", "Codes"), 0)
    yearValue_Var = Nz(DLookup("YearValue", "Codes"), 0)
 
    Set db = CurrentDb()
    Set rstsource = db.OpenRecordset("Codes", dbOpenTable)
 
    'If no records were found, create a new Requisition Number in the Codes table
    'and set initial value to 1
    If lastNum_Var = -1 Then
        codeDesc_Var = Nz(DLookup("Code_Desc", "Codes"), 0)
        yearValue_Var = Nz(DLookup("YearValue", "Codes"), 0)
        startRange_Var = Nz(DLookup("StartingRange", "Codes"), 0)
        lastNum_Var = Nz(DLookup("Last_Nbr_Assigned", "Codes"), 0)
 
        Set db = CurrentDb()
        Set rstsource = db.OpenRecordset("Codes", dbOpenTable)
 
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = codeDesc_Var & "-" & yearValue_Var & "-" & startRange_Var + lastNum_Var
    Else
        'Determine new Requisition Number
        'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
        LNewRequisitionNumber = codeDesc_Var & "-" & yearValue_Var & "-" & startRange_Var + lastNum_Var
        'Debug.Print (CLng(startRange_Var) + CLng(lastNum_Var))
        'Increment counter in Codes table by 1
        LUpdate = "UPDATE Codes SET Last_Nbr_Assigned = " & lastNum_Var + 1
 
        CurrentDb.Execute LUpdate, dbFailOnError
    End If
 
    NewRequisitionNumber = LNewRequisitionNumber
 
    Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    NewRequisitionNumber = ""
    Call MsgBox("An error occurred while trying to determine the next Requisition Number to assign." & Chr(10) & Err.Description, vbCritical)
End Function

 

[pr2-eugin]

Well glad you got it sorted.. BUT you have just done something that might seem it will work, but in reality will not.. The IF part will never be executed.. As if the table with value is NULL then the first DLookUp will place it as 0.. Even if it does execute.. you are initialising the variables again to the same as it was before.. Which means in case if the IF works, then you will have the requestation ID with data as..

Location = 0, 
Year = 0, 
Number = 0 
Last Number Used = 0

If you are not sure what DLookUp does, please look it up on the internet.. it is a simplified SELECT statement.. That is the reason I asked you to Understand what I have implemented... You have a long way to go with this code..

 

[RainLover]

Paul

I was going to say more but Steve appeared to have made his mind up on the direction he wanted to go. I have done the same many times. I have had an idea in my head and nothing would change my mind.

I hope he comes back and asks questions about my Demo.

I did say when I posted it that it would require changes to suit his needs.

If we were to observe Normalisation Rules then there would be no need to store the Three letters, or the Year as these can be done at run time. So upon reflection I think his code could be reduced to one line of code. (I think)

See what happens.

 

[steve21nj]

You are both absolutely right about something that works now is not the best solution. I am building this to show that the concept works and to provide a temporary fix until a REAL solution can be developed.

What is the best way to implement the sample database provided by RainLover in my database? I want the number to auto generate based on the last number assigned rather than the user typing a value shown on the form [DMax Plus One] that gives the next number.

My situation: In two months, I will have 3 locations using this database. Each database will be its own separate entity so none of the tables, forms, reports will be shared, basically each location will have its own individual copy of the database. With that said, there will never be an instance where the tblNumbers will have multiple letters or strings of cat, dog, bird, etc. It will always be cat. The year value will always be 13. And the range will always be 60000 (or whatever the number is) +1.

I’d like to have DMAX work, I am just having a tough time wrapping my head around how to customize the sample database into an auto-generating machine.