Thank you John. That worked pefectly. I think I wasn't doing anything to dirty the form in order to get it to create the autonumber. Wow, and I'm just gettng underway on this project...Steve
--- In MS_Access_Professionals@yahoogroups.com, John Viescas wrote:
>
> 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 (6) |
Tidak ada komentar:
Posting Komentar