By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,729 Members | 1,559 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,729 IT Pros & Developers. It's quick & easy.

Assigning autonumber

P: 10
I am creating a runner table. I need help in assigning a bib number. I could have used runner id as a bib number However, a runner id is assigned to all runners in my database. Hence, it is not unique to a particular race. What I would like to do is input bib number unique to a particular race. I may have 10,000 runners in my database but only 200 runner running a particular race on certain date. Bib Number is a field that is part of race & runner associative table.

One of the ways I tried to assign bib number was able to generate auto-number in SQL query. However, I can not assign or update the auto-number values into bib number. Is there a SQL command I can use in VBA to do that?

Another way was to try to do some kind of record loop. However, this only assigns last value in the loop to all the runners. This is the VBA command I used:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdAutoBib_Click()
  2.   Dim bibSQL As String
  3.   Dim db As DAO.Database
  4.   Dim querybib As DAO.QueryDef
  5.   Set db = CurrentDb
  6.   Set querybib = db.QueryDefs("qryAutoBib")
  7.   Dim strSQL As String
  8.  
  9.   bibSQL = "SELECT [event-race-runner].*, (select count(*) FROM [event-race-runner] AS temp WHERE eventid = " & Me.txtRaceMenuEventid & " AND raceid = " & Me.txtRaceMenuRaceid & " AND temp.runnerid < [event-race-runner].runnerid )+1 as AutoBibNumber FROM [event-race-runner] WHERE eventid = " & Me.txtRaceMenuEventid & " AND raceid = " & Me.txtRaceMenuRaceid & ";"
  10.   querybib.SQL = bibSQL
  11.   DoCmd.OpenQuery "qryAutoBib"
  12.  
  13.   Dim x As Integer
  14.   Dim y As Integer
  15.   Dim dbase As Database
  16.   Dim rs As Recordset
  17.   x = Me.txtRaceMenuEventid.Value
  18.   y = Me.txtRaceMenuRaceid.Value
  19.  
  20.   Dim var As Integer
  21.   Dim qvar As Integer
  22.   Set dbase = CurrentDb()
  23.   Set rs = db.OpenRecordset("qryAutoBib")
  24.   Dim qSQL
  25.   var = 1
  26.  
  27.  
  28.   Do Until rs.EOF
  29.     qSQL = "Update [event-race-runner] SET [event-race-runner].bibnumber = " & var & " WHERE [event-race-runner].eventid= " & x & " and [event-race-runner].raceid = " & y & ";"
  30.     DoCmd.RunSQL qSQL
  31.     var = var + 1
  32.     rs.MoveNext
  33.   Loop
  34.  
  35.  
  36.   Set querybib = Nothing
  37.   Set db = Nothing
  38. End Sub
  39.  
Apr 11 '10 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
Runners have a unique ID and races have a unique ID
Why not combine those two to make your BIB number

eg
RunnerID=1256
RaceID=9456
BibID=12569456

or perhaps
R for runnere and E for Race (Event)
BibID=R1256E9456

or some other scheme of combining them
Apr 12 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
Why not create a Primary Key consisting of the 2 Foreign key fields?

And Delarna, I dont see how your first method ensures a unique BibID, since the RunnerID and RaceID could combine to give the same BibID in some cases.

What do you need the BibID for?
Apr 12 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
As the OP has specified a number of 10,000 runners I expect a 4-digit runner number with a 4-digit Race number would actually resolve to a unique BibID. Clearly all 4 digits of both numbers would have to be used in all cases.
Apr 12 '10 #4

Delerna
Expert 100+
P: 1,134
TheSmileyOne
If the runnerId is unique and the race ID is unique then the only way to generate the identical number is by using the same runner and the same race ID's....unless individual runnerID's and race ID's are different length numbers which is why I suggested the second (R for runner and E for race).
Which in hindsight I probably should have specified in my post
Also my use of 4 digits was a random choice

It was just a suggestion

Anyway I like your suggestion better
Apr 12 '10 #5

P: 10
Thank you all for your help. The reason that I don't want to combine runnerid and raceid is because bib number are suppose be a tag number that runner wear on their shirt to identify their runner number. It cannot be a runner ID in my database because a race may only have 200 people while my database has information for more runners. I was thinking about generating a random number for a particular race and thus limiting bib number from say 1 to 250 if I only had 250 runners participating in that race.

Like I showed in my first post, I tried to use two methods but was unsuccessful. Is there a VBA code that I can use to update my bib id field value for each number with a number generated in a query table (this new field simply generates number but is not bound to any particular database table)?

If the above is not possible, can anybody figure out what is wrong in my code. If a particular race has 250 runners, the the code I use will update all runners' bib number with 250 instead of going from 1 to 250.
Apr 13 '10 #6

NeoPa
Expert Mod 15k+
P: 31,769
Assuming runners are assigned bib numbers as they are registered for a particular race, you would need to maintain the last Bib Number so far used for each Race. This would be stored in the Race table. As runners are registered, the Race/Runner associative table would be updated with a new record which would contain the previous Bib value for that race then the Race table would be updated with the new bib number.

May I suggest in future questions you share some name details in your question thread. It makes it unnecessarily complicated to try to help when all we have is a general description of your setup.
Apr 13 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.