Next Id after prefix

[powerblade]

Hi,

I have product form/table which has productId, productCategorie, productname, etc.

When i want add new product, i select first ProductCategorie ie "10"
Then i made a button to get productID ie: 10001 (10 is categorieID+001)
next add productID if categorie is 10, 10002 en so on.

If categorie = 20, then productID = 20001 next 20002 etc...

This works fine. here is my code:
Module:

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String) As Variant

    nextIdString = Nz(DMax(nisFieldName, nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")
    ' nisPrefix & "0" gives you a default value if one is not found in the table

    nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1
    ' Get next numerical value by looking at the highest value number after the prefix and adding 1

    nextIdString = nisPrefix & Format(nextIdString, "000") ' create next string Id
    ' Create new ID string by concatenating the formated number to te prefix
End Function

button onclick event:

dim strPrefix as string
strPrefix = Format(Me.categorieID, "0")
If Len(Me.Productid & vbNullString) = 0 Then Me.Productid = nextIdString("Productid", "producten", strPrefix)

So aboe works fine. But i want if my product table is:

productID - Productname
10 001 a
10 002 b
10 003 c
20 001 d
20 002 e
20 003 f

When i delete ie 10 002 productname b and i want add productname X and productCategorie is 10 the code must look to the product ID's with prefix and if ie

productID - Productname
10 001 a
<= 002 is missing
10 003 c
20 001 d
20 002 e
20 003 f

the new productID and name should: 10 002 X.

So looking to the seqiential number after prefix should allways correct without missing the incremented numbers by 1. If missing, then next productID must that missing number. And if all correct, then use the last number +1.

thanks all in advance

 

[June7]

Is categorieID an autonumber field?

I had this issue as well. I had to account for all of these custom identifiers, no gaps allowed. So instead of deleting record, I offered an ABORT option for data entry - this removes data from fields so only ID field has value. Then for another input, code searches for record where a different field is Null - a field that should otherwise always have data. Usually this occurs in same day so records are still in chronological order when sorted by ID. However, also provided an option to VOID an established record which prevents reusing ID.

Otherwise, locating a gap and creating record to fill likely means looping through recordset and comparing ID to an incrementing variable and when they don't match, you have a gap.

 

[moke123]

Is this effort to construct a primary Key?
It seems to me it would be prudent to store the category identifier in a seperate field and the sequential numbers in their own field as a number datatype. If you need to display them a certain way you can concatenate and format.

To find the first missing number you would select all the products from the category, ordered by you number field. Then loop through it to find the first missing number.

You'd loop through a recordset but for demo purposes it would work like this

Function FindFirstMissing() As Long
    Dim strTest As Variant
    Dim missing As String
    Dim i As Integer

    strTest = Split("1,2,3,4,5,7,8,9,10,11,13", ",")

    For i = 0 To UBound(strTest)

        If i + 1 <> strTest(i) Then

            FindFirstMissing = i + 1

            Exit For

        End If

    Next i

    Debug.Print FindFirstMissing

End Function

 

[powerblade]

Is categorieID an autonumber field?

I had this issue as well. I had to account for all of these custom identifiers, no gaps allowed. So instead of deleting record, I offered an ABORT option for data entry - this removes data from fields so only ID field has value. Then for another input, code searches for record where a different field is Null - a field that should otherwise always have data. Usually this occurs in same day so records are still in chronological order when sorted by ID. However, also provided an option to VOID an established record which prevents reusing ID.

Otherwise, locating a gap and creating record to fill likely means looping through recordset and comparing ID to an incrementing variable and when they don't match, you have a gap.

No the categorie ID is from logical purpose choosen by the user so its number type and long integer. So tehere are 15 categories starting from 10, 20 30 etc..

 

[powerblade]

Is this effort to construct a primary Key?
It seems to me it would be prudent to store the category identifier in a seperate field and the sequential numbers in their own field as a number datatype. If you need to display them a certain way you can concatenate and format.

To find the first missing number you would select all the products from the category, ordered by you number field. Then loop through it to find the first missing number.

The created productID is datatype is number longinteger. The products table containes Productid, productname,productcategorieID etc.

10 001 A 10
10 002 B 10
and so on.

And the first missing ID after certain prefix(categorieID at the beginning of the productid) is what im looking for.

So you suggestion i would give it a try...

What i can do is, after 2 replies, before each delete i can store the product id's on a new table lets call it tbl_deleted_id and after when the user adds a new product, first look to tbl_deleted_id if the first ID exists matching the categorie, then it can copy number to the product table and delete that id from tbl_deleted_id.....

what do you think?

It would be better offcourse if i could manipulate the module i provide which could check right away to the first missing productid matching the categorie selected, so if there is a way to combine of my module with you test suggestion for missing number, that would be perfect offcourse..

I will look test several things in the meantime.

and all further suggestions are welcome.

 

[moke123]

You can find the first missing number in a sequence and if there isn't a missing number get the next number in the sequence in one procedure.

Here's an example.

 

[powerblade]

thnx for the sample database.

Its allmost what i want, but youre sample works only if the categorie ID is same so :

10-0001
10-0002
<=missing 0003 ie
10-0003

so above works fine. i added manually to your sample table 2 or more categories

10-0001
20-0001
10-0002
20-0002
10-0004
20-0004


so after click find missing, i got 0002. I understand why... So
i can make a query on selected categorie id, so there would be only ID's for that categorieID en youre code would work fine and after finding the missing, then vba could handle insert the id etc....

is it possible with vba the next scenario:

After add productID button,

1) make a query with all productsID with selected categorie CID from your sample form
2) then find missing id of created query from step 1 let see the missing, or if nothing missing, then the next after last id. ( as your sample)
3) Add the missing or next ID to table

So the question is, how do you create from vba temp query with condition and after your job is done, delete the temp query...

I read something about querydefs before but never used it.

How could we implement temp query with your sample to do the job as i need?

thnx moke your sample and advice put me on new ideas and actually i can creat a solution like my suggestion before with extra table or make a query in design view, but if it could be in VBA it would more nice and also
I want to broaden my programmer knowledge.

 

[moke123]

After typing a long answer, It dawned on me what I think your talking about.

My sample was very simple just to show you how to get the number. It wasn't written to be a final procedure.

The function NextNumberInCat was written just to cover the sample data which was all one category.

I think what your saying is you want a more generic function that you can designate which Category to use. Makes sense. I should have written it that way to begin with but it was just a quick sample.

We need to add an argument to the function to select only the one categoryID you choose.

heres the sample with a bunch of changes.

 

[powerblade]

Thnx moke. The end result is exactly what i talking about. Offcourse i understand that sample 1 was for quick test.

Your workaround works fine, i can implement in mine code...So in fact my question has answered.

I wonder if we could combine several buttons in 1 vba code like i mentioned above. Do you have experience with querydefs?

Because in your sample after selecting categorieid dropdown, you get all ids only matching that catID.

Could we do that in vba in the background as a temp query? and your code of find missing or next nr button code:

Dim NN As Long
NN = NextNumberInCat(Me.CID)

loops trough the temp query to find..?

then the code would be perfect automated...

 

[moke123]

I'm not quite sure why you would need a querydef in this instance.

You should be able to combine all those procedures into one.
They are split out with the seperate buttons to make it easier for you to see how each part works.

I dont know enough about your processes to give more specific help. I suggest you give it a go and experiment a little and post back with any questions.

 

[powerblade]

thnx moke for assistance and advice. Ok i will play around with the code and post my final solution .

thnx this form is the best!

 

[moke123]

Good luck with your project!