How to ignore certain Customer Numbers and increase a certain kind incrementally?

[DanicaDear]

Hi Bytes!

I am working in a database and we've gone through several big changes this year in our Order Numbers (ShopOrder is the field name).
These are the types of ShopOrders we have had this year:
039801, 039802, 039803… (this was a sequence, now going away)
(no sequence on these next three, just showing the variety of ShopOrders we currently have in the database)
810964A3180
809511A3744-1
TDCC22A0100
TF0001, TF0002, TF0003… (will still use this sequence sometimes, special case, and can input manually)

My desire is to begin a new sequence that looks like this:
20160001, 20160002, 20160003 for this year, and of course next year we will start 20170001...

I have some code from another database increases the number (default value) by 1. Here is the code:

Format$(Year(Date()),"0000") & IIf(Val(Left$(DMax("[ShopOrder]","tblShopOrders"),4))=Val(Format$(Year(Date()),"0000")),Format$(Val(Right$(DMax("[ShopOrder]","tblShopOrders"),4)+1),"0000"),"0001")

However, in this database it doesn't seem to be working and I suspect it is because I have these other varieties of OrderNumbers already in the database. (My other databases where I successfully use this code doesn't have all this clutter!)

Can someone tell me how I can ignore all the junk order numbers and just look for the max YEAR ShopOrder and increase it by 1, like I desire? I'm not a code writer, FYI, but I do great things by picking up code off the internet and manipulating it to fit my needs. Pretty please be specific with the code if possible. :-)
If it helps, this is the only sequence in my database that has 8 digits, so could we use that somehow to our advantage, like ignore everything that isn't 8 digits? How would I code this?

Thanks in advance!!
Danica

[jforbes]

Hey Danica,

Here is a similar piece of Code to yours:

Public Function getKey(ByRef sTable As String, ByRef sField As String, ByRef iKeyLen As Integer, ByRef sPrefix As String) As String
    Dim sLastKey As String
    sLastKey = Nz(DMax(sField, sTable, sField & " LIKE '" & sPrefix & "*'"), sPrefix & "0")
    getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iKeyLen - Len(sPrefix))
End Function

If you called it like so:

?getKey("tblShopOrders", "ShopOrder", 6, "TF")

you sould get the next key that you are expecting.

The main difference between this code and yours is this code uses the Criteria in the DMAX() function to limit what records to look at to determine the last key that was used.

[DanicaDear]

I am no expert here, but does this code increase the sequence of TF0001, TF0002...?
I want to increase the 20160001, 20160002... (not the TF sequence..we will have to do that one manually if I use the default value to increase the 2016xxxx sequence). I will see if I can apply your idea to my situation. Strings freak me out but I can at least try this!
Thank you very much for your reply. :-)

[jforbes]

Haha, Yeah, I provided a solution for the key starting with TF. This function call works for a Key that starts with the Year:

?getKey("tblShopOrders", "ShopOrder", 8, year(now()))

Sorry about the mixup.

[DanicaDear]

Ok, I got this working, for the most part. I had never seen a Public Function before. :-)
I have the getKey set under the default value for ShopOrder in my properties, with the Public Function in an event procedure when you click the NEW button which I have located on the form.
When I open the form (fresh start) and go to new record, it increases the value perfectly. However, when I type in the field, it keeps that ShopOrder value in the field I am writing in, and ALSO gives the next field (it would then be the new record) the same value, I suppose because the write field isn't saved yet so the next new field adds one to the previously saved field.

Example:
20160005 is the last saved record.
Click new record. 20160006 defaults correctly. As soon as I begin to type in details, 20160006 also defaults into the next record too. Not only would this be the wrong number, but my primary key would duplicate. Any further ideas? Did I screw it up?

PS. Just in case someone follows this later.... I changed your "00000" to "0000" in your Public Function code. I think that's what you meant. :-)

[jforbes]

It sounds like you have everything setup correctly. Usually when using something like this as a Default for a field, this is how it works. When you save your new record, the key in the new record that is shown directly below the one you just saved to, should update and give display the correct next key value.

That point in time where they are both showing the same key value is correct. You as a human know that they should be different, but the defaulting and the Function are just returning the next key based on what it knows, which is the saved records.

Changing "00000" to "0000" shouldn't impact what happens much. The code that is being executed on that line is pretty dense and so it's a bit hard to follow:

getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iKeyLen - Len(sPrefix))
 
If the Previous key was "20160123" and the function
was run, sLastKey would be equal to "20160123" and 
the sPrefix would be "2016".  
Substituting these values, this would be the result:
 
getKey = "2016" & Right("00000" & Val((Right("20160123", Len("20160123") - Len("2016"))) + 1), 8 - Len("2016"))
 
Simplifying more would give 
getKey = "2016" & Right("00000" & Val("0123") + 1, 8 - 4)
...
getKey = "2016" & Right("00000" & 124, 4)
...
getKey = "2016" & Right("00000124", 4)
...
getKey = "2016" & "0124"
...
getKey = "20160124"