Automatically assign a number to a new record

[Guest]

I need to make my existing database assign a "File Number" which looks like
this 07-00345, (long intiger, the - is just a space in the number 0700345).
I need it to assign a number one greater than the last, or one greater than
the "max File Number" for the next record entered. I tried an append query
based on "max file number" but I can't get the result of the append query
onto the form, and consiquently into the table. This is for a police
reporting system, if anyone can personally assist me please e-mail me at
ajpszenny(AT)hotmail.com, I'm a cop, not a computer guy.

 

[Guest]

Also, I need to be able to EASILY start next years records by making the
number start with 08-0001. (080001)

 

[Joseph Meehan]

I need to make my existing database assign a "File Number" which
looks like this 07-00345, (long intiger, the - is just a space in the
number 0700345). I need it to assign a number one greater than the
last, or one greater than the "max File Number" for the next record
entered. I tried an append query based on "max file number" but I
can't get the result of the append query onto the form, and
consiquently into the table. This is for a police reporting system,
if anyone can personally assist me please e-mail me at
ajpszenny(AT)hotmail.com, I'm a cop, not a computer guy.

I would suggest using two different fields, one for the year and one for
the increment number. That will it would easier to find a specific year's
data, and easier to work with this in general. Displaying the data would be
a simple matter of displaying both fields next to each other. The only
place that might be difficult would be when viewing the table, but then
tables are not designed for view or editing data, they are for storing data
and definitions.

See:
http://www.lebans.com/rownumber.htm

and/or

Assign the number in the BeforeUpdate event.

If Me.NewRecord Then

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

End If

Replace the 0 if you want a different starting number

 

[Guest]

Drew:

I'd agree with Joseph that two columns (fields) would be better than one,
YearNum and FileNum say. To automatically assign the values to these columns
put code in the data entry form's BeforeInsert event procedure:

Me.YearNum = Year(Date() – 2000
Me.FileNum = Nz(DMax("FileNum","YourTable","YearNum = " Me.YearNum)),0) + 1

Don't include any controls on the form bound to the YearNum and FileNum
columns. Instead include an unbound text box for the file number with a
ControlSource of:

= Format([YearNum],"00") & "-" & Format([FileNum],"00000")

To show the file number in reports either use an unbound control with the
same ControlSource or include a Computed column in its underlying query e.g.

SELECT
Format([YearNum],"00") & "-" & Format([FileNum],"00000")
AS [File Number], <more columns>
FROM YourTable;

Remember that if table or column names include spaces toy must wrap them in
square brackets [like this].

If you do wish to stick with the single [File Number] column (of text data
type) then use the following code for the form's BeforeInsert event procedure:

Dim strYear As String
Dim strFileNum As String
Dim strCriteria As String

strYear = Format(Year(Date(),"00")

strCriteria = "Left([File Number],2) = """ & strYear & """"

strFileNum = Format(Nz(DMax("Right([File Number],5)","YourTable",
strCriteria),0) + 1,"00000")

Me.[File Number] = strYear & "-" & strFileNum

or if you don't want the hyphen stored as part of the value:

Me.[File Number] = strYear & strFileNum

This gets the year from the date on which the data is entered in the form.
If the date is derived from another column in the table, such as an Incident
Date, then don't include the year prefix in the file number as it can always
be derived from the Incident Date in a computed control on a form or report
using an expression, Format(Year([Incident Date],"00"). To store the year
prefix as part of the file number or in a separate column would introduce
redundancy, which leaves the database at risk of update anomalies as the
Incident date and the year prefix could be changed in a row so that they
don't match.

Ken Sheridan
Stafford, England

 

[Guest]

Joseph Meehan, I'll try your idea on the "Before Update" thing. I tried it
but it did nothing, I'm sure I did it wrong, so please explaine it like I
don't know what I'm doing.
1) I opened my form in Design view
2) I got to the properties of FILE#
3) I click the little triangle for "Before Update"
4) Then I click the ... button and get to the event procedure window.
5) what do I do now???
My Field Name is "File#"
My Table name is "Table1"
I want to start my new number with 070924
Thanks,

 

[Joseph Meehan]

Joseph Meehan, I'll try your idea on the "Before Update" thing. I
tried it but it did nothing, I'm sure I did it wrong, so please
explaine it like I don't know what I'm doing.

Cut and paste:

If Me.NewRecord Then

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

End If

Be sure the change the ("YOURFIELDNAME","TABLENAME")