Creating my own unique auto-sequential Notice Number

[KerryL]

My database records company layoff or closing notices. The primary key is an autonumber that I don't visibly use.

However, the notices do need to be uniquely numbered, and my agency would like them numbered as follows:

Notices in 2006 will be numbered 20060001, 20060002, etc.
Notices in 2007 will be numbered 20070001, 20070002, etc.

What's the best way for me to add a field to the table that will accomplish this and will allow me to change it with each new year?

I checked the FAQs but didnt see anything similar to my request. Thanks for your help,
kl

 

[IGPCS]

well what you can do in the on after update of the first field that you fill out the data you can something like
this

A = format (date(),"yyyy")  
B = Dcount ()
'use the dcount function to get the amount of records you have with criteria is the A and then type this

text1 = A & B

this should work correctly hope you get the idea


I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

I'm sorry, I'm afraid I'm not following you.

I was thinking of using the "On Dirty" property of the form to populate the field but I can't get the yyyynnnn format to work (where yyyy=2006 & nnnn = record count).

What if I used a date field with the long format mmddyyyyhhmmss but formatted the object as an integer. IOW, turn a time stamp into a number. Would that work?

 

[IGPCS]

if your using vba for this just like i showed you earlier create a variabele its works great

Dim Year1

Year1 = format ([yourdatefield], "yyyy")


format functions should lood like this

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

I'm obviously omitting something because no matter what I try I get an "Argument No Optional" vba error:


Private Sub

Private Sub Form_Dirty(Cancel As Integer)

Dim Year
Year = Format(txtYYYY, "yyyy")

NNNN = DCount("[Name]")

txtNoticeNo = Year & NNNN

End Sub

 

[IGPCS]

first you cant call a variable year cause access knows it as a original function call it year 1 and is txtyyyy a date field? it should have a date in it

Dim Year1,NNNN

    Year1 = Format([your date field], "yyyy")

    NNNN = DCount([Name])
        
txtNoticeNo = Year1 & NNNN


I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

Ok, I finally got the syntax right. Now I'm getting a type mismatch for the txtNoticeNo field, which is a long integer field with a format of 00000000.


Private Sub Form_Dirty(Cancel As Integer)

Dim Year
Year = Format(txtYYYY, "yyyy")

NNNN = DCount("[Name]", "tblWARNData", Not Null)

txtNoticeNo = "Year" And "NNNN"

End Sub

 

[barny2006]

the best way that i can think of and i use, is to have a small table with one column in it: "avaibale_number_tbl"
when generating a notice, this number will be retrieved.
after the notice is printed, the number is incremented and the small table (available_number_tbl) is updated.

 

[IGPCS]

you should make it a text field or try adding a variable something like this

Dim Year1,NNNN
Dim B as Integer

    Year1 = Format([your date field], "yyyy")

    NNNN = DCount([Name])

    B = Year1 & NNNN
        
txtNoticeNo = B

This should work as well

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[barny2006]

igpcs,
what is dcount? have you tested this code? i get an error with the format.

 

[IGPCS]

Sorry i missed it you need to put you table or query name like thefollowing and needs to be in ""

    NNNN = DCount(Name,"YouTableName","And your Critirea")

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

Yes, I figured that out, thanks. Here's what I have:

//CODE
Private Sub Form_Dirty(Cancel As Integer)

Dim Year1
Dim NNNN
Dim B As Integer

Year1 = Format([txtYYYY], "yyyy")

NNNN = DCount("[Name]", "tblWARNData", Not Null)

B = Year1 & NNNN

txtNoticeNo = B

End Sub
//CODE

There are two problems:
1. The resulting Notice No = 00020061; it should be 20060001.
2. When the number is calculated, the form freezes while the notice number field blinks for 5-10 seconds until the number is finally inserted into the field. (I assume the code is going through the table.) I can only assume how long it will take when I actually put records into the table.

I may have to find a more efficient way to produce an 8-digit numbering sequence.

 

[IGPCS]

why did you put on dirty and not on current or one of the fields after update

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

I used the "On Dirty" property because I don't want the field to be populated until data goes into the form. I guess I could use it in the "After Update" of one of the fields, but the two problems I mentioned still exist.

 

[IGPCS]

so what you should do instead of creating it an intiger and placing it a number field just change your number field into a string (text) field it should resolve your problem

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

IGPCS,
I modified it as you suggested:


Private Sub Form_Dirty(Cancel As Integer)

Dim Year1
Dim NNNN
Dim B As String

Year1 = Format([EntryDate], "yyyy")

NNNN = DCount("[Name]", "tblWARNData", Not Null)

B = Year1 & NNNN

txtNoticeNo = B

End Sub


Unfortunately, both problems still exist. The next notice number was 20064 instead of 2006004, and the form freezes (and fields flash) for 3-4 seconds as the number is calcuated.

FWIW, txtNoticeNo is a text field with a format of 00000000.

 

[IGPCS]

oh i see so type this

if len(NNNN) = 1 then
B = Year1 & "000" & NNNN
else if len(NNNN) = 2 then
B = Year1 & "00" & NNNN
elseIf len(NNNN) = 3 then
B = Year1 & "0" & NNNN
else
B = Year1 & NNNN
end if

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[KerryL]

Shouldnt I use a Case stmt for this?

 

[IGPCS]

you can as well it dose the same thing

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[MichaelRed]

search the faq section with keywork "Autonumber" there are several articles which show a more-or-less complete development without the hazzards of using the DLookup.




MichaelRed