[RESOLVED] Assiging numbers?

[escudolm]

I have a query that finds certain data based upon what I want it to. It looks up info in tblEntrants, looking for category=2, gender=M, and racenumber is null. Basically I am trying to see if a RaceNumber exists. If it doesn't, assign the person(s) the next highest number between a start and stop number. Here is my code so far:

SELECT tblEntrants.Category, tblEntrants.Gender, tblEntrants.LastName, tblEntrants.RaceNumber
FROM tblEntrants
WHERE (((tblEntrants.Category)=2) AND ((tblEntrants.Gender)="M") AND ((tblEntrants.RaceNumber) Is Null))
ORDER BY tblEntrants.LastName;

This works perfect to get the names of the males in category 2 without a race number. I also have looked up the males in category 2 with race numbers. The male racenumbers are between 51-450. So far, 51-117 have been named and there are like 76 that havent. How can I assign racenumbers to the males that don't have one, based upon looking up the already existing numbers?

[escudolm]

Any ideas?

[DKenny]

Step 1/ Determine the next available racenumber (using a recordset that will return 1 row only).
Step 2/ Open a recordset containing all male entrants who don't have a race number.
Step 3/ Loop through the second rs and insert the value for the next racenumber, incrementing the race number on each pass.
Step 4/ Commit the changes to the 2nd recordset back into the database.

[escudolm]

Well i created a query that finds the next highest number for the male and female after looking at the query i made to find all the male/female's w/ racenumbers. Would I do the other steps in a query or should I code it in a Module? I understand what you are saying in your steps, just cant think of how to implement it. I appreciate the help so far.

[DKenny]

