troubleshooting Question

Notification System based on responses to a Questionnaire, construction dates, and user requested notification criteria.

Avatar of Jason Steward
Jason Steward asked on
Microsoft AccessVBA
22 Comments1 Solution141 ViewsLast Modified:
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)
OilTruckingChanges (yes/no)
ProducedWaterTruckingChanges (yes/no)
(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:
Query
Method of selecting notifications:
NotificationSelector
Feel free to ask for more info if I'm not clear on my vision.
ASKER CERTIFIED SOLUTION
Jason Steward

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros