Incorporate criteria into event procedure

[lndsy]

I have the following event procedure:

Private Sub Form_AfterUpdate()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.SetWarnings False

If [TYPE] <> "CHEST X-RAY" Then DoCmd.OpenQuery "UPDATE - TB DUE DATE"

DoCmd.SetWarnings True

End Sub

For the life of me, I cannot figure out how to incorporate additional criteria.

I would like it to run on the additional conditions that If [TYPE]= "MANTOUX" WHERE [RESULTS] isnotnull
or If [RESULTS] <> "COMPLETED"

Any help in writing this would be much appreciated, as I am not adept at writing code and I have read for hours on how to write this and still cannot figure it out. Thank you.

If this doesn't make sense, I can try and clarify further

 

[Axoliien]

If I understand correctly, you want to run your IF...THEN statement on the conditions (CRIT1 OR CRIT2 OR CRIT3).

If I assume correctly, then you can just take what you have and put the criteria in parentheses in a single if statement. In queries, the WHERE statement is a listing or criteria, so you can include that also in your total statement. As I understand, here is your pseudocode.

If  (Type is not CHEST X_RAY) OR
    (RESULTS are not Completed) OR
    ((Type is Mantoux) AND (Results are not null))
Then
    Open the UPDATE TB-DUE DATE query
Else
    'Do something else
End if

Is this the correct pseudocode?

 

[lndsy]

yes

 

[Axoliien]

This is close to what you are looking for. All I am doing is combining the criteria into a single statement. When you have different criteria that do different things, you can make separate if statements for each different criteria. When you have different criteria that each do the same thing, you can make one if statement and "OR" each criteria that does the same thing.

If(([TYPE]<>"CHEST X-RAY") OR ([RESULTS]<>"COMPLETED") OR ([TYPE]="MANTOUX" AND Not IsNull([RESULTS])) Then
    DoCmd.OpenQuery "UPDATE - TB DUE DATE"
Else
    'Do something else, or nothing, or remove the else.
End If

As a general rule, you apply the same logic to IF/THEN/ELSEIF/ELSE as you do to queries.

 

[Axoliien]

Forgot the closing parentheses, and the line broke. Let me reformat. This should keep the lines from breaking.

If (([TYPE]<>"CHEST X-RAY") OR _
    ([RESULTS]<>"COMPLETED") OR _
    ([TYPE]="MANTOUX" AND Not IsNull([RESULTS]))) _
Then
    DoCmd.OpenQuery "UPDATE - TB DUE DATE"
Else
    'Do something else, or nothing, or remove the else.
End If

 

[lndsy]

well...for some reason...it is not working

when I enter a record with "MANTOUX" TYPE and null RESULTS, the query still runs...the same with "COMPLETED" RESULTS.

I don't want the query to run if the above criteria is met.

Thank you Axoliien!

 

[Axoliien]

The logic says that if any of the three criteria are satisfied, the query will still run. My understanding and the pseudocode describe this, so if you want ALL criteria to be satisified for the query to run, then you must have ANDS instead of ORS. Then only if everything is true will the query run. Perhaps you are not specifying exactly what you need correctly. This is what the pseudocode specifies:

If you enter a record with TYPE <> "CHEST X-RAY" then no matter what else in the record, the query will still run.

If you enter RESULTS<>"COMPLETED" then no matter what else in the record, the query will still run.

If you enter TYPE=MANTOU AND RESULTS has anything at all in it (including the letters null or NULL) then the query will still run.

 

[lndsy]

I am sorry Axo...I don't mean to be confusing

If the TYPE is "CHEST X-RAY", I do not want the query to run.

Or

If the RESULTS are "COMPLETED", I do not want the query to run.

Or

If the TYPE is "MANTOUX" AND the RESULTS are null, I do not want the query to run.

Just re-stating for good communication...Is this what the code specifies?

 

[lndsy]

This variation works for some reason Axo. Thank you very much for your help!

DoCmd.SetWarnings False

If (([TYPE] = "CHEST X-RAY") Or _
([RESULTS] = "COMPLETED") Or _
([TYPE] = "MANTOUX" And IsNull([RESULTS]))) _
Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.OpenQuery "UPDATE - TB DUE DATE"


End If


lndsy