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

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.

Open in new window


The first form is  "QuestionnaireF".  It populates a ProjectQuestionnaireT and has these columns:  

QuestionaireID (primary key)
OilTruckingChanges (yes/no)
ProducedWaterTruckingChanges (yes/no)

Open in new window

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

Open in new window


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.
Jason StewardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
What is the error message?
Jason StewardAuthor Commented:
Error in syntax.  I'm not sure how to implement that code, so the error may be on my part.  I've been struggling with this for awhile and decided to reach out for help from the community for ideas on how to send notifications when user selected criteria have been met.
PatHartmanCommented:
If the error is a compile error, where does the highlight appear?
If the error is a runtime error, it may be that the variable does not contain a valid column name.  Put a stop in the code on that expression and when the code stops, print rs.Fields(strFieldName) to the debug window to see what it contains.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

John TsioumprisSoftware & Systems EngineerCommented:
I am not sure if i read your issue correctly but if the error occurs in this line
 If Date() => DateAdd("d",rs![AdvanceNoticeDays], rs.Fields(strFieldName) = True Then

Open in new window

its rather clear that the problem is due to erratic values from the rs recordset.
Just put a bookmark and check exactly what values you have in
rs![AdvanceNoticeDays] & rs.Fields(strFieldName)
Jason StewardAuthor Commented:
Thanks, John.  The error isn't really with just that part of code.  I have components of a system, but not sure how to pull it all together.  That code was a hint if someone knew how to help design a process around it.  That code can't work because it's not integrated with an overall solution.  Perhaps if I understood it, I could build a system around it.  I'll research "bookmark" and try to find out what that is and how to use it.  Thanks.
John TsioumprisSoftware & Systems EngineerCommented:
You got me  more confusing ....probably i should take a look at your previous question...to my understanding you are trying to implement some kind of notification system (maintenance tasks ?) ....well you must clear out the condition when the notification will be sent..after that ..when the condition is true this can be accomplished in many ways ...e.g. right away send emai, add a calendar entry...let the application run on intervals...and so on.
Jason StewardAuthor Commented:
Today I worked on a table called "NotificationActionT".  I have columns for NotificationNeeded and NotificationSent.  If I can get the NotificationNeeded updated, I think I can handle the NotificationSent using an update query following the email action.  

I need to figure out how to integrate the Questionnaire, User notification preferences, and the NotificationActionT to notifiy users only if they need notifications based of the type things they want to be notified about and the current status of the project.
John TsioumprisSoftware & Systems EngineerCommented:
if your screenshot is correct you have an unjoined that will produce a "Cartesian product" .....usually this is undesired.
PatHartmanCommented:
The reason you are having trouble with this is because the database is not properly structured.  You have a repeating group.  Instead of having multiple columns each with a different date, you should have a separate table with one row per date type per person.
John TsioumprisSoftware & Systems EngineerCommented:
Well , i checked your previous question and i am thinking that probably there is some kind of "circling"
As Jim said in your old question the implementation could be based on Eval command but it requires careful "design" to avoid mistakes that could bomb everything out..
Another way ...similar is decision tables...which act on the same concepts...you write down the conditions and the probable values and you iterate the table to get the correct "answer"...after that ,depending on the implementation you have chosen it will notify the person/s in the proper manner.
Jason StewardAuthor Commented:
Can Eval() be added to a query or just VBA?  If it can go into a query, how do yo structure with GUI design view instead of SQL view?
John TsioumprisSoftware & Systems EngineerCommented:
Either way works but it would be more handy to use it inside a function that you will use it in a query.
PatHartmanCommented:
In the long run, fixing the design flaw will save lots of work.  This solution is only a band-aid.
Jason StewardAuthor Commented:
When a user adds their criteria, I just want to run an append query that adds all projects matching that criteria to the NotificationNeededT table.
PatHartmanCommented:
Jason,
Someone may code this for you but that is far from the end of your problem since you have much bigger problems.  Do some reading on normalization to see if you can understand why all those dates in one table are a repeating group.  Usually, repeating groups are identified because they have a numeric suffix but Jan, Feb, Mar - Dec columns are also a repeating group even though each month has a unique name.  Once you normalize the tables, the queries will be much simplier.
John TsioumprisSoftware & Systems EngineerCommented:
Well i am trying to grasp the concept but i am not sure...
I have prepared a small (quick and dirty) sample to "see" if i got the initial idea
Its a bit "hacky" but it should the job
checKIfItsTimeToNotify does the checking
QuestionNaires does the input
NotificationDates contains the NotificationDates

Give it a spin.
Jason.accdb
Jason StewardAuthor Commented:
John, that's awesome!   If I were a billionaire, I'd split it with you!  I should be able to adopt this to suit my needs.  I have been working with it for awhile this morning and I think the "notificationscope" makes it a little too complicated to implement because although Facility has an integer primary key, other options have a string...  so I'm taking that out of our equation.  Thanks so much!!!
Jason StewardAuthor Commented:
Pat, I'm not sure what you mean.  I don't believe I have any repeating columns.  It may appear that way because I queried field names.  I have a QuestionnaireT with questions.  I have a NotificationCriteriaT with users' preferences.  If a project's inclusion of a certain type of work is a match to user's preference for notifications, they would be notified.
Jason StewardAuthor Commented:
To anyone who might be interested, the solution was to code this in SQL using union queries.  This provided me exactly the information I need, then I just ran an append query to create a NotificationNeeded table with a field "NotificationNeeded" (default is "Yes").  From there, emails are sent then NotificationNeeded is updated to "No".  

USE [MyDatabaseName]
GO

/****** Object:  View [dbo].[vw_FacilityProjectNotification]    Script Date: 3/12/2019 4:50:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[vw_FacilityProjectNotification]
AS
SELECT a.PeopleID
, c.FacilityProjectID
,'Constructionstart'  as DateSource
, b.ConstructionStart AS DateBasis
, 'OilTruckingChange' as NotificationSource
, a.OilTruckingAdvancedNotice As DaysInAdvance
, b.[ConstructionStart]-[OilTruckingAdvancedNotice] AS NotificationNeededDate
FROM FacilityProjectNotificationUserPreferencesT a
	JOIN (FacilityProjectT b
		JOIN FacilityProjectNotificationQuestionnaireT c
			ON b.ProjectID = c.FacilityProjectID) 
		ON a.OilTruckingChange = c.OilTruckingChange
WHERE (((a.OilTruckingChange)<> 0))

UNION ALL

SELECT a.PeopleID
, c.FacilityProjectID
,'Constructionstart'  as DateSource
, b.ConstructionStart as datebasis
, 'OilPipelineDeliveryChange' as NotificationSource

, a.OilPipelineDeliveryAdvancedNotice As DaysInAdvance
, b.[ConstructionStart]-OilPipelineDeliveryAdvancedNotice AS NotificationNeededDate
FROM FacilityProjectNotificationUserPreferencesT a
	JOIN (FacilityProjectT b
	JOIN FacilityProjectNotificationQuestionnaireT c
		ON b.ProjectID = c.FacilityProjectID) 
		ON a.OilPipelineDeliveryChange = c.OilTruckingChange
WHERE (((a.OilPipelineDeliveryChange)<>0))

UNION ALL

SELECT a.PeopleID
, c.FacilityProjectID
,'Constructionstart'  as DateSource
, b.ConstructionStart as datebasis
, 'ProducedWaterTruckingChange' as NotificationSource
, a.ProducedWaterTruckingAdvancedNotice As DaysInAdvance
, b.[ConstructionStart]-ProducedWaterTruckingAdvancedNotice AS NotificationNeededDate
FROM FacilityProjectNotificationUserPreferencesT a
	JOIN (FacilityProjectT b
	JOIN FacilityProjectNotificationQuestionnaireT c
		ON b.ProjectID = c.FacilityProjectID) 
		ON a.ProducedWaterTruckingChange = c.ProducedWaterTruckingChange
WHERE (((a.ProducedWaterTruckingChange)<>0))


GO

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
Hopefully this is what you want
Jason.accdb
Gustav BrockCIOCommented:
Your code has a wrong comparator and is missing a parenthesis:

 If Date() >= DateAdd("d",rs![AdvanceNoticeDays], rs.Fields(strFieldName)) Then
       ' Send e-mail.

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.