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
- 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