Steve-
See the frmDemo form in the file I uploaded. Go to a new record and type
something in "Data" to dirty the record. The code will generate the next
number when you save the record.
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: Sunday, February 10, 2013 12:02 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Sequential Numbering
John,
Thank you for the solution. I tried using the code you have below, however,
nothing happens when I save the form. I'm sure it's something I'm doing
wrong. Do you think you could re-attach the change you made to the files
area so I can see what I'm doing wrong. I have the code tied to the box that
shows the new values, but nothing shows up.
Thanks again,
Steve
--- In MS_Access_Professionals@yahoogroups.com, John Viescas wrote:
>
> 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
>
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar