Steve-
Using a command button doesn't make sense. If a user goes to a new record,
enters data and then tries to save it, you should intercept it in the
BeforeUpdate event of the form to calculate the new key.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strYear As String, varMax As Variant, intSeq As Integer
' If on a new record
If Me.NewRecord Then
' Calculate the "next" value
strYear = Format(Date, "yy") & "-"
varMax = DMax("ActualFWCNumber", "M_AnimalIntake")
' If nothing found,
If IsNull(varMax) Then
' Set next sequence to 1
intSeq = 1
Else
' Add 1 to the last number
intSeq = CInt(Right(varMax, 3)) + 1
End If
' Set the new value of the key
Me.ActualFWCNumber = strYear & Format(intSeq, "000")
End If
End Sub
What the above code is doing when you're about to save a new record is look
up the previous high value for the current year. If there are no records,
the code generates a key of "yy-001" where "yy" is the current year. If
there is a previous record, it extracts the sequence part, adds 1, and uses
that.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of sspatriots
Sent: Saturday, February 09, 2013 10:00 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Sequential Numbering
Hi,
I kept looking around for an example close to what I needed and found one
very similar. I just uploaded it to the "Assistace Needed" folder. It's
description is "Autonumber With Year and Sequential No.".
Anyhow, I managed to tweak this a bit to get it to work for what I was
after, however, for some reason when I run the query or look at the table
after I add a few new numbers using the command button, I don't see the new
entries being recognized in the table or the query when I run it. I'm so
close now and hoping someone can explain to me why the table doesn't get
updated right away and the query doesn't recognize the newest entries. I
only see them after I close the database and re-open it.
Thanks,
Stee
--- In MS_Access_Professionals@yahoogroups.com, "sspatriots" wrote:
>
> Hi,
>
> I'm struggling trying to follow instructions I've found on numerous
websites and forums on how to set up a custom sequential numbering system. I
can't even manage to follow the simplest approach that I've found. I know it
is because I've only had a couple of on-line courses with Access and SQL and
that was 2 years ago...and here I am now trying to get into this for a
project at work.
>
> Long story short, I'm hoping to find a sample database on-line or some
really detailed instructions on doing this. If someone could just point me
in the right direction that would help tremendously.
>
> The format I need to create is YY-###. Where the Year is always the
current year and the 3-digit number at the end is sequential when the user
selects a button on the form to create one. Every, year I would need to have
the 3-digit number to reset to "001". Any help would be greatly appreciated
>
>
> Thanks,
>
> Steve
>
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar