Assign number 1-11 to fields in a table

[TOTCOM11]

I have a database which I am using to keep track of a fantasy football draft. I have 11 teams in the league thus far and would like to randomly assign each of these teams a "draft order" number. Each team must have a unique draft number and the numbers must be in the range of 1-11. I have a "Teams" table set up with the following fields:

Team As Text
DraftOrder As Integer

Is there some way I can use a query or VBA code to assign a unique draft number to each of the teams?

TOTCOM11

 

[GoDawgs]

Tell me about the table. Are the field names the same as in the example code? Do all 11 teams have a DraftOrder of 0 to start? Is that field a number field? etc...

 

[TOTCOM11]

I looked over the code and compared it to my table, and everything is identical and should work. The DraftOrder for all of the teams is 0 and the field is a numeric field. What ends up happening is the loop will not stop..it just continues to oblivion. When I break out of the loop, it only adds a 1 to the first record in the table in the DraftOrder field.

 

[GoDawgs]

We're missing something here, hopefully it's not the 97/2002 difference. You sure you don't want to go with the pick them from the hat solution?

Do you know how to walk through the code? If you go to the code screen and click on grey area to the left of the "Set..." line you put a big red dot that is a stop in the code. Then when you run the code you can press F8 to go line by line through the code and follow exactly what is going on and what each variable is and all that good stuff. Give that a try and see if you can follow what's going on.

 

[TOTCOM11]

Yup....I did that. That's how I knew the loop kept running over and over again without stopping. I can't figure it out.

 

[GoDawgs]

Well what is RST![DraftOrder] each time it goes through? The only thing I can think of is that it is constantly checking the first record, seeing that it has already assigned "1" to that record and then doing the random again...but the random keeps returning the same record. So probably the Rnd function isn't doing what it's supposed to. Is the TheID variable changing every time? Is RST![DraftOrder] changing?

 

[TOTCOM11]

RST![DraftOrder] is not changing. In fact, the following code is getting skipped when the loop runs:

If RST![DraftOrder] = 0 Then
RST.Edit
RST![DraftOrder] = TheOrder
RST.Update
MoveOn = True
End If

I'm assuming this is because [DraftOrder] = 0 (duh). However, the code does run the first time so the problem is that the code is not looking at all of the records, only the first one.

 

[GoDawgs]

Yeah, actually the problem is that draftorder is NOT 0 every time except the first...meaning that, since all fields except the first one are 0 after the first time through, it's always looking at the first record, meaning that the TheID variable is always equal to 1, meaning that the TheID = Int((11 * Rnd) + 1) line is not returning a random number. If I'm right then you should see that TheID is equal to 1 (or whatever the ID of the first record is) every time. Try placing a "Randomize" before the "TheID =" line. If that's the problem then I'm gonna feel real dumb...

 

[TOTCOM11]

That didn't fix it

 

[GoDawgs]

Is TheID always 1?

 

[GoDawgs]

You know what, if you have an email address I'd be happy to send the database I created to try this...maybe then you can find what's going on...you'd have to convert it to 2002.

 

[TOTCOM11]

Send away!

Chris.Vesta@Packerland.com

 

[raskew]

Having tried out GoDawg's solution (and getting it to work (In Access 97) with just minor tweaking), suspect that either
(1) There's something about your table setup that we're not seeing
-or-
(2) There really is a problem with the 97 -> 2002 conversion.

For Michael Red:
Your latest rant was typically garbled, barely readable and missing the point by a mile (hmm, must have been the hijacked mystery posting--need to check with Homeland Security on that one). We all pray for your speedy recovery. For more information, click here:

http://easyweb.easynet.co.uk/simplepsych/psychosis.html

Looking for employment in all the places where sane folks go!
How's that working out, big guy??
Not real well!!