custom autonumbering

[s_samira_21]

I have a form that can save the tasks that should be done
In this form I made a combobox to select the name of the person who should do the task and an other one to select the project that this task is relevant to it.
and I have a textbox to enter the ID of the task
but I want access write the ID automatically such this:
first 4 numbers is the Project ID and next 2 numbers is the Person ID ans next 4 numbers is numbers that starts from 0001.
PPPPppxxxx

what should I do??

 

[spikepl]

Search for

custom autonumbering

using the Search facility at the top of the page

 

[s_samira_21]

????
any idea???

 

[VilaRestal]

You would create a sub that would be called from both the combobox's afterupdate events.

The first part would check that both are not null. If not then it would concatenate the Project ID and Person ID:

Private Sub SetTaskID()
    If Not (IsNull(ProjectID.Value) Or IsNull(PersonID.Value)) Then
        Dim sTaskID As String
        sTaskID = LeadingZeros(ProjectID.Value, 4) & LeadingZeros(PersonID.Value, 2)
        'More code to follow
    End If
End Sub
 
Private Function LeadingZeros(ByVal iValue As Integer, ByVal iDigits as Integer) As String
    Dim sReturn As String
    sReturn = iValue
    Do While Len(sReturn) < iDigits
        sReturn = "0" & sReturn
    Loop
    LeadingZeros = sReturn
End Function

The last 4 numbers could be complicated.
Should it search for tasks for the same Project-Person and find the highest number used? Or will it be a running total for all the tasks (in which case you'd create an autonumber field and base it on that I think)?

 

[Galaxiom]

Most of us would suggest you don't store the TaskID but recreate it from the fields PersonID, ProjectID and and another field incremented using DMax.

However, as Spike's post suggested, this subject has been covered ad nauseum at this site.

Type this into Google:
"custom autonumber" site:www.access-programmers.co.uk

 

[speakers_86]

Please don't make this your primary key! It's fine to include this when required, but there is no reason to base your db on it.

 

[s_samira_21]

It doesn't work

 

[VilaRestal]

Firstly, I agree with speakers_86 - don't make it your primary key.
Secondly, I can see problems with it: What happens when you get 100 people or more in the database? Some sort of separator between the numbers would be wise in my opinion: PPPP/pp/xxxx
And still we haven't decided how to get xxxx

You say it doesn't work? In what way? What does it do?

 

[s_samira_21]

any thing happen

 

[Galaxiom]

any thing happen

Appently not. You have obviously done absolutely nothing to apply yourself to this problem.

Without trying to be rude, I would suggest you try working in some other field. You have replied to our suggestions with the total sum of eight words in three posts, not one of them reporting what actually happened despite being specifically asked.

You apparently want solutions spoon fed. Programming is intellectually demanding and you clearly don't have anything approaching the tenacity for it.

 

[s_samira_21]

Dear GalaxiomAtHome
Im sorry for my short messages
but I have 2 Problems
1) my english is not that good so saying my problem in english in not easy for me and I have to spend a lot of time for just saying me problem.
2) I am so BZ and I m trying to answer as short as can. not because I am hasteful but because I want to save my time to learning.
working with access is not my job in my office anything I do is in my extra times in office.
How ever I think you are right and I m sorry if I behave in bad way...

and I will try more

 

[Galaxiom]

While I can understand your difficulty with the language we do need more information to help guide you. Don't worry about perfect English. We won't judge you for that but we do need to know what you have tried and how it is going wrong.

 

[s_samira_21]

thank you very much
the fact is that
I dont know where is the set(taskID) event
so I copied the code in form load bud any thing happened

The first thing that I need to know is where should I use this code

 

[Galaxiom]

My first comment pointed out that you should not store the TaskID as a complete field but regenerate it as needed from three fields every time it is required.

The ProjectID and PersonID fields already hold parts of the information that is required and repeating it in the TaskID is undesirable (This is a database concept called "Normalization" which forbids the repetition of information held elsewhere in the record.)

The third part is the SequenceNumber field which I will deal with later.

Concatenate (join together) the values from ProjectID, PersonID and SequenceNumber. Concatenation is done with the ampersand (&).

So don't store the TaskID in the table but only create it as a textbox on the forms and reports with a ControlSource in the textbox:

=Format(ProjectID,"0000") & Format(PersonID,"00") & Format (SequenceNumber,"0000")

(VilaRestal's LeadingZero Function is unnecessary because the Format function that is native to VBA already does that exact task and more.)

For the SequenceNumber we need to know if it should be incremented for each new Task or only for each new combination of ProjectID and TaskID.

By this I mean do you want this kind of pattern:

0001010001, 0001010002, 00010503

or this one

0001010001, 0001010002, 00010501

Once you explain that we can do the next step to generate the SequenceNumber and the way its generation is triggered.

 

[s_samira_21]

Dear GalaxiomAtHome
thank you very much for your kindly and patiently help.
I need the task ID to be incremented for each new combination of ProjectID and TaskID.
0001010001, 0001010002, 00010501

 

[s_samira_21]

One question!
Should I write the code in task ID text box Control source?
If yes, It says "the expression you entered contains invalid syntax"

 

[Galaxiom]

This is the line of code that finds the next SequenceNumber for the current record of the form. It is used on new records. It finds the previous larest number and adds one.

Me.SequenceNumber = DMax("SequenceNumber", "tablename", "ProjectID=" &  Me.ProjectID & " AND PersonID=" & Me.PersonID) + 1

 

[Galaxiom]

Should I write the code in task ID text box Control source?
If yes, It says "the expression you entered contains invalid syntax"

Yes. Though I don't know why it is invalid syntax unless it is the extra space between the last Format and the (

 

[s_samira_21]

ok
thank you
this way I Should change my access file structure
but no problem

should I write this code in my task list in new task button on click event?

one thing else I want this kind of codes:
00001010001
00001010002
00001010003
00001020001
00001020002
00002010001
I mean according to the organization or the person that is selected the numbers start from 1!
I wish I could say what I want!!!

 

[s_samira_21]

OOOOOOOOOOOOOk
I dont Know what was the problem but today it works as well as I want
Thank you

but one problem is that I want user see the project ID in Project list split form so I need to save the project ID!!
Is there another way except saving to show the Project ID?