asked on

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:

Method of selecting notifications:

Feel free to ask for more info if I'm not clear on my vision.

PatHartman

What is the error message?

Jason Steward

ASKER

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.

PatHartman

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.

John Tsioumpris

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 Steward

ASKER

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 Tsioumpris

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 Steward

ASKER

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 Tsioumpris

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

PatHartman

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 Tsioumpris

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 Steward

ASKER

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 Tsioumpris

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

PatHartman

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

Jason Steward

ASKER

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.

PatHartman

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 Tsioumpris

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 Steward

ASKER

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 Steward

ASKER

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 Steward

ASKER

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 Tsioumpris

Hopefully this is what you want
Jason.accdb

Gustav Brock

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

ASKER CERTIFIED SOLUTION

Jason Steward

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial