Need Expert Advice

Zippyfrog · July 9th, 2006, 07:42 PM

Hello. Currently I am creating a random class scheduling program for a school, and I am running into a snag. I have the scheduler creating a schedule, but I want to add a "check" line of code to make sure that two records in the table don't have both identical teacherID's or Periods. Right now all classes are assigned to a period using a random number, and if the number has already been in use for a period for a given teacher, I want that one record to have a different random number added. Please let me know if anyone has any suggestions because I am scratching my head on this one...

Here is the code that I have thus far:

  Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strTableName As String

    strSQL = "SELECT qryDynamicTeacherRequests.SchoolYearID, qryDynamicTeacherRequests.TermID, qryDynamicTeacherRequests.ClassID, qryDynamicTeacherRequests.RoomID, qryDynamicTeacherRequests.TeacherID " & _
             "INTO tblTempMasterSchedule " & _
             "FROM qryDynamicTeacherRequests;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTempMasterSchedule")
    Set fld = tdf.CreateField("PeriodID", dbDouble)

    tdf.Fields.Append fld

    Set rst = db.OpenRecordset("tblTempMasterSchedule", dbOpenTable)

    rst.MoveFirst
    Do
    rst.Edit
            rst![PeriodID] = Int((8 - 1 + 1) * Rnd() + 1)
        rst.Update

(RIGHT HERE I NEED SOME CODE ADDED!! Basically, I want something to the effect of If the combination of PeriodID and TeacherID are the same on any previous record, I want this step to loop until a random number is placed in this PeriodID field until the Period/Teacher combination is unique )

        rst.MoveNext
    Loop Until rst.EOF

    rst.Close
    Set rst = Nothing

mmcdonal · July 12th, 2006, 02:34 PM

You can just create a find duplicates query and check to see if the recordcount = 0, if not then do something else.

Otherwise you have to open the same table in two recordsets, and then loop through one inside the other looking for dupes.

Or use a Union query...

I am not sure which way you want to go. Right now it looks like the nested loop.

More later if you want help. I just implemented a similar solution so have lots of code.

mmcdonal

Zippyfrog · July 13th, 2006, 04:13 PM

Thanks for the advice. Here is what i have come up with over the past couple of nights, but I am stuck. I am getting a type mismatch, and I can't figure out why I am getting a type mismatch because everything is a Long variable... Any ideas would be greatly appreciated.

These two lines are driving me nuts...
Me.Recordset.FindFirst ("TeacherIDCompared = " & TestTeacher & "" And "PeriodIDCompared = " & TestPeriod & "")
Me.Recordset.FindNext ("TeacherIDCompared = " & TestTeacher & "" And "PeriodIDCompared = " & TestPeriod & "")

Here is the entire code.

Code:

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim rst3 As DAO.Recordset
    Dim bpass As Boolean
    Dim bsearch As Boolean
    Dim varTest As Integer
    Dim strSQL As String
    Dim strTableName As String

    varTest = Int((8 - 1 + 1) * Rnd() + 1)


    strSQL = "SELECT qryDynamicTeacherRequests.SchoolYearID, qryDynamicTeacherRequests.TermID, qryDynamicTeacherRequests.ClassID, qryDynamicTeacherRequests.RoomID, qryDynamicTeacherRequests.TeacherID " & _
             "INTO tblTempMasterSchedule " & _
             "FROM qryDynamicTeacherRequests;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTempMasterSchedule")
    Set fld = tdf.CreateField("PeriodID", dbDouble)

    tdf.Fields.Append fld

    Set rst = db.OpenRecordset("tblTempMasterSchedule", dbOpenTable)


    rst.MoveFirst
        rst.Edit
        rst![PeriodID] = Int((8 - 1 + 1) * Rnd() + 1)
        rst.Update
        rst.MoveNext
    Do
           rst.Edit
           rst![PeriodID] = Int((8 - 1 + 1) * Rnd() + 1)
           rst.Update
         rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Set rst = Nothing

'Copy info into final Master Schedule Table
    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.SetWarnings False
    stDocName = "qryDynamicTeacherRequestsToMasterSchedule"
    DoCmd.OpenQuery stDocName
    DoCmd.SetWarnings True

        '==========================================================
        'Checking Code
        'Needs to check to make sure TeacherID/PeriodID is unique
        'i.e. a teacher can't be assigned 2 classes the same period
        'during master schedule generation
        '==========================================================
Dim TestTeacher As Long
Dim TestPeriod As Long
Dim TeacherIDCompared As Long
Dim PeriodIDCompared As Long


Set rst2 = db.OpenRecordset("tblMasterSchedule", dbOpenTable)
Set rst3 = rst2.Clone

Do Until rst2.EOF = True
    TestTeacher = (rst2![TeacherID])
    TestPeriod = (rst2![PeriodID])
    TeacherIDCompared = (rst3![TeacherID])
    PeriodIDCompared = (rst3![PeriodID])


    Do Until bpass = True
    With rst2
        If bsearch = False Then
            With rst3
               rst3.MoveFirst
               Me.Recordset.FindFirst ("TeacherIDCompared = " & TestTeacher & "" And "PeriodIDCompared = " & TestPeriod & "")
               Me.Recordset.FindNext ("TeacherIDCompared = " & TestTeacher & "" And "PeriodIDCompared = " & TestPeriod & "")
               If .NoMatch = True Then
                    bpass = True
                    bsearch = False
                Else
                    bpass = False
                    bsearch = True
               End If
            End With
        Else
            !PeriodID = varTest
            .Update
            '.LastModified
            !PeriodID = TestPeriod
            With rst3
               Me.Recordset.FindFirst ("TeacherIDCompared = " & TestTeacher & "" And "PeriodIDCompared = " & TestPeriod & "")
               Me.Recordset.FindNext ("TeacherIDCompared = " & TestTeacher & "" And "PeriodIDCompared = " & TestPeriod & "")
               If .NoMatch = True Then
                  bpass = True
                  bsearch = False
               Else
                  bpass = False
                  bsearch = True
               End If
             End With
        If bpass = True Then .MoveNext
        End If
    End With
   Loop
Loop
        '==========================================================
        'End Checking Code
        '==========================================================

mmcdonal · July 14th, 2006, 06:20 AM

Two things I see:

rst![PeriodID] = Int((8 - 1 + 1) * Rnd() + 1)

If this is long, then Int() may not work.

Also:

Me.Recordset.FindFirst ("TeacherIDCompared = " & TestTeacher & "" And "PeriodIDCompared = " & TestPeriod & "")

I would recast this sort of line as:

Dim sSQL As String

sSQL = "[TeacherIDCompared] = " & TestTeacher & _
" And [PeriodIDCompared] = " & TestPeriod

Me.Recordset.FindFirst(sSQL)

I am still looking this over. Did that help?

mmcdonal

Zippyfrog · July 15th, 2006, 09:17 AM

Thanks! I didn't consider doing it that way.