serial number restarts each year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have just created a table for our documents reference, in which we want to
start a new serial number each year without creating a separate table each
year.
Kindly let me know how I can do that in which the starting value is 1 each
year as primary key and adding a year value to it to make it unique.
Treat me as in intermediate level when explaining how it is done.
Regards,
Hanadi
 
hi,
I have just created a table for our documents reference, in which we want to
start a new serial number each year without creating a separate table each
year.
Kindly let me know how I can do that in which the starting value is 1 each
year as primary key and adding a year value to it to make it unique.
Use a combined primary key with the fields YEAR and SERIAL. Use the
following insert:

INSERT INTO Table (YEAR, SERIAL, ...)
SELECT Year, Nz(DMax("SERIAL", "Table"), 0) + 1, ...
FROM Table

You need to create and execute this statement in VBA.

I would prefer this:
Table: ID (autonumber), YEAR, SERIAL
with ID as PK, and YEAR, SERIAL as unique index.


mfG
--> stefan <--
 
Assuming your serial number is one field and that the first serial number for
2007 will be 200700001, you can use a function like this to return the next
serial number:

strNextSerial =Format( Nz(DMax("[SERIAL_NBR]", "tblDocuments",
"Left([SERIAL_NBR], 4) = '" & Format(Date(), "yyyy")), 0) + 1, "000000000")
 
Thank you Stefan & Klatuu for your feedback,

the current table is blank not yet used name: LogMainTable
the designed form for this table is MainLogForm

In My table I had the following usefull field for this exercise:
Doc_Reference & Doc_Date

I want to make the Doc_reference starting at 1 then / then CurrentYear e.g
0001/2006
0002/2006

on the begining of the next year I want the Doc_Reference to restart from 1
then NewYear e.g.
0001/2007
0002/2007

this will make Doc_Reference as my PK as advise the suitable Data Type.
can you help me to do so in the VBA
 
Thank you Stefan & Klatuu for your feedback, lets refresh as follows:

the current table is blank not yet used name: LogMainTable
the designed form for this table is MainLogForm

In My table I had the following usefull field for this exercise:
Doc_Reference & Doc_Date

I want to make the Doc_reference starting at 1 then / then CurrentYear e.g
0001/2006
0002/2006

on the begining of the next year I want the Doc_Reference to restart from 1
then NewYear e.g.
0001/2007
0002/2007

this will make Doc_Reference as my PK as advise the suitable Data Type.
can you help me to do so in the VBA

Thanks
Hanadi
 
hi Hanadi,
I want to make the Doc_reference starting at 1 then / then CurrentYear e.g
0001/2006
0002/2006
Don't store two meaningful values in one field. It's against the 1NF.

http://en.wikipedia.org/wiki/First_normal_form
on the begining of the next year I want the Doc_Reference to restart from 1
then NewYear e.g.
0001/2007
0002/2007

this will make Doc_Reference as my PK as advise the suitable Data Type.
can you help me to do so in the VBA
This is not wise to do, because then it must be a character field. This
is not native sortable:

SELECT Doc_Reference
FROM Table
ORDER BY Doc_Reference

will give you with you example data:

"0001/2006"
"0001/2007"
"0002/2006"
"0002/2007"

So use two fields storing the year and the counter on their own:

SELECT Right$("0000" & Str(Counter), 4) & "/" & Str(Year)
FROM Table
ORDER BY Year, Counter

You also can use the fields as combined PK and use it as foreign key in
other tables.


mfG
--> stefan <--
 
Let's do it this way,
Doc_Reference & Doc_date field are both PK (combined).
therefore, Can you help me in getting this right all what I want to do is to
have a Doc_Reference field to starts with number 1 and increase +1 for next
records and this counter will reset to 1 if date is => 1 Jan.
the previous provided codes are in SQL and I wish to do so in the Code
builder,
Can you help me to do so.
Regards,
Hanadi
 
hi Hanadi,
Let's do it this way,
Doc_Reference & Doc_date field are both PK (combined).
Store only the year, not the date.
therefore, Can you help me in getting this right all what I want to do is to
have a Doc_Reference field to starts with number 1 and increase +1 for next
records and this counter will reset to 1 if date is => 1 Jan.
the previous provided codes are in SQL and I wish to do so in the Code
builder,
To get the correct serial:

Dim Serial As Long
Serial = Nz(DMax("Serial", "Table", "[Year]=" & Year(yourDate)), 1) + 1

You can use it in the Before_Update event of your form.

Private Sub Form_BeforeUpdate()

Dim Serial As Long

If Me.NewRecord Then
Serial = Nz(DMax("Serial", "Table", _
"[Year]=" & Year(yourDate)), 1) + 1
Me![Doc_Reference] = Serial
Me![Doc_date] = Year(yourDate) ' or Year(Now)
End If

End Sub


mfG
--> stefan <--
 
Dear Stefan
I have pasted this code in code builder it's not working:

Private Sub Form_BeforeUpdate()
Dim Serial As Long
If Me.NewRecord Then
Serial = Nz(DMax("Serial", "Table", _
"[Year]=" & Year(yourDate)), 1) + 1
Me![Doc_Reference] = Serial
Me![Doc_date] = Year(yourDate) ' or Year(Now)
End If
End Sub

just to make sure
my table name is LogMainTable
my form name is MainLogForm
The avialable fileds in both are:
Doc_Reference; Doc_Date; Doc_Type; Doc_To; Doc_Subject; Doc_signed_by
Doc_Reference & Doc_date field are both PK (combined).

Can you advise.
 
hi,
I have pasted this code in code builder it's not working:
Of course it's not working, but it would be a greate help, if you
provide the error messages.
Private Sub Form_BeforeUpdate()
Dim Serial As Long
If Me.NewRecord Then
Serial = Nz(DMax("Serial", "Table", _
"[Year]=" & Year(yourDate)), 1) + 1
Use here instead of

"Serial" -> "[Doc_Reference]"
"Table" -> "[LogMainTable]"
"[Year]" -> "[Doc_Date]"
Me![Doc_Reference] = Serial
Me![Doc_date] = Year(yourDate) ' or Year(Now)
End If
End Sub

mfG
--> stefan <--
 
after I pasted the following:
Private Sub Form_BeforeUpdate()
Dim Serial As Long
If Me.NewRecord Then
Serial = Nz(DMax("[Doc_Reference]", "[LogMainTable]", _
"[Doc_Date]=" & Year(Now)), 1) + 1
Me![Doc_Reference] = Serial
Me![Doc_Date] = Year(Now)
End If
End Sub

the following error message after trying entering new record
The Expression BeforeUpdate you entered as the event property setting
produced the following error: Procedure delaration does not match
description of event or procedure having the same name.
 
Correction:
when I pasted the following code:
Private Sub DocSerial()
Dim Doc_Reference As Long
If Me.NewRecord Then
Serial = Nz(DMax("[Doc_Reference]", "[LogMainTable]", _
"[Doc_Date]=" & Year(Now)), 1) + 1
Me![Doc_Reference] = Serial
Me![Doc_Date] = Year(Now)
End If
End Sub

therewas no error put i am not getting any number in the Doc_reference Field.
 
hi,
the following error message after trying entering new record
The Expression BeforeUpdate you entered as the event property setting
produced the following error: Procedure delaration does not match
description of event or procedure having the same name.
Before pasting the body of the event method, create a new in the
property editor.


mfG
--> stefan <--
 
hi,
Hi, can you tell me how can I do that?
Open your form in design view. Open the property editor. Go to the
events page. Press the ellipsis '...' button in the row Before Update.
If your asked to create a macro or event procedure, choose event procedure.


mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top