Unique Name in Form

[imousen]

Hi. My name is Mohsen from Iran. I started learning VBA Access recently. I interested to learn more and use your Knowledge.

I have a Question.
I have a Table in Access includes 4 fields. (Year - ProjectName - Phase - ContractNo)
I want make unique ID for each project in my form when I register a new project. How can I do this?
Like ;
[2019-01-OG]
[2019-02-BU]
[2020-01-HE]
[2020-02-OG]
[2020-03-IN]
[2021-01-OG]

As you see, I want ["Year" - "Number of Projects the same Year"- "Phase"].
Thank you for guiding me to get the answer.

 

[theDBguy]

Would the Phase change for each project during the lifetime of that specific project? For example, if the second project A was created with Phase 1, will that second project A ever change from Phase 1 to something else?

 

[imousen]

Maybe change or not. phase is not matter. I just want count Automatic number of projects in same year.

Would the Phase change for each project during the lifetime of that specific project? For example, if the second project A was created with Phase 1, will that second project A ever change from Phase 1 to something else?

 

[theDBguy]

Maybe change or not. phase is not matter. I just want count Automatic number of projects in same year.

One way to do that is to use the DCount() function. Maybe the sample @Pat Hartman posted does something similar to that. You should take a look and let us know if you need more help.

 

[theDBguy]

You can't use dcount() to do this, you use dmax().

That depends. I was thinking if all you want to use was the project name, then you should be able to count how many of them are when you're about to create a new one, so you can the assign the right value to the new record. Hope that makes sense...

PS. The syntax would be similar to DMax()

DCount("*", "ProjectTable", "ProjectName='" & [ProjectName] & "'") + 1

 

[theDBguy]

Only if you NEVER delete a record. Why would you risk having a problem when using dMax() works whether you delete or don't delete?

I see your point. But, don't we (at least I do) usually tell people not to delete records and simply mark them inactive or something similar?

 

[theDBguy]

We can tell them not to delete. We tell people to look both ways before crossing a street. If you can actually prevent a problem using method 1, why would you keep trying to justify method 2 which works ONLY if people ALWAYS follow our advice AND no accidents happen. I don't get it. Yes both methods work, usually. But one method works always. Why would you even consider the method that doesn't work always but has a known failure point? I don't think this is a case of take your pick, the methods are equal. Maybe if you didn't understand the weakness of the count() method you would randomly choose count rather then max. But if you know the weakness, why are we even having this discussion?

Fair enough. Don't need to continue further. Cheers!

 

[imousen]

Welcome aboard @imousen.

Take a look at the attached example. It shows two different ways to work with custom sequence numbers. One is similar to what you asked for.

However, do NOT use your customID as the primary key. Use an autonumber and add a unique index for the CustomID. This will eliminate problems should your business improve and you need to accommodate more than 99 projects in a single year.

Each of the pieces of the custom sequence number is stored separately. You can also store the concatenated value because this should never be changed for any given record and if you have coded to prevent change, it is safe to store the concatenation to make it easy for you to use for searching.

This description looks like a hierarchy. Are you sure you don't need a separate table for the phase data?

@Pat Hartman Thank you very much. It is useful for me. Thanx again

 

[imousen]

Only if you NEVER delete a record. Why would you risk having a problem when using dMax() works whether you delete or don't delete?

NewNum = Nz(DMax("SeqNum", "YourTable", "ProjectID =" & Me.ProjectID), 0) +1

If you want to have two variables, the expression is:

NewNum = Nz(DMax("SeqNum", "YourTable", "ProjectID = " & Me.ProjectID & " AND SaleYear = " & Me.SaleYear), 0), +1

The Nz() handles new values for project and SaleYear.

How and Where can I use this formula ? in Builder ? or VBA ?
[NewNum = Nz(DMax("SeqNum", "YourTable", "ProjectID = " & Me.ProjectID & " AND SaleYear = " & Me.SaleYear), 0), +1]