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
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar