Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: Add Plus One Value To Feild Value In Form

    Hello,

    What i need to do:
    For the value in my primary key field to increase by 1 when a new record is started.

    Currently, when a new record is started, i have to manually enter the "Card Number" each time (Which is +1 than the previous record). I need vb code which can do this for me. (Please tell me where i place the code)

    The Problem:
    I can't use the Autonumber data type because i have some what 2000 records already in the table, and it wont allow me to change it which is why im asking for vb code.

    In Summary:
    Currently i have a form which uses a text field, linked to a primary key called "Card Number".

    When i start a new record, i have to enter a card number eg, 1001239
    (the previous records had the card numbers: 1001237, 1001238)

    Can anyone give a code or solution as to how i achieve this?

    Thank you in advance,
    Nicky Thorne.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should be able to make the field an autonumber assuming that there are no duplicate values and the column is not nullable...
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2009
    Posts
    8
    Eh, well, if you really don't want to use autonumbering (I can't see why this won't work, even with your explanation), you should be able to order your datatable by its card number, and then take the last card number and add 1 to it.

    Something like:
    Dim newValue as Integer = DataSet.CardsTable.Select("", "CardNumber DESC")(0)("CardNumber") + 1

  4. #4
    Join Date
    Jan 2009
    Posts
    5
    Thank you for your replies:

    I can't change the data type to Autonumber, because of the reasons in my first post.

    http://img7.imageshack.us/img7/5351/proofgc0.png

    @jjz-, Where do i place the code you provided me with?

    Table = Customer Details
    Field Name = Card Number

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you sure you don't have duplicate values in there?
    Code:
    SELECT your_field
         , Count(*)
    FROM   your_table
    GROUP
        BY your_field
    HAVING Count(*) > 1
    Have you got any null values?
    Code:
    SELECT *
    FROM   your_table
    WHERE  your_field IS NULL
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2009
    Posts
    8
    Llandaff hotels? What. This is Access, right?
    Microsoft Access 2007 lets me change field's datatype to Autonumber just fine, as long as there is only one autonumber field. I tried it just now on a fully populated table. I am sure 2003 lets you do that as well.

    Well, I have no idea what your code looks like... You would put that code right after you create a new datarow.
    You would create the datarow, and then assign the value in newValue to the "Card Number" field name.

  7. #7
    Join Date
    Jan 2009
    Posts
    5
    @georgev

    No duplicate values,
    No Null values - it was one of the first things i checked:
    http://img87.imageshack.us/img87/4472/proofym0.png



    @jjz-

    Lol, ya its access alright - I changed the title bar to "Llandaff Hotels" from the default "Microsoft Access".

    Thats kinda strange then? i guess

    And im confused now...
    I want the card number to be increased by one, when there is a new record:
    http://img15.imageshack.us/img15/676/helpcl7.png

    Can i place that code you supplied anywhere in the properties of the "Card number" field?
    Sorry for my in-orthodox use of access terms.

    Dang, i insisted when i created this thread that i wouldn't show my extreme lack of knowledge of access. I guess thats out of the window!
    Last edited by NickyThorne; 02-03-09 at 11:22.

  8. #8
    Join Date
    Feb 2009
    Posts
    8
    Well, I do not have extensive experience with databases or microsoft access; however, I can tell you that it let me change fields to AutoNumber from number.
    It only let me use one autonumber field, and it did not let me switch one field to another field without first saving and restarting; however, it did allow me to do it in the end, without trouble.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I can tell you that it let me change fields to AutoNumber from number.
    Not if there is data in the table it wont.

    There is absolutely zero point in having two Autonumber fields in one table.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Feb 2009
    Posts
    8
    If you read, I said that it could only let me use one autonumber field.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I obviously have a reading problem.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Feb 2009
    Posts
    8
    Not if there is data in the table it wont.
    You were certainly right about that.
    I just tried it, and it indeed did not work.
    I am so new to databases .

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You have to empty the table first.

    No biggie though, you can make a copy of it, then empty the table, then change to autonumber, then pull the data back into it from the copy.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •