Increment a field by 1, based on value of previous record.

[vmortimer]

I need to assign race numbers to entrants in a marathon. In Access 2003, I could enter "1" in the Race Number field and then simply press the arrow key; it was a nifty way to assign hundreds of numbers.

That feature is gone now. So how do I write a query that will accomplish this?

 

[Jgrant@soilsafe.com]

I addressed the same thing but put it in the code behind a button. I set up a field named last number. I needed to add a number of records at once so set up a loop. I added 1 to last number each iteration. Not sure if its the same thing as what you want to do but here's some code:

Dim Number1 As Integer
Dim Number2 As Integer
Dim IncNum As Integer

Number1 = 1
Number2 = Forms!formname!Txtfield

IncNum = IIF(IsNull(DLookup("MaxofLastNumber", "LastNumLookUpQRY")), 0, DLookup("MaxofLastNumber", "LastNumLookUpQRY")) ' this is important only for the first record.

For i = Number1 To Number2 'sets up the loop and tells it how many times to run based on data entry in the form text box

Set db = CurrentDb
Set rs = db.OpenRecordset("Tablewhereyouwanttochangeinfo")
rs.AddNew 'opens a new record
rs("LastNumber") = IncNum + i 'adds the number (ie if it is running the 5th loop it adds 5) to the incnum value and places it in the last number field.

I hope this helps.

Have a great day.

 

[vmortimer]

I'm afraid that I know nothing at all about programming, which suggests that I will need to learn quite a bit more before I can put your good advice to use. Thanks anyway - I just don't know enough to implement a solutoin.

 

[Jgrant@soilsafe.com]

Vmortimer,

This is a cheap and sneaky way to do it. In your table you have a field called ID. The program wants to put the ID field in to keep track of records. This number increases by 1 each time you enter a record. It's not good from what I understand to mess around with this number so here is my ultra simplified solution for you.

Make sure you have a field called "runnernumber"

Make a query on your table. Click the "update" button at the top of the screen.
Down on the bottom half of the screen drag and drop the runner number field into a column. In the "update to" Type in the following:
[thetableyouwhereyourrecordsare]![ID]

Be sure to include the brackets and the exclamation mark. Make sure you have the spelling of your table correct and it's best if you don't have any spaces in the table name.

Then click the red exclamation mark and your runner numbers will have dropped in with increment of 1

Hope this helps.

Jim

 

[vmortimer]

That solution would be perfect, except that I already have all the names entered and, worse, the numbering starts at different points, depending on the race: marathon at 1, half marathon at 3500. So, your excellent solution works for the marathon, but what do I do if the numbering has to start at something higher than 1?

 

[Jgrant@soilsafe.com]

Ok then this is what I suggest

It looks complicated but should only take you a couple of minutes to do.

In your registration table make sure you have a field for Runner Number
Then on your form put Two new text boxes. Leave them unbound. (it'll drop onto the screen that way). write down the names they are given.

Put a button on your form. When the pop up comes up click cancel.
In the properties box go to the "event Tab"
Put your cursor in the first row (should say "On Click") and click the little gray button with four dots that comes up. When the pop up comes up click Code Builder

Then paste this code. (it works I built a little data base and tried it)

Dim Number1 As Integer
Dim Number2 As Integer
Dim IncNum As Integer
Dim frmcurrentform As Form
Set frmcurrentform = Screen.ActiveForm




Number1 = Forms!Table1!Text6
Number2 = Forms!Table1!Text13

For i = Number1 To Number2

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1")
rs.AddNew
rs("Runnernumber") = IncNum + i
rs.Update
rs.Close

Next

DoCmd.RunCommand acCmdRecordsGoToLast


Change the textbox names to the ones your db gave them. and change your form names to the ones you've already given them It is important that there are no spaces in any of the names,

What we have done is add a button that will add as many or as few records as you want. The first text box is your starting number and the second text box is your ending number. Ie if you want to add runner numbers from 3500 to 3512 enter 3500 in the first text box and 3512 in the second text box and click the button. It will take you to the last record. And then you can add runner names etc. You will have that many new records where you can go in and add Data. If you already have a bunch of runners registered we can go back and update their records some other time. But this will work for any new entries. You can always find out what the last number is by either adding a combo box and sorting it by descending order or click the little button at the bottom to go to the last record. Be careful because you can duplicate numbers if you don't pay attention.

Hope this helps. Have a great evening.

J

 

[Jgrant@soilsafe.com]

You can make a a new table, put the runner numbers in it through the routine above and then run an update query linking the two tables by ID I'll think about it a bit and get back to you.

 

[vmortimer]

I finally figured it out, beginning with your suggestion about the ID number. I exported the names to a new table, then updated the RaceNo field with the ID number for the marathon runners.

Runners in the half marathon have numbers that start at 3500, so I just used an update query: [ID]+3499 (I made a separate table for the half marathon).

If I knew anything at all about programming, I am certain that the solutions offered by you and others would have worked. Luckily, I only have to do this once a year!

Many, many thanks for your kind helps!

 

[Jgrant@soilsafe.com]

Good luck with the race, hope all goes well. Glad I could help.