serial number restarts each year

H

hodhod13

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
 
S

Stefan Hoffmann

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.
Click to expand...
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 <--
 
K

Klatuu

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")
 
H

hodhod13

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
 
H

hodhod13

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
 
S

Stefan Hoffmann

hi Hanadi,
I want to make the Doc_reference starting at 1 then / then CurrentYear e.g
0001/2006
0002/2006
Click to expand...
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
Click to expand...
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 <--
 
H

hodhod13

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
 
S

Stefan Hoffmann

hi Hanadi,
Let's do it this way,
Doc_Reference & Doc_date field are both PK (combined).
Click to expand...
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,
Click to expand...
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 <--
 
H

hodhod13

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.
 
S

Stefan Hoffmann

hi,
I have pasted this code in code builder it's not working:
Click to expand...
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
Click to expand...
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
Click to expand...

mfG
--> stefan <--
 
H

hodhod13

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.
 
H

hodhod13

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.
 
S

Stefan Hoffmann

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.
Click to expand...
Before pasting the body of the event method, create a new in the
property editor.


mfG
--> stefan <--
 
S

Stefan Hoffmann

hi,
Hi, can you tell me how can I do that?
Click to expand...
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 <--
 
Top