Add 1 to the max value in a field based on the type

[mooreb55020]

All,

First off thanks in advance. New to access and trying to build a list of products a company makes. However, the company has already built intelligence into the Item Codes. 01***** is a product line and 02***** is another. I have a list of all products and the product line they are in. What I'm looking to do is build a system that when a new product is added to a specific line it grabs the max value from that product line (this is a field in the table called ProdLineType) and adds 1 to it. The only other way I can think about doing this is to build a table for each product line and the query it all together. However, this makes building a bill of material for each one that much more difficult. Also, can I do that with text as they decided to have a leading zero on their product Item Code to distinguish between all 10 product lines they have? Again, thanks for your help!

[June7]

Generating custom unique identifier is a common topic. Search forum. Here is one discussion https://www.accessforums.net/showthread.php?t=23329

Assume this is a text field and you want to increment the ***** part. Requires VBA to retrieve the max value based on product line, parse the string, increment, recombine parts. Although in your case, the parsing and recombining might not be needed if there are no alpha characters in the identifier.

The first 2 characters identify product line? Only 99 product lines?

Trick is handling generating first ID in a series. Is there a combobox on form for selecting product line? Consider:

Format(Nz(DMax("ID", "Table", "Right(ID,2)=" & [Product]), [Product] & "00000") + 1, "0000000")

[GinaWhipp]

Another post on sequential numbering...
https://regina-whipp.com/blog/?p=704

[mooreb55020]

June7,

Yes there is a ComboBox and Field for selecting the Product Line. I have never used the code you wrote above. What you wrote seems fairly easy but I'm at a loss can you explain better on how this works? I'm assuming that if the product line is Colorant it should search for the last colorant product (lets say its 0100001) and make the next one 0100002. if the product is material (lets say the last one is 0200021) it will make the new one 0200022. could you explain the code a little more?

[June7]

Suggest you research each function used and learn how they work individually. Then the nesting is like algebraic operation, work your way from inside to outside.

Right()
DMax()
Nz()
Format()

These are all intrinsic to Access and VBA.

However, if you have a field for product line, there probably is no need to also save this value into the product ID. Just increment the 5-digit ProductID. Simpler expression.

Nz(DMax("ID", "table", "Product=" & [Product]),0) + 1


Then concatenate ProductLineID with ProductID whenever needed for display purpose.
=Format(ProductLineID], "00") & Format([ProductID], "00000")

[mooreb55020]

June7 you had it exactly right. i did a couple of instances where i used the SQL format and some with vba and it worked. Now I'm fighting another battle. I set up a form that has the property data entry set to "yes" so that it opens as a blank form. however, this causes it to add a new record from what i can see and then when i run my code it's adding 2 records for every entry. is there a way to either open the form and have it be blank without it creating a new record or is there a way to find the record the form just created and edit that record?

i know this is off topic just thought i'd ask quick

[June7]

DataEntry set to Yes does not cause a new record. Something else is. Most likely some code behind the form.

[GinaWhipp]

What's the code you are using to add a new record, that is most likely the issue.