Creating Project numbers

[Guest]

Sorry if this post is long - I would like to give you as much information as
possible.

We use a project number system that works as follows:

ex. "Q26A01-01" breaks down to

[Q=type of project - we currently operate using 3 types][26=year, ie.
2006][A=the month, ie. A=January, B=February, etc.][1=first project of the
month - consecutive numbering that restarts at the beginning of the next
month] - [01=optional section of the main project - this doesn't happen very
often, but it does happen on the big projects]

This project number stays that same for this project once it is started. Is
there a way to set up Access so that is automatically creates this number
automatically? How would one go about doing this. I imagine it would have
to be a macro of some kind, but I don't know where to start.

I would like to be able to use this generated project number as the key to
many of my tables, as they are referenced to this unique project number.

Any help would be greatly appreciated.

 

[Tim Ferguson]

in

ex. "Q26A01-01" breaks down to

[Q=type of project - we currently operate using 3 types][26=year, ie.
2006][A=the month, ie. A=January, B=February, etc.][1=first project of
the month - consecutive numbering that restarts at the beginning of
the next month] - [01=optional section of the main project - this
doesn't happen very often, but it does happen on the big projects]

Eeek: that's not one field it's four or five:

ProjType Text(1),
StartDate DateTime,
SerialNumber Integer,
SectionNumber Integer

You might like to read about First Normal Form. It's easy enough to
create the original string for display on a form or report; but there
will be several update and insert problems keeping this lot intact. The
phrase "intelligent key" sounds like something good but actually it's
really A Bad Thing.

This project number stays that same for this project once it is
started. Is there a way to set up Access so that is automatically
creates this number automatically?

Yes.. If. Jet does not have triggers so you have to do the allocation via
programming code, usually behind a users' form. You have to prevent them
creating records using datasheets, queries, MS Query, Excel etc etc. As
long as you can make sure of that, then the rest is easy. Google for
"Access Custom Autonumbers" for lots of examples, but the heart of it is
a function like this:

'
' get the highest SerialNumber so far
'
whereSql = "Month(StartDate) = " & thisMonthNumber & " " & _
"Year(StartDate) = " & thisYearNumber

newNumber = DMax("SerialNumber", "Projects", whereSql)

'
' it's a null if there are no projects yet this month...
'
If isNull(newNumber) Then
newNumber = 1
Else
newNumber = newNumber + 1
End if


This is not safe for a multi-user setup, but there are examples around
that are.

Hope that helps


Tim F

 

[Guest]

Tim,

I thought it might be a multiple field system to make this work - I was just
hoping it wouldn't have to be, ya know? Turns out it IS as complicated as I
thought it was going to be. But, I'm not under any pressure to get this done
at the moment, so I can mess with it till it works.

I'll start looking at the code you gave me and do the extra searches you
have suggested. I will need this to be multi-user eventually; although the
project number assignment might be staying with the administrator alone if it
comes to that.

It's amazing how fast a simple Office Administrator position can turn into
an IT position.... It's a good thing I'm not afraid of my computer.

Thanks so much for your help!

in

ex. "Q26A01-01" breaks down to

[Q=type of project - we currently operate using 3 types][26=year, ie.
2006][A=the month, ie. A=January, B=February, etc.][1=first project of
the month - consecutive numbering that restarts at the beginning of
the next month] - [01=optional section of the main project - this
doesn't happen very often, but it does happen on the big projects]

Eeek: that's not one field it's four or five:

ProjType Text(1),
StartDate DateTime,
SerialNumber Integer,
SectionNumber Integer

You might like to read about First Normal Form. It's easy enough to
create the original string for display on a form or report; but there
will be several update and insert problems keeping this lot intact. The
phrase "intelligent key" sounds like something good but actually it's
really A Bad Thing.

This project number stays that same for this project once it is
started. Is there a way to set up Access so that is automatically
creates this number automatically?

Yes.. If. Jet does not have triggers so you have to do the allocation via
programming code, usually behind a users' form. You have to prevent them
creating records using datasheets, queries, MS Query, Excel etc etc. As
long as you can make sure of that, then the rest is easy. Google for
"Access Custom Autonumbers" for lots of examples, but the heart of it is
a function like this:

'
' get the highest SerialNumber so far
'
whereSql = "Month(StartDate) = " & thisMonthNumber & " " & _
"Year(StartDate) = " & thisYearNumber

newNumber = DMax("SerialNumber", "Projects", whereSql)

'
' it's a null if there are no projects yet this month...
'
If isNull(newNumber) Then
newNumber = 1
Else
newNumber = newNumber + 1
End if


This is not safe for a multi-user setup, but there are examples around
that are.

Hope that helps


Tim F

 

[Guest]

Hi again Tim,

I don't want to sound like a dolt, but where exactly should I put this type
of code? I'm not really strong on coding - kinda learning it as I go along.
I have set up the talbe that you suggested, but should this code go into a
query? I can't get to code for a table.

Jen

in

ex. "Q26A01-01" breaks down to

[Q=type of project - we currently operate using 3 types][26=year, ie.
2006][A=the month, ie. A=January, B=February, etc.][1=first project of
the month - consecutive numbering that restarts at the beginning of
the next month] - [01=optional section of the main project - this
doesn't happen very often, but it does happen on the big projects]

Eeek: that's not one field it's four or five:

ProjType Text(1),
StartDate DateTime,
SerialNumber Integer,
SectionNumber Integer

You might like to read about First Normal Form. It's easy enough to
create the original string for display on a form or report; but there
will be several update and insert problems keeping this lot intact. The
phrase "intelligent key" sounds like something good but actually it's
really A Bad Thing.

This project number stays that same for this project once it is
started. Is there a way to set up Access so that is automatically
creates this number automatically?

Yes.. If. Jet does not have triggers so you have to do the allocation via
programming code, usually behind a users' form. You have to prevent them
creating records using datasheets, queries, MS Query, Excel etc etc. As
long as you can make sure of that, then the rest is easy. Google for
"Access Custom Autonumbers" for lots of examples, but the heart of it is
a function like this:

'
' get the highest SerialNumber so far
'
whereSql = "Month(StartDate) = " & thisMonthNumber & " " & _
"Year(StartDate) = " & thisYearNumber

newNumber = DMax("SerialNumber", "Projects", whereSql)

'
' it's a null if there are no projects yet this month...
'
If isNull(newNumber) Then
newNumber = 1
Else
newNumber = newNumber + 1
End if


This is not safe for a multi-user setup, but there are examples around
that are.

Hope that helps


Tim F

 

[Tim Ferguson]

I thought it might be a multiple field system to make this work - I
was just hoping it wouldn't have to be, ya know? Turns out it IS as
complicated as I thought it was going to be. But, I'm not under any
pressure to get this done at the moment, so I can mess with it till it
works.

<g> Trust me, splitting the data into components makes your life easier not
harder! There is 40 years of maths research behind the relational model and
I have to believe that there is something there.

See other post...


Tim F

 

[Tim Ferguson]

I don't want to sound like a dolt, but where exactly should I put this
type of code? I'm not really strong on coding - kinda learning it as
I go along. I have set up the talbe that you suggested, but should
this code go into a query? I can't get to code for a table.

The best way to do this sort of stuff depends on your actual business
practices. Who is responsible for creating a new record; how important is
it to avoid duplicates; in what order are the various bits of information
available; etc.

One of my strategies is to place a lot of work in the Form_Load event --
this launches a Search dialog to find an existing record and then creates
a new one (allocating a new serial number along the way) if the user
can't find it alreay there.

Another is to intercept the Form_Current event; later versions of Access
also have a NewRecord event when the user moves off the last existing
record on the form.

Try sketching on a piece of paper what you think the user ought to see in
front of him or her when going through the process (and show the sketches
to your users/ admins). For ideas, look at other existing databases: get
your IT admin to talk you through adding a new network login, or the
financial bods to start a new budget-holder account, etc. Or join Paypal
or create a MS Passport account. Etc. Once you can describe the steps
_you_ need in small, logical bits then it's usually easy to program them.
If it isn't then there is always us!

Best of luck


Tim F