Function SCHD2()
Dim bolOK As Boolean
Dim db As DAO.Database
Dim rstIQA As DAO.Recordset
Dim rstNewTable As DAO.Recordset
Dim strIQA As String
Dim nxtInspection As Date
Dim intInsp As Integer
intInsp = Forms!frmSCHED!txtInsp.value
strIQA = "SELECT tblWIUnion.fldDocID, tblWIUnion.fldWISerial, [Work Instructions].DocTitle, [Work Instructions].PA, [Work Instructions].LastAudit, tblWIUnion.fldIQADue, tblWIUnion.fldIQA " & _
"FROM [Work Instructions] INNER JOIN tblWIUnion ON [Work Instructions].ID = tblWIUnion.fldWIID " & _
"WHERE (((tblWIUnion.fldWISerial) Is Not Null));"
Set db = CurrentDb
Set rstIQA = db.OpenRecordset(strIQA, dbOpenDynaset)
Set rstNewTable = db.OpenRecordset("tblIQASchedule", dbOpenDynaset)
Call EmptyTable("tblIQASchedule")
With rstNewTable
rstIQA.MoveFirst
Do Until rstIQA.EOF
.AddNew
![fldWISerial] = rstIQA![fldWISerial]
![fldTitle] = rstIQA![DocTitle]
![fldPA] = rstIQA![PA]
![fldLastAudit] = rstIQA![LastAudit]
![fldAuditDue] = rstIQA![fldIQADue]
![fldAuditDate] = rstIQA![fldIQA]
bolOK = False
nxtInspection = Nz(rstNewTable!fldAuditDue, Date) 'Sets next IQA date
Do Until bolOK
If DCount("fldAuditDate", "tblIQASchedule", "Format$(fldAuditDate,'yyyymm')='" & Format$(nxtInspection, "yyyymm") & "'") < intInsp Then 'If number of next IQA dates per month is less than set by user.
If DCount("1", "tblMonth", "Month(fldMonth) = " & Month(nxtInspection) & " And fldactive = -1") <> 0 Then 'If other criteria set by user is met.
bolOK = True 'Date above is then accepted.
End If
End If
If Not bolOK Then 'If user criteria is not met
nxtInspection = DateAdd("m", -1, nxtInspection) 'Set next IQA date to one month less and try again.
End If
Loop
If nxtInspection < Date Then
nxtInspection = Date
End If
rstNewTable!fldAuditDate = nxtInspection
.Update
rstIQA.MoveNext
Loop
.Close
End With
Set rstNewTable = Nothing
db.Close
Set db = Nothing
DoCmd.Requery