Sorry, I misspoke. The code I gave must be modified to work for the
historical records. I was using the current date. You would need to use
the date associated with the record you are entering. Assume you have a
txt_Date text box on the form. You could put this code in the AfterUpdate
Event for the txt_Date text box.
If Me.NewRecord Then
' calculate FileNumber
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select Max(FileNumber) As fileNum From
FileTable Where Left(FileNumber,2) = Format(txt_Date(), 'yy')")
txt_FileNumber = Format(txt_Date, "yy") & Format(rs!fileNum Mod 1000 + 1,
"000")
End If
This would assign the number once the record's date is entered.
HTH,
Toby
----- Original Message -----
From: "Toby Bierly" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Monday, August 01, 2005 10:33 AM
Subject: Re: [AccessDevelopers] Autonumbering -- 2 fairly simple situations
You should not use Access' autonumbering for either of these situations.
Autonumbering is ONLY for assigning a unique identifier to a record that
doesn't mean anything. If you want a unique identifier that does mean
something, then you have to calculate it. You could definitely program this
to happen automatically.
If you are using a Form to enter data, you could calculate this in the
Current event of the form. The following code assumes there is a text box
on the form named txt_FileNumber bound to a FileNumber text field in a
FileTable table.
*******CODE*********
If Me.NewRecord Then
' calculate FileNumber
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select Max(FileNumber) As fileNum From
FileTable Where Left(FileNumber,2) = Format(Date(), 'yy')")
txt_FileNumber = Format(Date, "yy") & Format(rs!fileNum Mod 1000 + 1,
"000")
End If
*******END*********
If you have this setup beforehand, it will assign numbers to the historical
records as you enter them.
On the phone system numbers, why do you need to recycle the numbers? Do you
really need to do so, or could you set up an Autonumber field and let it
keep incrementing. If you do need to recycle numbers, there are at least
two methods to do so: 1) Calculate it by searching the table for the first
free number or 2) Store recycled numbers in a table when a record is deleted
and then grab the numbers from there to assign to new records.
HTH,
Toby
----- Original Message -----
From: "bassfolks" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Monday, August 01, 2005 9:18 AM
Subject: [AccessDevelopers] Autonumbering -- 2 fairly simple situations
> We are attempting to create a database to track new files opened in
> our office. Until our file clerk retired this month, this task had
> been handled manually with paper on a clipboard. Our new file clerk
> is anxious to get out of the dark ages. Our numbering system for
> files ties to our client number in our accounting system and is very
> simple. The number is a five digit number. The first two digits
> represent the year the file is opened and the remaining three are a
> file count (05001, 05002, etc.) We would like to go back to 1999.
> We will have 2, and possibly 3, people entering the historical
> information and then it will revert back to just one person
> maintaining the information as new files are opened. Is it possible
> to autonumber the file number field? If it is not possible to do it
> with the historical information, is it possible to do it going
> forward and how do we go about it so that when a new year starts the
> numbering changes?
>
> Also, our phone system is a separate set of numbers for purposes of
> tracking long distance calls by client. These numbers are recycled
> as files close and are not sequential. Is it possible to autofill a
> new record with the next recycled number and how do we do that? Do
> we tie that field to a table of recycled numbers and as each file
> closes, the number is entered into that table? If so, how do we get
> the numbers assigned to the new files as they are opened?
>
> Thanks for your help!
>
> Debbie
>
>
>
>
>
>
> Please zip all files prior to uploading to Files section.
> Yahoo! Groups Links
>
>
>
>
>
>
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
------------------------ Yahoo! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12h2pic2e/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1122925691/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org
">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life
- brought to you by One Economy</a>.</font>
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
