Assigning autonumber

[zombiezoom]

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:

Private Sub cmdAutoBib_Click()
  Dim bibSQL As String
  Dim db As DAO.Database
  Dim querybib As DAO.QueryDef
  Set db = CurrentDb
  Set querybib = db.QueryDefs("qryAutoBib")
  Dim strSQL As String
  
  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 & ";"
  querybib.SQL = bibSQL
  DoCmd.OpenQuery "qryAutoBib"
  
  Dim x As Integer
  Dim y As Integer
  Dim dbase As Database
  Dim rs As Recordset
  x = Me.txtRaceMenuEventid.Value
  y = Me.txtRaceMenuRaceid.Value
  
  Dim var As Integer
  Dim qvar As Integer
  Set dbase = CurrentDb()
  Set rs = db.OpenRecordset("qryAutoBib")
  Dim qSQL
  var = 1
  
  
  Do Until rs.EOF
    qSQL = "Update [event-race-runner] SET [event-race-runner].bibnumber = " & var & " WHERE [event-race-runner].eventid= " & x & " and [event-race-runner].raceid = " & y & ";"
    DoCmd.RunSQL qSQL
    var = var + 1
    rs.MoveNext
  Loop
  
 
  Set querybib = Nothing
  Set db = Nothing
End Sub

[Delerna]

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=R1256E945 6

or some other scheme of combining them

[TheSmileyCoder]

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?

[NeoPa]

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.

[Delerna]

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

[zombiezoom]

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.

[NeoPa]

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.