Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Can I reset an auto number in an access table

Discussion in 'Information Technology' started by Gretchen, Jul 28, 2006.

  1. Gretchen

    Gretchen
    Expand Collapse
    Guest

    Is there a way to reset an auto number in access tables with out changing the
    number for existing records? I have an auto number starting at 260001; as of
    January 1, 2007 I want the numbering sequence to start at 270001 however I
    don't want the numbers for previous records to be modified. Is this possible
    using the field format of auto number?
     
  2. Loading...

    Similar Threads Forum Date
    World Israeli bedouins fight resettlement plan Breaking News Aug 3, 2013
    Leisure Arcade scores have been reset Business, Lifestyle & Leisure Oct 7, 2005
    Sikh News BJP holds protest against Resettlement Act GK NEWS SERVICE (Greater Kashmir) Breaking News Apr 30, 2005
    Opinion Is Autocomplete Evil? Some Women Take A Hard Look at Google ! Breaking News Nov 7, 2013
    Sikh News AutoZone sued for firing Everett Sikh Breaking News Sep 29, 2010

  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    I haven't tested this, but I think when I needed to do somthing similar, I
    wrote a query that forced a value into the table that was 1 less than the
    next value I required, ie somthing like:

    INSERT INTO myTable(myAutoNumberField) VALUES (270000);

    Cheers,

    ChrisM

    "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    news:8CFC6AFE-7117-461C-99B5-CFF8B903F174@microsoft.com...
    > Is there a way to reset an auto number in access tables with out changing
    > the
    > number for existing records? I have an auto number starting at 260001; as
    > of
    > January 1, 2007 I want the numbering sequence to start at 270001 however I
    > don't want the numbers for previous records to be modified. Is this
    > possible
    > using the field format of auto number?
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Autonumbers exist for one purpose only: to provide a (practically
    guaranteed) unique value that can be used as a primary key. No meaning
    should ever be assigned to the value of the autonumber. In fact, usually the
    user isn't even aware of what value has been assigned.

    Now, you appear to have what's often referred to as an "intelligent key",
    which isn't a compliment. It would appear that you're trying to store more
    than one piece of information in the same field, which is actually a
    violation of database normalization rules.

    Having said all that, yes it is possible to do what you want. Insert a row
    with a record that has a value of 270000, and the next row to be inserted
    will get an autonumber value of 270001. Once that's occurred, you can delete
    the bogus 270000 record you inserted. Hopefully, though, you'll rethink what
    you're doing and not take that route.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    news:8CFC6AFE-7117-461C-99B5-CFF8B903F174@microsoft.com...
    > Is there a way to reset an auto number in access tables with out changing

    the
    > number for existing records? I have an auto number starting at 260001; as

    of
    > January 1, 2007 I want the numbering sequence to start at 270001 however I
    > don't want the numbers for previous records to be modified. Is this

    possible
    > using the field format of auto number?
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Gretchen wrote:
    > Is there a way to reset an auto number in access tables with out
    > changing the number for existing records? I have an auto number
    > starting at 260001; as of January 1, 2007 I want the numbering
    > sequence to start at 270001 however I don't want the numbers for
    > previous records to be modified. Is this possible using the field
    > format of auto number?


    Let me recommend a careful reading of Mr. Steele's response.

    I will suggest it even stronger. Using an autonumber anywhere that a
    user will see it is an almost sure way to befuddle and confuse the users.

    Remember that you can combine fields when displaying them so you can
    store a two character or digit (note: they are not the same and you should
    be careful about which you chose) and a incremental field (see note above)
    and display them as if they were one.

    If you already have a data related to the record, then you can pull the
    year from there. It is almost always better to use existing data than to
    make a second field from that data since someone is bound to change one and
    the other will be missed.

    --
    Joseph Meehan

    Dia duit
     
  6. Gretchen

    Gretchen
    Expand Collapse
    Guest

    OK, I'll rethink this but I still need help!
    I'll simply join in my reports the prefix "26" or "27" to my job number.
    But I still have a problem in getting the number to increase incrementally
    for me. I want the number to move from 0001 to 0002 and so on. BUT, I have
    to have the ability to reset it back to 0001 on January 1st.

    "Joseph Meehan" wrote:

    > Gretchen wrote:
    > > Is there a way to reset an auto number in access tables with out
    > > changing the number for existing records? I have an auto number
    > > starting at 260001; as of January 1, 2007 I want the numbering
    > > sequence to start at 270001 however I don't want the numbers for
    > > previous records to be modified. Is this possible using the field
    > > format of auto number?

    >
    > Let me recommend a careful reading of Mr. Steele's response.
    >
    > I will suggest it even stronger. Using an autonumber anywhere that a
    > user will see it is an almost sure way to befuddle and confuse the users.
    >
    > Remember that you can combine fields when displaying them so you can
    > store a two character or digit (note: they are not the same and you should
    > be careful about which you chose) and a incremental field (see note above)
    > and display them as if they were one.
    >
    > If you already have a data related to the record, then you can pull the
    > year from there. It is almost always better to use existing data than to
    > make a second field from that data since someone is bound to change one and
    > the other will be missed.
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  7. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Gretchen wrote:
    > OK, I'll rethink this but I still need help!
    > I'll simply join in my reports the prefix "26" or "27" to my job
    > number. But I still have a problem in getting the number to increase
    > incrementally for me. I want the number to move from 0001 to 0002
    > and so on. BUT, I have to have the ability to reset it back to 0001
    > on January 1st.


    You can do that using the DMAX function

    Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

    Replace the 0 if you want a different starting number

    I'll have to think about how to modify that to allow you to start over
    at 1 again. Maybe basing it on a query that filters out prior years data?


    >
    > "Joseph Meehan" wrote:
    >
    >> Gretchen wrote:
    >>> Is there a way to reset an auto number in access tables with out
    >>> changing the number for existing records? I have an auto number
    >>> starting at 260001; as of January 1, 2007 I want the numbering
    >>> sequence to start at 270001 however I don't want the numbers for
    >>> previous records to be modified. Is this possible using the field
    >>> format of auto number?

    >>
    >> Let me recommend a careful reading of Mr. Steele's response.
    >>
    >> I will suggest it even stronger. Using an autonumber anywhere
    >> that a user will see it is an almost sure way to befuddle and
    >> confuse the users.
    >>
    >> Remember that you can combine fields when displaying them so you
    >> can store a two character or digit (note: they are not the same and
    >> you should be careful about which you chose) and a incremental field
    >> (see note above) and display them as if they were one.
    >>
    >> If you already have a data related to the record, then you can
    >> pull the year from there. It is almost always better to use
    >> existing data than to make a second field from that data since
    >> someone is bound to change one and the other will be missed.
    >>
    >> --
    >> Joseph Meehan
    >>
    >> Dia duit


    --
    Joseph Meehan

    Dia duit
     
  8. Gretchen

    Gretchen
    Expand Collapse
    Guest

    Thanks...maybe this is dumb question but I put that code where in my form? I
    was trying to use it on the "on focus" but it was not working.

    "Joseph Meehan" wrote:

    > Gretchen wrote:
    > > OK, I'll rethink this but I still need help!
    > > I'll simply join in my reports the prefix "26" or "27" to my job
    > > number. But I still have a problem in getting the number to increase
    > > incrementally for me. I want the number to move from 0001 to 0002
    > > and so on. BUT, I have to have the ability to reset it back to 0001
    > > on January 1st.

    >
    > You can do that using the DMAX function
    >
    > Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    >
    > Replace the 0 if you want a different starting number
    >
    > I'll have to think about how to modify that to allow you to start over
    > at 1 again. Maybe basing it on a query that filters out prior years data?
    >
    >
    > >
    > > "Joseph Meehan" wrote:
    > >
    > >> Gretchen wrote:
    > >>> Is there a way to reset an auto number in access tables with out
    > >>> changing the number for existing records? I have an auto number
    > >>> starting at 260001; as of January 1, 2007 I want the numbering
    > >>> sequence to start at 270001 however I don't want the numbers for
    > >>> previous records to be modified. Is this possible using the field
    > >>> format of auto number?
    > >>
    > >> Let me recommend a careful reading of Mr. Steele's response.
    > >>
    > >> I will suggest it even stronger. Using an autonumber anywhere
    > >> that a user will see it is an almost sure way to befuddle and
    > >> confuse the users.
    > >>
    > >> Remember that you can combine fields when displaying them so you
    > >> can store a two character or digit (note: they are not the same and
    > >> you should be careful about which you chose) and a incremental field
    > >> (see note above) and display them as if they were one.
    > >>
    > >> If you already have a data related to the record, then you can
    > >> pull the year from there. It is almost always better to use
    > >> existing data than to make a second field from that data since
    > >> someone is bound to change one and the other will be missed.
    > >>
    > >> --
    > >> Joseph Meehan
    > >>
    > >> Dia duit

    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  9. Gretchen

    Gretchen
    Expand Collapse
    Guest

    When I type in that code the first thing I notice is the code is
    automatically removing the ! after Me...furthermore, it is giving me a
    perameter error.

    "Gretchen" wrote:

    > Thanks...maybe this is dumb question but I put that code where in my form? I
    > was trying to use it on the "on focus" but it was not working.
    >
    > "Joseph Meehan" wrote:
    >
    > > Gretchen wrote:
    > > > OK, I'll rethink this but I still need help!
    > > > I'll simply join in my reports the prefix "26" or "27" to my job
    > > > number. But I still have a problem in getting the number to increase
    > > > incrementally for me. I want the number to move from 0001 to 0002
    > > > and so on. BUT, I have to have the ability to reset it back to 0001
    > > > on January 1st.

    > >
    > > You can do that using the DMAX function
    > >
    > > Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    > >
    > > Replace the 0 if you want a different starting number
    > >
    > > I'll have to think about how to modify that to allow you to start over
    > > at 1 again. Maybe basing it on a query that filters out prior years data?
    > >
    > >
    > > >
    > > > "Joseph Meehan" wrote:
    > > >
    > > >> Gretchen wrote:
    > > >>> Is there a way to reset an auto number in access tables with out
    > > >>> changing the number for existing records? I have an auto number
    > > >>> starting at 260001; as of January 1, 2007 I want the numbering
    > > >>> sequence to start at 270001 however I don't want the numbers for
    > > >>> previous records to be modified. Is this possible using the field
    > > >>> format of auto number?
    > > >>
    > > >> Let me recommend a careful reading of Mr. Steele's response.
    > > >>
    > > >> I will suggest it even stronger. Using an autonumber anywhere
    > > >> that a user will see it is an almost sure way to befuddle and
    > > >> confuse the users.
    > > >>
    > > >> Remember that you can combine fields when displaying them so you
    > > >> can store a two character or digit (note: they are not the same and
    > > >> you should be careful about which you chose) and a incremental field
    > > >> (see note above) and display them as if they were one.
    > > >>
    > > >> If you already have a data related to the record, then you can
    > > >> pull the year from there. It is almost always better to use
    > > >> existing data than to make a second field from that data since
    > > >> someone is bound to change one and the other will be missed.
    > > >>
    > > >> --
    > > >> Joseph Meehan
    > > >>
    > > >> Dia duit

    > >
    > > --
    > > Joseph Meehan
    > >
    > > Dia duit
    > >
    > >
    > >
     
  10. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Unfortunately, Joseph made a typo.

    It should be:

    Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

    where NameOfField is the name of the field that's supposed to hold the
    value.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    news:C5716839-225E-4440-AA5E-6314EACFB95C@microsoft.com...
    > When I type in that code the first thing I notice is the code is
    > automatically removing the ! after Me...furthermore, it is giving me a
    > perameter error.
    >
    > "Gretchen" wrote:
    >
    >> Thanks...maybe this is dumb question but I put that code where in my
    >> form? I
    >> was trying to use it on the "on focus" but it was not working.
    >>
    >> "Joseph Meehan" wrote:
    >>
    >> > Gretchen wrote:
    >> > > OK, I'll rethink this but I still need help!
    >> > > I'll simply join in my reports the prefix "26" or "27" to my job
    >> > > number. But I still have a problem in getting the number to increase
    >> > > incrementally for me. I want the number to move from 0001 to 0002
    >> > > and so on. BUT, I have to have the ability to reset it back to 0001
    >> > > on January 1st.
    >> >
    >> > You can do that using the DMAX function
    >> >
    >> > Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    >> >
    >> > Replace the 0 if you want a different starting number
    >> >
    >> > I'll have to think about how to modify that to allow you to start
    >> > over
    >> > at 1 again. Maybe basing it on a query that filters out prior years
    >> > data?
    >> >
    >> >
    >> > >
    >> > > "Joseph Meehan" wrote:
    >> > >
    >> > >> Gretchen wrote:
    >> > >>> Is there a way to reset an auto number in access tables with out
    >> > >>> changing the number for existing records? I have an auto number
    >> > >>> starting at 260001; as of January 1, 2007 I want the numbering
    >> > >>> sequence to start at 270001 however I don't want the numbers for
    >> > >>> previous records to be modified. Is this possible using the field
    >> > >>> format of auto number?
    >> > >>
    >> > >> Let me recommend a careful reading of Mr. Steele's response.
    >> > >>
    >> > >> I will suggest it even stronger. Using an autonumber anywhere
    >> > >> that a user will see it is an almost sure way to befuddle and
    >> > >> confuse the users.
    >> > >>
    >> > >> Remember that you can combine fields when displaying them so you
    >> > >> can store a two character or digit (note: they are not the same and
    >> > >> you should be careful about which you chose) and a incremental field
    >> > >> (see note above) and display them as if they were one.
    >> > >>
    >> > >> If you already have a data related to the record, then you can
    >> > >> pull the year from there. It is almost always better to use
    >> > >> existing data than to make a second field from that data since
    >> > >> someone is bound to change one and the other will be missed.
    >> > >>
    >> > >> --
    >> > >> Joseph Meehan
    >> > >>
    >> > >> Dia duit
    >> >
    >> > --
    >> > Joseph Meehan
    >> >
    >> > Dia duit
    >> >
    >> >
    >> >
     
  11. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Douglas J. Steele wrote:
    > Unfortunately, Joseph made a typo.


    Who me? Well I never! ...

    Well maybe ... :)

    Thanks for the correction, I am sure it would have taken me forever to
    notice it.


    >
    > It should be:
    >
    > Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    >
    > where NameOfField is the name of the field that's supposed to hold the
    > value.
    >



    --
    Joseph Meehan

    Dia duit
     
  12. Gretchen

    Gretchen
    Expand Collapse
    Guest

    When I use this formula (I'm putting the code at the "ON ENTER" of the field
    for Project Number), I get the following error
    Compile Error
    Expected =
    so still no luck in getting this to work.
    Thanks for your continued help.


    "Joseph Meehan" wrote:

    > Douglas J. Steele wrote:
    > > Unfortunately, Joseph made a typo.

    >
    > Who me? Well I never! ...
    >
    > Well maybe ... :)
    >
    > Thanks for the correction, I am sure it would have taken me forever to
    > notice it.
    >
    >
    > >
    > > It should be:
    > >
    > > Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    > >
    > > where NameOfField is the name of the field that's supposed to hold the
    > > value.
    > >

    >
    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  13. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    What's the exact code you've got (copy-and-paste it into your reply, as
    opposed to retyping it)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    news:DD3A57A0-4EEF-4E2B-890E-DEE8346A26C0@microsoft.com...
    > When I use this formula (I'm putting the code at the "ON ENTER" of the

    field
    > for Project Number), I get the following error
    > Compile Error
    > Expected =
    > so still no luck in getting this to work.
    > Thanks for your continued help.
    >
    >
    > "Joseph Meehan" wrote:
    >
    > > Douglas J. Steele wrote:
    > > > Unfortunately, Joseph made a typo.

    > >
    > > Who me? Well I never! ...
    > >
    > > Well maybe ... :)
    > >
    > > Thanks for the correction, I am sure it would have taken me forever

    to
    > > notice it.
    > >
    > >
    > > >
    > > > It should be:
    > > >
    > > > Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    > > >
    > > > where NameOfField is the name of the field that's supposed to hold the
    > > > value.
    > > >

    > >
    > >
    > > --
    > > Joseph Meehan
    > >
    > > Dia duit
    > >
    > >
    > >
     
  14. Gretchen

    Gretchen
    Expand Collapse
    Guest

    Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1

    Perhaps this is my problem...I have Project Number as the field in my main
    form that I'm trying to update and Project Number in a table called
    Project_Number Table where I'm trying to keep my next available number.

    Thanks again.

    "Douglas J Steele" wrote:

    > What's the exact code you've got (copy-and-paste it into your reply, as
    > opposed to retyping it)
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    > news:DD3A57A0-4EEF-4E2B-890E-DEE8346A26C0@microsoft.com...
    > > When I use this formula (I'm putting the code at the "ON ENTER" of the

    > field
    > > for Project Number), I get the following error
    > > Compile Error
    > > Expected =
    > > so still no luck in getting this to work.
    > > Thanks for your continued help.
    > >
    > >
    > > "Joseph Meehan" wrote:
    > >
    > > > Douglas J. Steele wrote:
    > > > > Unfortunately, Joseph made a typo.
    > > >
    > > > Who me? Well I never! ...
    > > >
    > > > Well maybe ... :)
    > > >
    > > > Thanks for the correction, I am sure it would have taken me forever

    > to
    > > > notice it.
    > > >
    > > >
    > > > >
    > > > > It should be:
    > > > >
    > > > > Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    > > > >
    > > > > where NameOfField is the name of the field that's supposed to hold the
    > > > > value.
    > > > >
    > > >
    > > >
    > > > --
    > > > Joseph Meehan
    > > >
    > > > Dia duit
    > > >
    > > >
    > > >

    >
    >
    >
     
  15. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Because of the spaces in your names, you need square brackets:

    Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
    Table]"),0)+1


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    news:2DBFB5C0-64DB-48CB-99FE-733EB34A873C@microsoft.com...
    > Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1
    >
    > Perhaps this is my problem...I have Project Number as the field in my main
    > form that I'm trying to update and Project Number in a table called
    > Project_Number Table where I'm trying to keep my next available number.
    >
    > Thanks again.
    >
    > "Douglas J Steele" wrote:
    >
    > > What's the exact code you've got (copy-and-paste it into your reply, as
    > > opposed to retyping it)
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    > > news:DD3A57A0-4EEF-4E2B-890E-DEE8346A26C0@microsoft.com...
    > > > When I use this formula (I'm putting the code at the "ON ENTER" of the

    > > field
    > > > for Project Number), I get the following error
    > > > Compile Error
    > > > Expected =
    > > > so still no luck in getting this to work.
    > > > Thanks for your continued help.
    > > >
    > > >
    > > > "Joseph Meehan" wrote:
    > > >
    > > > > Douglas J. Steele wrote:
    > > > > > Unfortunately, Joseph made a typo.
    > > > >
    > > > > Who me? Well I never! ...
    > > > >
    > > > > Well maybe ... :)
    > > > >
    > > > > Thanks for the correction, I am sure it would have taken me

    forever
    > > to
    > > > > notice it.
    > > > >
    > > > >
    > > > > >
    > > > > > It should be:
    > > > > >
    > > > > > Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    > > > > >
    > > > > > where NameOfField is the name of the field that's supposed to hold

    the
    > > > > > value.
    > > > > >
    > > > >
    > > > >
    > > > > --
    > > > > Joseph Meehan
    > > > >
    > > > > Dia duit
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
  16. Gretchen

    Gretchen
    Expand Collapse
    Guest

    OK, Now I have it partially working. I created a table called
    NewProjectNumber_Table and it has only one field called NewProject Number. I
    set the number to 260001. This is the only record in this table. When I run
    my form it will create a new project number, 260002 which appears to be OK.
    However when I create a second project it gives it the same number. I was
    expecting my table called NewProjectNumber to be updated to 260002 so when I
    went to create another project it would generate the number 260003. This is
    not what is happening.
    Am I missing a step or am I expecting too much from access?
    Thanks again for your help.

    "Douglas J Steele" wrote:

    > Because of the spaces in your names, you need square brackets:
    >
    > Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
    > Table]"),0)+1
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    > news:2DBFB5C0-64DB-48CB-99FE-733EB34A873C@microsoft.com...
    > > Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1
    > >
    > > Perhaps this is my problem...I have Project Number as the field in my main
    > > form that I'm trying to update and Project Number in a table called
    > > Project_Number Table where I'm trying to keep my next available number.
    > >
    > > Thanks again.
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > What's the exact code you've got (copy-and-paste it into your reply, as
    > > > opposed to retyping it)
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    > > > news:DD3A57A0-4EEF-4E2B-890E-DEE8346A26C0@microsoft.com...
    > > > > When I use this formula (I'm putting the code at the "ON ENTER" of the
    > > > field
    > > > > for Project Number), I get the following error
    > > > > Compile Error
    > > > > Expected =
    > > > > so still no luck in getting this to work.
    > > > > Thanks for your continued help.
    > > > >
    > > > >
    > > > > "Joseph Meehan" wrote:
    > > > >
    > > > > > Douglas J. Steele wrote:
    > > > > > > Unfortunately, Joseph made a typo.
    > > > > >
    > > > > > Who me? Well I never! ...
    > > > > >
    > > > > > Well maybe ... :)
    > > > > >
    > > > > > Thanks for the correction, I am sure it would have taken me

    > forever
    > > > to
    > > > > > notice it.
    > > > > >
    > > > > >
    > > > > > >
    > > > > > > It should be:
    > > > > > >
    > > > > > > Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    > > > > > >
    > > > > > > where NameOfField is the name of the field that's supposed to hold

    > the
    > > > > > > value.
    > > > > > >
    > > > > >
    > > > > >
    > > > > > --
    > > > > > Joseph Meehan
    > > > > >
    > > > > > Dia duit
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     
  17. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    You need to be querying for the maximum value in whatever table it is in
    which you're storing the records.

    If you're always querying NewProjectNumber_Table, but never updating the
    value there, it's always going to return the same value to you.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    news:7208632A-61DE-4E82-BD0C-34CCDDE72596@microsoft.com...
    > OK, Now I have it partially working. I created a table called
    > NewProjectNumber_Table and it has only one field called NewProject Number.

    I
    > set the number to 260001. This is the only record in this table. When I

    run
    > my form it will create a new project number, 260002 which appears to be

    OK.
    > However when I create a second project it gives it the same number. I was
    > expecting my table called NewProjectNumber to be updated to 260002 so when

    I
    > went to create another project it would generate the number 260003. This

    is
    > not what is happening.
    > Am I missing a step or am I expecting too much from access?
    > Thanks again for your help.
    >
    > "Douglas J Steele" wrote:
    >
    > > Because of the spaces in your names, you need square brackets:
    > >
    > > Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
    > > Table]"),0)+1
    > >
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    > > news:2DBFB5C0-64DB-48CB-99FE-733EB34A873C@microsoft.com...
    > > > Me!Project Number = Nz(DMax("Project Number","Project_Number

    Table"),0)+1
    > > >
    > > > Perhaps this is my problem...I have Project Number as the field in my

    main
    > > > form that I'm trying to update and Project Number in a table called
    > > > Project_Number Table where I'm trying to keep my next available

    number.
    > > >
    > > > Thanks again.
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > What's the exact code you've got (copy-and-paste it into your reply,

    as
    > > > > opposed to retyping it)
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "Gretchen" <Gretchen@discussions.microsoft.com> wrote in message
    > > > > news:DD3A57A0-4EEF-4E2B-890E-DEE8346A26C0@microsoft.com...
    > > > > > When I use this formula (I'm putting the code at the "ON ENTER" of

    the
    > > > > field
    > > > > > for Project Number), I get the following error
    > > > > > Compile Error
    > > > > > Expected =
    > > > > > so still no luck in getting this to work.
    > > > > > Thanks for your continued help.
    > > > > >
    > > > > >
    > > > > > "Joseph Meehan" wrote:
    > > > > >
    > > > > > > Douglas J. Steele wrote:
    > > > > > > > Unfortunately, Joseph made a typo.
    > > > > > >
    > > > > > > Who me? Well I never! ...
    > > > > > >
    > > > > > > Well maybe ... :)
    > > > > > >
    > > > > > > Thanks for the correction, I am sure it would have taken me

    > > forever
    > > > > to
    > > > > > > notice it.
    > > > > > >
    > > > > > >
    > > > > > > >
    > > > > > > > It should be:
    > > > > > > >
    > > > > > > > Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1
    > > > > > > >
    > > > > > > > where NameOfField is the name of the field that's supposed to

    hold
    > > the
    > > > > > > > value.
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > > Joseph Meehan
    > > > > > >
    > > > > > > Dia duit
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     

Share This Page