I had a previous thread here: https://www.experts-exchange.com/questions/29134150/MS-Access-Adding-Columns-Containing-Criteria-Language-To-Be-Called-Within-An-Expression.html
" that's now locked because I thought I had a solution. I am not circling back to try and implemenl.
@Jim Dettman was kind enough to assist me in the previous thread with code below, but it came back with a syntax error in VBA:
If Date() => DateAdd("d",rs![AdvanceNoticeDays], rs.Fields(strFieldName) = True Then
' Send e-mail.
The first form is "QuestionnaireF". It populates a ProjectQuestionnaireT and has these columns:
QuestionaireID (primary key)
(There are more columns, but I'm simplifying for example purposes).
The second form is user's requested "NotificationF". This form addresses what type of things users want to be notified about and is driven by a NotificationCriteriaT consisting of these columns:
NotificationCriteriaID (primary key)
PeopleID (integer) -Person who wants the notifications
NotificationSubject (nvarcharMAX) -This is a column from ProjectQuestionnaireT... for example, OilTruckingChanges. Column is selected using combobox.
SubjectDateColumn (nvarcharMAX) -This is a column from FacilityProjectT… for example, ConstructionStartDate. Column is selected using combobox.
AdvancedNoticeDays (int) Number of days in advance user wants notification prior to date referenced in SubjectDateColumn.
I have construction projects in a table called FacilityProjectT. That's where the actual dates are for fields like "ConstructionStartDate".
My goal first goal is to press a button (or run AutoExec) that checks requested date fields in the FacilityProjectT. Those are requested in the NotificationF (form) “SubjectDateColumn” combo box entry. Notifications should go out if the project meets the user requested criteria in the ProjectQuestionnaireT (such as OilTruckingChange = Yes). I’ve struggled with a code that brings this together. Maybe I need another table to track notifications needed and sent… But the main thing I need is to send a notification email when the project’s data become a match to the notification criteria. I have all the email code. I just need help with the evaluation and completing the vision.
NotificationT To FacilityProjectT:
Method of selecting notifications:
Feel free to ask for more info if I'm not clear on my vision.