Add to table at specified time

[craigprice]

Hi,

I have created a form with "notifications" I am wanting to create a table which contains different types of notifications that will automate (some are manually input too)

I have tried this with the following but it seems to just hang when the form runs.

Basically I want it to look at all records on the current form/table and if the time is the present time then it will add the relevant record to the table.

So far I tried this:

Private Sub Form_Timer()
 [CLOCK].[Requery]
 Me.Requery
 
   
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.CurrentDb
Set rs = db.OpenRecordset("updates")
    
With rs
 Do Until .EOF
If Me.Time = Me.CLOCK Then
      rs.AddNew
   rs!Title = "" & [Title]
   rs!Message = "" & [Message]
   rs!author = "OCP-AUTOMATION"
   rs.Update
ElseIf Me.Dirty = False Then
.MoveFirst
 End If
Loop
End With
End Sub

I managed to get it to run for the first record and it successfully adds it when the relevant time is reached however I want it to continually check all records.

I'd like the time to issue to be taken from a table as I plan on giving admins the ability to add additional scheduled notifications to this.



Example of the table the "templates" are pulled from

ID time day title message
8 21:22:40 Monday TIMESHEETS AUTOMATION TEST

 

[JHB]

I've some difficulties to imagine what you want to do, maybe others is in the same "boat", then you haven't got any replies.
You can use the form's Recordsetclone to cycle through all records showing in a form. And instead of using a recordset for append records to a table, you can use an Append query.
Else try to explain what you want to do in another way - maybe some printscreens could help.

 

[craigprice]

I have a table called updates.

This table it manually fed into by "admins" this is basically a way of sending messages to front end users.

The table contains a date, time title for the notification type and then the body of the message.

what I am trying to do here is create another form which basically runs continuously and checks records from another table and if the time in the time field matches the current time then it copies the record and adds it into the updates table therefore automatically adding the notification for the user, the idea being that regular tasks will notify the user.

I have therefore created a second table which has these "template" notifications in.

I can get record 1 to copy but it will not move through the records to perform the checks mentioned about (time) on the other records.

Hope this helps a little?

 

[JHB]

...

I can get record 1 to copy but it will not move through the records to perform the checks mentioned about (time) on the other records.

Hope this helps a little?

Do you mean the records showing in the form?
If yes, then use the Recordsetclone.

 

[craigprice]

Yeah, would I need to set the form to continuous or single?

Also would this continue to loop round and check the records?

I can't figure out where to put this in either...

 

[Minty]

As you already have a table of events why not query it and display any pending events for the current user or user group on your switchboard or landing form on your database? Any events that are overdue conditonally format in RED.
If you added an ActionedDate or similar to the event record it could then be marked off and excluded from the list. You then don't need to do anything on that form other than requery it on the timer event.

 

[craigprice]

I had thought about this but I want the items to be time/day specific, therefore I thought adding an individual line to the table each time that time/day is hit it would be easier than trying to get a query to do this.

For example if I used a query I may have a notification to display at 23:50 on Monday, by 00:00 Tuesday this notification would no longer be in view because the day has changed

 

[Minty]

If you used a control field to determine if the reminder had been actioned, it would still display, provided you stored a datetime eg.

Reminder Time ------- ActionedDate
04/04/2016 22:50 ------ Null

Your query would simply be something like

SELECT * from tblReminders Where ReminderTime< NOW() AND ActionedDate is Null

 

[craigprice]

How would I then go about clearing this data (actioned) so that the following week the item would appear again for actioning?

 

[Minty]

Ah - I see you want repeating reminder actions to show up ( I missed the repeating bit from your OP) . That's a little more interesting to get round. I would assume you want to record who cleared the action down, and when?

Sticking with the actioned idea - why not simply create a list of waiting to be actioned reminders stretching as far into the future as you need, using the same logic above they won't appear until the due date.

You could make a form to create both repeating until x date, or single reminder entries. The form would need some code to loop around and insert the appropriate number of entries for the repeating reminders.

 

[craigprice]

To add more complication the items may differ on weekends too so don't know how well that would work...

Not wanting to sound difficult just trying to explain as best I can

Thinking about it, I think the easiest way to explain what I want to do is basically run an IF statement on all records on a continuous form...

I may have just made this whole question a lot more complicated than it needed to be.

 

[Minty]

Often with the right data structure things become a lot clearer and cleaner to deal with. We still use a paper and pen to draw out processes first to spot the holes.
It's why the more experienced guru's on here bang on about getting the tables right at the outset.

The other thing you could look at is to automate an outlook reminder, it may end up being better suited as it will ping up on a mobile if the device is linked back to exchange etc.

 

[craigprice]

I had thought about using outlook as an option but I think it may be irritating for management to have their phones going off with the regularity of the different notifications we'd have.

Is there a simple way to get an IF statement to run on each record on the current form?

I thought something like this would work if I used the go to next record comment after the IF and then looped but when I do that it seems to hang, if I don't put a loop on it moves itself to record 2 but doesn't run the IF statement again for that row or move onto the next record (record 3)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = Access.CurrentDb
Set rs = db.OpenRecordset("updates")
    
If Me.Time = Me.clock And Me.day = Me.notifday Then
      rs.AddNew
   rs!title = "" & [title]
   rs!message = "" & [message]
   rs!author = "OCP-AUTOMATION"
   rs.Update

 

[craigprice]

Ok, I managed to get it to work in a sense, now I just need to stop it from adding duplicates but can't figure this out!

This is what I have so far

 Private Sub Form_Timer()
 Dim db As DAO.Database
Dim rs As DAO.Recordset
 Set db = Access.CurrentDb
Set rs = db.OpenRecordset("updates")
 If Me.day = Me.notifday And Me.test Like Me.Time & "*" Then
      rs.AddNew
   rs!Title = "" & [Title]
   rs!Message = "" & [Message]
   rs!Author = "OCP-AUTOMATION"
   rs.Update
   
   End If
 [clock].[Requery]
 If Me.Time = Time() Then
DoCmd.GoToRecord , , acNext
Else
DoCmd.GoToRecord , , acFirst
End If
   
 End Sub

The problem I have now is that while the forms refresh if it is still within the same minute that the notification should input to the table it inputs it again, I can't figure out how to stop this, I thought maybe have it check to see if the time and title are the same then do not input it, maybe I need to make it add the current date to the table too and then if it sees the date already exists with the same notification and time then it will not add, any ideas?

Thanks for all your help so far!

 

[Cronk]

If you want to display something to each user at or after a certain time, you need to store an acknowledgement for each user, that that user has seen the message.

The condition for display to a particular user would then be
Now() > DisplayDateTime and UserIDAck = False

When a particular user acknowledges whether at the set time, an hour later or a week later, set the UserIDAck for that user to true.

To have the message display again the following day or week or whatever, reset the DisplayDate to when you want the message re-displayed and set the acknowledgment to false.

 

[Minty]

Why not set the form refresh to 60 seconds it's then not able to refresh more than once per minute period. Or store the minute value in a persistent variable and don't run the refresh if it's not changed.