# Thread: Assigning a number with click and increment by one

1. Registered User
Join Date
Oct 2003
Posts
5

## 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. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Aug 2003
Location
Argentina
Posts
465
Gina:
Off the record is not easy to be a programmer...

7. Registered User
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
•