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

Jason Steward
Jason Steward used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
What is the error message?

Author

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.
Distinguished Expert 2017

Commented:
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 you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
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)

Author

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 Engineer

Commented:
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.

Author

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 Engineer

Commented:
if your screenshot is correct you have an unjoined that will produce a "Cartesian product" .....usually this is undesired.
Distinguished Expert 2017

Commented:
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 Engineer

Commented:
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.

Author

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 Engineer

Commented:
Either way works but it would be more handy to use it inside a function that you will use it in a query.
Distinguished Expert 2017

Commented:
In the long run, fixing the design flaw will save lots of work.  This solution is only a band-aid.

Author

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.
Distinguished Expert 2017

Commented:
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 Engineer

Commented:
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

Author

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!!!

Author

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.

Author

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 Engineer

Commented:
Hopefully this is what you want
Jason.accdb
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial