Code for spacing between same numbers

[sfrope1]

I have a code that assigns records in the order the entry to the event comes in. My problem is that if the same person enters 3 times in a row they end up back to back in the event, I would like to have it make them no closer than a number I enter on my Main form. I thought about using random, but the order is generated in first to enter, last to compete.

 

[Rolliee]

do not understand what you are saying. Show some code, please.

Rollie E

 

[sfrope1]

Public Function GenerateRopingOrder(ByVal EventID As Long, ByVal MinSpacing As Long) As Boolean
On Error GoTo Err_GenerateRopingOrder

Dim UserResponse As Long
Dim conn As ADODB.Connection
Dim rstRegistration As ADODB.Recordset
Dim rstRopingOrder As New ADODB.Recordset
Dim SlotCount As Long
Dim MaxSlot As Long
Dim Spacing As Long
Dim i As Integer

Set conn = CurrentProject.Connection
Set rstRegistration = New ADODB.Recordset

UserResponse = MsgBox("Do you wish to reset the entire roping order?" & String(2, vbCrLf) & _
"Press 'Yes' to reset the entire roping order." & vbCrLf & _
"Press 'No' to create an order only for teams that have not yet been added to the roping order." & vbCrLf & _
"Press 'Cancel' to cancel this process.", vbQuestion + vbYesNoCancel, AppName)

If Not UserResponse = vbCancel Then
DoCmd.SetWarnings False
If UserResponse = vbYes Then
DoCmd.RunSQL "UPDATE tblRegistration SET RopingOrder = 0 WHERE EventID = " & EventID
End If
DoCmd.RunSQL "DELETE * FROM tblRopingOrder"
' DoCmd.RunSQL "DELETE * FROM tblRegistrationRandom"
' DoCmd.RunSQL "INSERT INTO tblRegistrationRandom (RegistrationID) SELECT RegistrationID FROM tblRegistration WHERE EventID = " & EventID

DoCmd.SetWarnings True

' rstRegistration.Open "SELECT tblRegistration.* FROM tblRegistration INNER JOIN tblRegistrationRandom ON tblRegistration.RegistrationID = tblRegistrationRandom.RegistrationID WHERE EventID = " & EventID & " AND RopingOrder = 0 ORDER BY Draw DESC, tblRegistrationRandom.Random", conn, adOpenStatic, adLockReadOnly
rstRegistration.Open "SELECT tblRegistration.* FROM tblRegistration WHERE EventID = " & EventID & " AND RopingOrder = 0 ORDER BY RegistrationID DESC", conn, adOpenStatic, adLockReadOnly
rstRopingOrder.Open "tblRopingOrder", conn, adOpenDynamic, adLockOptimistic, adCmdTable

MaxSlot = DMax("RopingOrder", "tblRegistration", "EventID = " & EventID)
SlotCount = DCount("RegistrationID", "tblRegistration", "EventID = " & EventID & " AND RopingOrder = 0&quot

If MinSpacing >= SlotCount * 0.05 Then
Spacing = SlotCount * 0.05 + 1
Else
Spacing = MinSpacing
End If

i = MaxSlot + 1

Do While Not rstRegistration.EOF
rstRopingOrder.AddNew
rstRopingOrder![RegistrationID] = rstRegistration![RegistrationID]
rstRopingOrder![Header] = rstRegistration![Header]
rstRopingOrder![Heeler] = rstRegistration![Heeler]
rstRopingOrder![Slot] = i
rstRopingOrder.Update
rstRegistration.MoveNext
i = i + 1
Loop

All this does, is take the first team and makes them the last team, when I need space between the same Header or the same heeler.

 

[Rolliee]

what would you like to happen if an entry comes 'too often?'

You might set up a recordsetclone and movelast and thus prevent a new entry from entering one he had just entered. This would not affect the operational recordsets.

Rollie E

 

[sfrope1]

They can enter up to as many times in each event as I specify on my form. I just don't want them to end up say number 1,2,3,4,5. I want to specify on my form how far apart to put them, like 1,5,10,15,20.

 

[Rolliee]

I usually find one way and then think about it to find a better one. How about using a list to make a table of 'sets' and run down the list setting a 'used' Y/N field when the person is place in a set. Repeat through the list till you get them all filled. Send me a zipped list and I will send back code to do it (1 way).

rollie@bwsys.net