Designing a code to assign date based on condition from another table (1 Viewer)

[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?

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

If the due date falls on the month it's not selected

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?

 

[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

 

[Pat Hartman]

You said you have High and Low priority and that is stored in the field called PA. I don't get the name so I would never identify it had I looked at a list of names so you might want to keep things like that in mind as you are chooseing abbreviations. My preference is don't use acronoms for naming columns unless the acronom is in wide use like SSN.

Then you said you want to keep max at 4 but if there are more, then I want LOW priority to go to +/- 2 months. "more" what? How would the algorithm decide whether to do +2 or -2? are +1 and -1 also options?

The next audit date should be calculated when the current audit is complete and the trigger for the next audit will be added programmatically, NEVER by an update query unless you have a scheduling problem so you need to reschedule a bunch of audits at once. The next scheduled audit date is added to the table in the AfterUpdate event of the form when the audit complete date is entered. This is tricky. You loose the original .OldValue property after the record gets saved so in the BeforeUpdate event of the form, you need to set a flag for yourself so when the AfterUpdate event of the form comes around, you know that the AuditCompleteDate was set. For Example:

If IsDate(Me.AuditCompleteDT) Then
    If IsNull(Me.AuditCompleteDT.OldValue) Then
        AuditCompleteYN = True
    Else
        AuditCompleteYN = False
    End If
End If

Then in the Form's AfterUpdate event:

AuditYN = False
'''code to insert the next audit date

Should the next audit use the current audit date in the calculation for the next or should it use the audit completed date? Does the audit frequency automatically change from High to Low at some point or does a person always decide? If it is not automatic, then the frequency needs to be updated at the end of each audit to be used in the calculation to determine when to do the next audit.

I also see an argument in the queries for PriceChange. What is that and how does it affect the calculation?

Also, why are you using 365 days rather than the DateAdd() function with an argument of 1 year?

Computers are faster than the speed of light but dumb as rocks. They can only do EXACTLY what you tell them to do and so far, I don't have enough information to build an algorithm. I've been designing and developing software for over 50 years and the hardest part of the job is determining WHAT needs to be done.

Before any queries get written, we need pseudo code that outlines all the rules.