Unique Name in Form (1 Viewer)

imousen

New member
Local time
Today, 19:56
Joined
Feb 26, 2022
Messages
5
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

I’m here to help
Staff member
Local time
Today, 08:26
Joined
Oct 29, 2018
Messages
19,164
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
36,868
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?
 

Attachments

  • CustomSequenceNumber20210303.zip
    93.7 KB · Views: 213

imousen

New member
Local time
Today, 19:56
Joined
Feb 26, 2022
Messages
5
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
36,868
Take a look at the example and see if you can modify it to suit your requirement.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:26
Joined
Oct 29, 2018
Messages
19,164
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
36,868
You can't use dcount() to do this, you use dmax().
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:26
Joined
Oct 29, 2018
Messages
19,164
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()
Code:
DCount("*", "ProjectTable", "ProjectName='" & [ProjectName] & "'") + 1
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
36,868
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...
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:26
Joined
Oct 29, 2018
Messages
19,164
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
36,868
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:26
Joined
Oct 29, 2018
Messages
19,164
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

New member
Local time
Today, 19:56
Joined
Feb 26, 2022
Messages
5
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

New member
Local time
Today, 19:56
Joined
Feb 26, 2022
Messages
5
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]
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 19, 2002
Messages
36,868
It is written to be used in VBA. Did you look at the example I posted?

You can use it in the Form's BeforeUpdate event to generate the number just before the record is saved. That works fine if you don't actually need to see the generated CustomCd for any reason before moving on.

If you have only ONE custom field, you could put the code into the BeforeUpdate event of that field but when you have two, you need to make the code a procedure and call it from each part of the custom field so in my example, from both the RrojectID and the SaleYear and the code needs to gracefully ignore the missing parts and not generate a number.

The problem with generating the sequence number too early in the process is that depending on how your business operates, you might have two people trying to create a new record at the same time and they can both generate sequence number 32. When the second person attempts to save the record, he will get a duplicate error and you have to deal with that and generate a new number. Using the BeforeUpdate event puts the generation as close to the save as possible and minimizes the potential problem. Therefore, it gives the user the opportunity to save again, and this time the code will generate a new number and probably not duplicate anything. If you have a large number of data entry users so that this type of conflict is probable, there are other methods to generate the number but they have the same problem as the autonumber in that if you don't save the record, the generated Sequence number will be discarded.
 

Users who are viewing this thread

Top Bottom