Unique numbering for records

[Guest]

I'm hoping someone can help me with some code.

Quick synop.: I have construction projects that will go into my database,
each with a unique number. The number is the year plus a counter for the
year, then will be refreshed every year. The numbers being used in other
office systems are like so: "107004" is the fourth job of the year. I
don't know why the "1" is in front of everything, it's just the way they've
been numbering them. For my purposes, until the end of the year, when I can
get everyone to change, I will leave that "1" off the front.

I know I'll need an event that creates this number. I'm new to all of this
(Access), so please bear with me. I'll need the number to be generated
automatically, refresh every year, and be connected with that construction
job forevermore. Can someone please help me make this happen?

I currently have the JOB table auto-numbering the jobs. (This is a bare,
brand new database, so there are no records). I have one simple form for
entry of new jobs (job name, place, type, start and end date, estimator
etc.). I don't have any queries written yet, nor do I have any other tables
built.

I don't want to do double work, so I'm stopping here first. Where shall I
put the code for the numbering system?

Thanks for your help!

 

[Guest]

Betty,

I would create separate fields for the JobYear, and the JobNumber, you can
keep the autonumber field, but I would call it JOB_ID, and would use that
throughout your database to relate back to this specific job.

If you do it this way, then what you need to do is automatically generate
the JobNumber each time you create a new Job. I normally do this in the
BeforeUpdate event of a form. What this means is that this number will not
be known until you actually save the new record you are creating. I usually
setup my data input forms such that you cannot leave a new form without
either clicking a Save or Cancel button. The cancel button undoes the
current record, but the Save button. The code behind the Save button is
simple:

Private Sub cmd_Save_Click()

me.dirty = false

end sub

This one command will force the forms BeforeUpdate event to fire, and I
would put code in that event that actually queries your database for the
maximum value in your JobNumber field for the current year (the year
corresponding to the). It would look something like the following. One
thing to note is that I test to determine whether this is a new record before
I make this change. If it is not a new record, then I skip this step. This
will build a criteria for the DMAX function that looks at the text control on
your form that houses your JobYear (txt_JobYear). The DMAX function will get
the largest value in the JobNumber column of your table for the year in your
criteria expression. By filling in this control just prior to updating the
record, you are saving that new job number, and can be sure that no one else
is going to get that number.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strCriteria as string
If me.NewRecord then 'you don't want to do this if you are editing a
record
strCriteria = "JobYear = " & me.txt_JobYear
me.txt_JobNumber = NZ(DMAX("JobNumber", "YourTable", strCriteria),
0) + 1
endif

End Sub

HTH
Dale