الاخوة الفاضل اتمنى تقديم المساعدة في هذا العمل
هذا Loop يستخرج لي كل شخصين ير غب في مكان الاخر
ارغب بتعديله الى Loop يستخرج لي كل ثلاثة او أكثر(كلما زاد العدد يكون افضل)يرغبون بالتناقل ولكن تختلف رغباتهم(لكل منهم 10 رغبات)
اي A يرغب بمكان B
و B يرغب بمكان C
و C يرغب بمكان A
وهكذا
وهذا Loop:
Private Sub Btn3_Click()
'' This is the whole list. It Will take hours to finish ! :)
Btn1.Enabled = False
Btn2.Enabled = False
Dim DbSn As Database
Dim WishesRs As Recordset
Dim ResultRs As Recordset
Dim VarSchoolNum As Double, VarWishes As Double, VarGroupNum As Double
Dim sqlstr As String
Dim mama As Integer
start = Time
Set DbSn = OpenDatabase(CurrentProject.FullName)
Set WishesRs = DbSn.OpenRecordset("Wishes")
With WishesRs
.MoveFirst
VarGroupNum = 1
Do While Not .EOF 'VarGroupNum < 3 '
VarSchoolNum = !school_no '55555
VarWishes = !wishes '22222
sqlstr = "SELECT count(*) as mama FROM wishes WHERE school_no=" _
& VarWishes & " And wishes=" & VarSchoolNum & ";"
Set ResultRs = DbSn.OpenRecordset(sqlstr, dbOpenDynaset)
If ResultRs!mama > 0 Then
sqlstr = "INSERT INTO ResultsAll (groupNo, idnum, school_no, wishNum, wishes)" _
& " SELECT " & VarGroupNum & ", idnum, school_no, wishNum, wishes FROM wishes" _
& " WHERE school_no=" & VarSchoolNum & " And wishes=" & VarWishes & ";"
DbSn.Execute (sqlstr)
sqlstr = "INSERT INTO ResultsAll (groupNo, idnum, school_no, wishNum, wishes)" _
& " SELECT " & VarGroupNum & ", idnum, school_no, wishNum, wishes FROM wishes" _
& " WHERE school_no=" & VarWishes & " AND wishes=" & VarSchoolNum & ";"
DbSn.Execute (sqlstr)
VarGroupNum = VarGroupNum + 1
End If
ResultRs.Close
WishesRs.MoveNext
Loop
End With
WishesRs.Close
DbSn.Close
MsgBox "Done!" & vbCrLf & vbCrLf & " Total Time is (hh:mm:ss) " & Format(Time - start, "hh:mm:ss ")
DoCmd.OpenReport "SwapAllList", acViewPreview 'acViewNormal
End If
End If
Btn1.Enabled = True
Btn2.Enabled = True
End Subشاكر لكم تعاونكم