OK, the following should help.
A few things to note about this code.
1/ I don't know how you are determining the next racenumber, so you will have to change the first SQL statement.
2/ For the second recordset you only need to return the racenumber, as it is the only field that you are updating. If for some reason you need to add more fields to this RS (I can't think why) then you will need to update the field index where you are updating the racenumber.

Sub AddRaceNumbers()
Dim conMyConnection As ADODB.Connection
Dim rsLastNumber As ADODB.Recordset
Dim rsMissingNumber As ADODB.Recordset
Dim sSQL As String
Dim lRaceNumber As Long
 
    Set conMyConnection = New ADODB.Connection
    
    'I'm assuming this code is in Access
    'and taht you are connecting to the db that the code is in.
    Set conMyConnection = CurrentProject.Connection
    
    'You will need to re-write this line as I don't know
    'how you are deriving the last used number
    sSQL = "Select Max(RaceNUM) FROM RaceTable"
    
    'Create the RS
    Set rsLastNumber = New ADODB.Recordset
    
    'Open the RS with the last used number
    rsLastNumber.Open sSQL, conMyConnection, adOpenKeyset
    
    'Put the last used number into a variable
    lRaceNumber = CLng(rsLastNumber.Fields(0))
    
    'Get rid of this RS as we no longer need it
    rsLastNumber.Close
    Set rsLastNumber = Nothing
    
    'Only return the racenumber from the entrants table
    'as it is the only field to be updated
    sSQL = "SELECT RaceNumber FROM tblEntrants WHERE Category=2 AND Gender=""M"" AND RaceNumber Is Null"
    
    
    Set rsMissingNumber = New ADODB.Recordset
    
    'Open the RS that contains the blank racenumbers
    With rsMissingNumber
        .Open sSQL, conMyConnection, adOpenForwardOnly, adLockPessimistic
        
        'Loop through the RS
        Do While Not .EOF
            'Increment the race number
            lRaceNumber = lRaceNumber + 1
            'Update the field
            .Fields(0) = lRaceNumber
            'Go to the next record
            .MoveNext
        Loop
        'Finished with the RS so it can now be closed
        .Close
    End With
    
    'Clean up object variables
    Set rsMissingNumber = Nothing
    Set conMyConnection = Nothing
End Sub

[escudolm]

OMG, dude, it works perfect. THANK YOU so much!! Thanks for taking time to help me. I owe you one.

[DKenny]

Glad I could help.
Don't forget to mark this as resolved

[escudolm]

Oh, one more thing. Sorry. But is there any way I can make it so the males and females in category 2 dont go above a certain number? males are supposed to be between 51-450 and females 451-600. It is just how they want them numbered. Is this possible?

[DKenny]

What happens if there are more than 400 male runners?

[escudolm]

Then its their problem. I doubt though that there will be more than 400 male runners, running 10 K in under 40 mins. If their is, then they can number them some other way.

[DKenny]

OK, I've revised the code and made the following changes.

1/ Made Category and Gender parameters of the procedure. (Should have done this the first time). This allows you to call the same procedure for all combos of category and gender.

2/ Added a check in the update loop to see if you are outside the limits and stop the updates when this occurs.

3/ the test procedure is just to show you how to call the main proc.

Sub test()
    AddRaceNumbers 2, "M"
End Sub
 
Sub AddRaceNumbers(ByVal lCategory As Long, ByVal sGender As String)
Dim conMyConnection As ADODB.Connection
Dim rsLastNumber As ADODB.Recordset
Dim rsMissingNumber As ADODB.Recordset
Dim sSQL As String
Dim lRaceNumber As Long
 
    Set conMyConnection = New ADODB.Connection
    
    'I'm assuming this code is in Access
    'and taht you are connecting to the db that the code is in.
    Set conMyConnection = CurrentProject.Connection
    
    'You will need to re-write this line as I don't know
    'how you are deriving the last used number
    sSQL = "Select Max(RaceNUM) FROM RaceTable"
    
    'Create the RS
    Set rsLastNumber = New ADODB.Recordset
    
    'Open the RS with the last used number
    rsLastNumber.Open sSQL, conMyConnection, adOpenKeyset
    
    'Put the last used number into a variable
    lRaceNumber = CLng(rsLastNumber.Fields(0))
    
    'Get rid of this RS as we no longer need it
    rsLastNumber.Close
    Set rsLastNumber = Nothing
    
    'Only return the racenumber from the entrants table
    'as it is the only field to be updated
    sSQL = "SELECT RaceNumber FROM tblEntrants "
    sSQL = sSQL & "WHERE Category=" & CStr(lCategory)
    sSQL = sSQL & " AND Gender=""" & sGender & """"
    sSQL = sSQL & " AND RaceNumber Is Null"
    
    
    Set rsMissingNumber = New ADODB.Recordset
    
    'Only the RS that contains the blank racenumbers
    With rsMissingNumber
        .Open sSQL, conMyConnection, adOpenForwardOnly, adLockPessimistic
        
        'Loop through the RS
        Do While Not .EOF
            'Increment the race number
            lRaceNumber = lRaceNumber + 1
            'Check to see if we are
            'within the specified number range
            'If we excede the limits then exit the loop
            If (sGender = "M" And lRaceNumber > 450) _
            Or (sGender = "S" And lRaceNumber > 600) Then
                MsgBox ("You have reached the limits of our numbering system")
                Exit Do
            End If
            'Update the field
            .Fields(0) = lRaceNumber
            'Go to the next record
            .MoveNext
        Loop
        'Finished with the RS so it can now be closed
        .Close
    End With
    
    'Clean up object variables
    Set rsMissingNumber = Nothing
    Set conMyConnection = Nothing
End Sub

[escudolm]

In the first sSql "Select Max(RaceNumber) FROM qCat2MNum". How would I put my code in? I mean which query do I use. I have one for male "qCat2MNum" and one for female "qCat2FNum". Should I just combine them into one query?

[DKenny]

Use an IF statement based on the value in the sGender variable to determine what SQL statement to use to get the last race number

If sGender = "M" Then
    sSQL = "select max male number"
Else
    sSQL = "select max female number"
End If

[escudolm]

So put this code where the first sSql goes?

[DKenny]

Yes, you need to replace the single line that previously set the sSQL variable with the revised code.

[escudolm]

Alright, thats what I figured. Once again man, you did it. Thank you very much!