 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / November 2006serial number restarts each year
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") > 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 [quoted text clipped - 4 lines] > Regards, > Hanadi 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. [quoted text clipped - 3 lines] > 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, can you tell me how can I do that? > hi, > 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 <-- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.