Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Post Unanswered: Assigning a number with click and increment by one

    I want to be able to assign a publication a "reprint" number. The reprint # will be sequential and incremented by one, but not necessarily on the next consecutive record if that makes sense....

    Ex.... Record 1 reprint # = 832
    Record 5 reprint # = 833

    two days later...

    Record 4 reprint # = 834


    I want to assign the number by clicking a button....

    I thought that<reprint = reprint +1"> would work but when I move to a new record, the reprint number starts over at 1 instead of picking up where it left off.

    Any suggestions?

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Assigning a number with click and increment by one

    Originally posted by gstarr
    I want to be able to assign a publication a "reprint" number. The reprint # will be sequential and incremented by one, but not necessarily on the next consecutive record if that makes sense....

    Ex.... Record 1 reprint # = 832
    Record 5 reprint # = 833

    two days later...

    Record 4 reprint # = 834


    I want to assign the number by clicking a button....

    I thought that<reprint = reprint +1"> would work but when I move to a new record, the reprint number starts over at 1 instead of picking up where it left off.

    Any suggestions?
    Two possible solutions:
    Use an auto number field which automatically increments a number.
    Or
    You can use a SQL statement to retrieve the last number on the list eg

    SELECT MAX(PrintRunNumber) + 1
    FROM MyTable

  3. #3
    Join Date
    Oct 2003
    Posts
    5

    Re: Assigning a number with click and increment by one

    Thank you!... but to ask another novice question... where exactly do I put the SQL statement... when I put in in the module I get nasty messages back..... secondly... does this mean I have to populate a table with all the numbers? or will it automatically increment the myTable by 1?

    Originally posted by justin_tighe
    Two possible solutions:
    Use an auto number field which automatically increments a number.
    Or
    You can use a SQL statement to retrieve the last number on the list eg

    SELECT MAX(PrintRunNumber) + 1
    FROM MyTable

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Assigning a number with click and increment by one

    Originally posted by gstarr
    Thank you!... but to ask another novice question... where exactly do I put the SQL statement... when I put in in the module I get nasty messages back..... secondly... does this mean I have to populate a table with all the numbers? or will it automatically increment the myTable by 1?
    You will need to add a field to your table to hold the reprint number. The field should be set to Number or Auto Number. If you set it to auto number then you don't need to do anything else. If you want to create the number on demand when you click a button you will have to use an UPDATE query along the lines of:

    UPDATE MyTable
    SET PrintRun = (SELECT MAX(PrintRun) + 1 FROM MyTable)
    FROM MyTable
    WHERE MyPK = PK_Of_Interest - this is the identifier of the record you want to update

  5. #5
    Join Date
    Oct 2003
    Posts
    5

    Re: Assigning a number with click and increment by one

    Sorry to be such a pain...I'm not a programmer by any means..... So I set up a query:

    UPDATE Reprints
    SET ReprintID = (SELECT MAX(ReprintID) +1 FROM Reprints
    FROM Reprints

    I get an error "syntax error (missing operator in query expression (SELECT MAX(ReprintID)+1 FROM Reprints) FROM Reprints)

    as for the next line of code you suggested I use....
    WHERE MyPK = PK_Of_Interest - this is the identifier of the record you want to update

    How do I specify the active record?

    Originally posted by justin_tighe
    You will need to add a field to your table to hold the reprint number. The field should be set to Number or Auto Number. If you set it to auto number then you don't need to do anything else. If you want to create the number on demand when you click a button you will have to use an UPDATE query along the lines of:

    UPDATE MyTable
    SET PrintRun = (SELECT MAX(PrintRun) + 1 FROM MyTable)
    FROM MyTable
    WHERE MyPK = PK_Of_Interest - this is the identifier of the record you want to update

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    Gina:
    Off the record is not easy to be a programmer...
    Saludos
    Norberto

  7. #7
    Join Date
    Oct 2003
    Posts
    5

    Clarification

    Norberto has told me that I'm not making myself clear in my request for help.... so let me try again.

    We have an internal number that we assign to some publications called a reprint number. This number is simply the next numerical number incremented by one in a series of numbers (I need to start the numbering at 832 when the db goes live.)

    On my main form I want to have a button that can be clicked when it is time to assign a reprint number to a publication.

    When I click the button, the next number in the "reprint number" series should be assigned to the publication(record) that I am currently working in and then be incremented by one in order to be assigned to the next time I need to assign a reprint number.

Posting Permissions

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