Designing a code to assign date based on condition from another table

[5hadow]

Hello all!
With the code below, I have RsHPOD!fldIQA = RsHPOD!LastAudit + 365. What I want it to be is as follows:
I want = RsHPOD!LastAudit + 365, ONLY IF the month of result matched ONE OF MONTHS in another table which contains list of dates as date "fldMonth" and checkbox field "fldActive"=True. Essentially I want to set the value only if I have those months active.
IF NOT match then
I want it to loop and set the new date +1 month until it satisfies above condition.

To help with this I have

Dim strMonth as String
Dim RsMonth as Database
strMonth = "SELECT tblMonth.ID, DatePart('mmmm',[fldmonth]) From tblMonth WHERE (((tblMonth.fldActive)=True))"
Set RsMonth = db.OpenRecordset(strMonth, dbOpenDynaset)

I'm trying to visualize this, but I can't poop out code. Can someone help?

    Do While Not RsHPOD.EOF
        RsHPOD.Edit
        RsHPOD!fldIQA = RsHPOD!LastAudit + 365
        RsHPOD.Update
        RsHPOD.MoveNext
        RsMonth.MoveNext
        If RsMonth.EOF Then
            RsMonth.MoveFirst
        End If
    Loop

 

[theDBguy]

Can you post a sample db with test data?

 

[5hadow]

Hi!

Here is a sample of DB.
frmIQASched is the main form.

When I press "Generate" button, I want the following value in "fldIQA" field:

Date based on "LastAudit" + 1year (if the PA = "High" and fldPriChange = True)
or Date based on "LastAudit" + 4 years (if the PA = "Low" and fldPriChange = False)

The resulting date has to be one of the "Checked" months on the subform, if the date falls under unchecked month, then it adds another month to date until it lands on checked date.

 

[5hadow]

I think you might be making this more complicated than necessary. Instead of describing the code you want us to fix, please try to describe the business problem you are trying to solve. Use little words that your dumbest user would understand and avoid industry jargon unless you can explain it. You can assume a moderate level of business knowledge. We all know what basic business systems do but we don't know how your business wants to operate if it is non standard.

Reading between the lines, it sounds like you want to schedule the next audit but you only do audits during some months of the year. So, the date you need will be the current audit date plus one year but if you aren't doing audits that month, find the next available month using a table.

Good point.

So yes, trying to conduct audits based on selected months where high priority is conducted every one year and low priority every four years. If the due date falls on the month it's not selected then I just want to assign it next first selected month. Hope that clears it up a bit.

 

[5hadow]

You need to use a few more nouns. I can't make any sense of what "the month" means in this context.

How do you determine if an audit is high or low priority? What months of the year do you schedule each type in? Is there a maximum number of audits that can be scheduled for any given month? Looking ahead, if you change the monthly schedule in the future, does that change the date for any audit already scheduled?

Hi,
I've uploaded a sample DB.

How do you determine if an audit is high or low priority?

[Work Instructions]!PA determines whether it's HIGH or LOW priority.

What months of the year do you schedule each type in?

Both HIGH and LOW priority are assigned to first available month, HIGH is on one year cycle, LOW is on four year cycle.

Is there a maximum number of audits that can be scheduled for any given month?

I want to keep max at 4. If there are more then I want LOW priority to go +/- 2months.

Looking ahead, if you change the monthly schedule in the future, does that change the date for any audit already scheduled?

Yes because dates are based on LastAudit field, so I'd just want to re-calculate everything on press.

By the way, here is what I have so far:

Function MakeSched()
Dim RsMonth As Recordset
Dim RsHP As Recordset
Dim RsLP As Recordset
Dim RsHPOD As Recordset
Dim RsLPOD  As Recordset
Dim LA As Date
Dim strLPOD As String
Dim strMonth As String
Dim strHP As String
Dim strHPOD As String
Dim strLP As String
Dim RsLM As Recordset
Dim strLM As String
Dim intLMID As Long
Dim strOD As String
Dim strDM As String

strHP = "SELECT [Work Instructions].*, tblWIUnion.* FROM [Work Instructions] INNER JOIN tblWIUnion ON [Work Instructions].ID = tblWIUnion.fldWIID " & _
"WHERE ((([Work Instructions].PA)='High')) OR (((tblWIUnion.varPriChange)=True))"

strHPOD = "SELECT [Work Instructions].*, tblWIUnion.* FROM [Work Instructions] INNER JOIN tblWIUnion ON [Work Instructions].ID = tblWIUnion.fldWIID " & _
"WHERE ((([Work Instructions].PA)='High') AND (([Work Instructions].LastAudit)<=Date()-365)) OR (((tblWIUnion.varPriChange)=True))"

strLPOD = "SELECT [Work Instructions].*, tblWIUnion.* FROM [Work Instructions] INNER JOIN tblWIUnion ON [Work Instructions].ID = tblWIUnion.fldWIID " & _
"WHERE ((([Work Instructions].PA)='Low') AND (([Work Instructions].LastAudit)<=Date()-365)) OR (((tblWIUnion.varPriChange)=False))"


strLP = "SELECT [Work Instructions].*, tblWIUnion.* FROM [Work Instructions] INNER JOIN tblWIUnion ON [Work Instructions].ID = tblWIUnion.fldWIID " & _
"WHERE ((([Work Instructions].PA)='Low')) AND (((tblWIUnion.varPriChange)=False))"

strMonth = "SELECT tblMonth.ID, fldmonth From tblMonth WHERE (((tblMonth.fldActive)=True))"
strLM = "SELECT * FROM tblWIUnion"

Set db = CurrentDb
Set RsHP = db.OpenRecordset(strHP, dbOpenDynaset)
Set RsLP = db.OpenRecordset(strLP, dbOpenDynaset)
Set RsMonth = db.OpenRecordset(strMonth, dbOpenDynaset)
Set RsLM = db.OpenRecordset(strLM, dbOpenDynaset)
Set RsHPOD = db.OpenRecordset(strHPOD, dbOpenDynaset)
Set RsLPOD = db.OpenRecordset(strLPOD, dbOpenDynaset)
       
    Do Until strDM = RsMonth!fldmonth
        If RsHPOD!LastAudit + 365 = RsMonth!fldmonth Then
            strDM = RsHPOD!LastAudit + 365
        Else
            strDM = DateAdd(m, 1, RsHPOD!LastAudit)
        End If
    Loop
   
    Do While Not RsHPOD.EOF
        RsHPOD.Edit
        RsHPOD!fldIQA = RsHPOD!LastAudit + 365
        RsHPOD.Update
        RsHPOD.MoveNext
        RsMonth.MoveNext
        If RsMonth.EOF Then
            RsMonth.MoveFirst
        End If
    Loop
    MsgBox "Generated!"

    End Function