Create way for an uneducated user to modify code?

[Foe]

I have a database for log keeping. I've created popups to remind users when it is time for an entry.

'Popup reminders for watch entries
'------------------------------------------------------------------------------
'Update information between [ and ] for new reminders. Remove the brackets in
'completed code - see existing entries for proper syntax
'Reminder code follows:  
'    If TimeValue(Now()) = #[ time goes here ]# Then   
'        msgbox "[ reminder goes here ]", vbOKOnly, "Daily Routine Reminder"
'    End If    
'------------------------------------------------------------------------------
Private Sub Form_Timer()
    If TimeValue(Now()) = #1:30:00 AM# Then
        msgbox "Random Thing", vbOKOnly, "Daily Routine Reminder"
    End If
    If TimeValue(Now()) = #2:00:00 AM# Then
        msgbox "Random Thing", vbOKOnly, "Daily Routine Reminder"
    End If
    If TimeValue(Now()) = #4:30:00 AM# Then
        msgbox "Random Thing", vbOKOnly, "Daily Routine Reminder"
    End If
    If TimeValue(Now()) = #6:00:00 AM# Then
        msgbox "Random Thing", vbOKOnly, "Daily Routine Reminder"
    End If
End Sub

*The actual reminders have been edited and truncated - code above is just to show how it's set up

The commented lines were added when I first set it up. The thought process was that if the Daily Routine was ever edited or updated then the popups would have to be edited in kind to match. I also figured that since these popups are a coded feature, that any new or modified popups would need to be created/modified from within the VBA editor itself.

This database is being built with a primary goal of being managed into the future by people that know nothing at all about Access (let alone VBA). As I've gone along in the build, I feel I've come up with some pretty solid ways to keep users in a sandbox and provide "database managers" with the tools needed to make adjustments to the database w/o ever having to enter a table directly.

And then there's these popup reminders... I can't think of a way to provide the "database managers" with a way to manage them without having to send them into the VBA editor.

Anyone have any ideas on a way to do this? Is is possible to build them a form with some underlying code that can make modifications to the existing code on another form?


disclaimer: Please don't tell me how bad of an idea it is to build a complex database and send it into the wild w/ no one to support it. I'm well aware this isn't ideal, but ideal isn't always an option. The unsupported database scenario is what started this project in the first place. A database is already in use that was built 6 years ago which nobody knows how to manage/modify. No one even knows who built it! This project will replace that one and have a ton of options built in (as many as I can think of) to allow someone with no experience to manage it into the future. I know nothing is totally future proof, but this is going to be much more "management-friendly" than what they have now.

 

[MarkK]

Those time thresholds are data, not code. If you store them in a table they'll be very easy for your users to edit in a form--at which time you can also relatively easily validate user input. Then, OnTimer, you can open a recordset or run a DCount() for records that match the current time and run your process--or not--based on that result.

I don't get the need for your disclaimer. You are free to post a question, others are free to respond, and you are free to not value their information.

 

[pbaldy]

I think you're going in the wrong direction. This db essentially has "data" stored in code. I'd have a table of the reminders, and give the users a form to maintain them. Your code checks the table rather than having everything in code.

 

[pbaldy]

Great minds think alike. Some just think slower than others.

 

[Cronk]

I've never had need to do it, but it is possible to export/import the text of a module and presumably possible to semi-automate the editing of the text outside of Access.

However, if it is only a msgbox that you want triggered at different times with different messages, why not store the times and message in a table and use generic code to read the values in the table each time the form timer event is called.

One other thing, you would be needing to call your timer event at least once every second, maybe even more than once to ensure that some other process does not delay the calling of the event and thereby missing the exact hour, minute, second.

You can make it more bullet proof by testing for example
TimeValue(Now()) >= #2:00:00 AM# AND LastRemindDate < Date()
where LastRemindDate is stored in the table I referred to above and is updated each time the reminder is run.

 

[MarkK]

Hey Paul, yeah, it's a game of minutes, seconds even.

 

[Foe]

Moving it all into a table is an awesome suggestion! Such a simple solution that I'm a little bummed I didn't think of it myself. I've even done it once already to move something else out of code to make it manageable via a form.

The timer is set to a 1000 interval to fire every second.

I also really like the TimeValue(Now()) >= #2:00:00 AM# AND LastRemindDate < Date() solution to cover hiccups that might occur when a timer was due. I'll be incorporating that for sure.

Thanks guys!

 

[Foe]

question about the table design:

Would a table with two fields (Time and Reminder), using Time as my primary key be a bad idea?

Also, do you have recommendations for setting up the LastReminderTime?

 

[Foe]

I'm in the editor now and I'm realizing I've never used DCount() before.

How would I set this up?

If DCount("Reminder", "tblReminders", "[Time] = #" & TimeValue(Now()) & "#") Then

My table currently has two fields. Time (Date/Time) and Reminder (Text). Time is currently the Primary key

I'm also unsure how to reference the appropriate reminder in the msgbox after the Then. I'm assuming the code below is nowhere near to correct. I'm kinda spitballing here...

msgbox SELECT tblReminders.Reminder FROM tblReminders WHERE [Time] = TimeValue(Now()), vbOKOnly, "Daily Routine Reminder"

 

[Cronk]

tblReminders
ReminderID
RemindTime
LastRemindDate
RemindMessage

In the timer event, open a recordset where date() > [LastRemindDate] and current time > [RemindTime]

Loop through the recordset with Msgbox rst!RemindComments,64,"Reminder"
and update [LastRemindDate] to Date()

 

[pbaldy]

Would a table with two fields (Time and Reminder), using Time as my primary key be a bad idea?

I personally wouldn't, as it's theoretically possible to have 2 reminders at the same time.

As to the method, I'd open a recordset. That would let you test for EOF to see if there are any, loop the results and present the messages to users if there are, and even update the DateRead field.

 

[pbaldy]

Jeez, again I'm slow. I'm going to get a Dr Pepper.

 

[Foe]

I've never dealt with RecordSets or looping either

I'll do some research and see what I come up with and run it by you guys if it doesn't work as intended.

 

[MarkK]

Paul, lol.

Foe, I also wouldn't run the timer every second, run a timer every 5 or 10 minutes and then check if you need to show a reminder. What are the criteria for that? And be careful checking for equality with date/time values. Equality in date/time data is rare, so I'd check a range of values, so . . .

DCount("Reminder", "tblReminders", "[Time] < #" & TimeValue(Now()) & "#")

TimeValue(Now()) returns a value to the second, and if you don't run your DCount() during the exact second that equality would occur, then you won't get equality at all, and your process will never, or rarely, run.

 

[Foe]

Paul, lol.

Foe, I also wouldn't run the timer every second, run a timer every 5 or 10 minutes and then check if you need to show a reminder. What are the criteria for that? And be careful checking for equality with date/time values. Equality in date/time data is rare, so I'd check a range of values, so . . .

DCount("Reminder", "tblReminders", "[Time] < #" & TimeValue(Now()) & "#")

TimeValue(Now()) returns a value to the second, and if you don't run your DCount() during the exact second that equality would occur, then you won't get equality at all, and your process will never, or rarely, run.

the good news - my DCount code looks like it was accurate
the bad news - I still don't know how to make use of it
in other news - it looks like I'm being steered towards using a RecordSet instead of a DCount. Or am I supposed to use them together?

The timer was setup to fire every second once I realized TimeValue(Now()) and whatever Time I was testing against had to match up to the second, as you've stated. It seemed like an awful lot of overhead to catch 11 events per day, but I didn't see another way to catch the timer. I am going to shift to a greater than - less than model once I sort out the details of pulling the relevant data out of the table.

As far as setting it to fire once every 5 or 10 minutes, that won't work for my use case. I can relax it to once per minute though. That should reduce the overhead by a multiple of 60

 

[Foe]

I need to get a handle on what I'm trying to do, before I can sort out how to do it...

step 1) search tbReminders looking for something like TimeValue(Now()) >= .RemindTime AND Day(Now()) <> Day(.LastRemindTime)
step 2) If there is a match capture the .ReminderID of that match
step 3) Create msgbox using .Reminder tied to that .ReminderID
step 4) Record time the msgbox was acknowledged in .LastRemindDate

this whole process fires off once per minute.

Is my logic sound?

 

[Foe]

Sorry to spam this thread, but it helps me work through it. (And it offers more chances for you guys to catch me before I go too far in the wrong direction)

For step 1, I decided to build a qry. The sql view of which is:

SELECT tblReminders.ReminderID, tblReminders.RemindTime, tblReminders.RemindMessage, tblReminders.LastRemindDate
FROM tblReminders
WHERE (((tblReminders.RemindTime)<=TimeValue(Now())) AND (Not (Day([LastRemindDate]))=Day(Now())));

 

[Foe]

Yay!

the code below appears to be working as intended!

Private Sub Form_Timer()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryReminders")
    With rs
        If .RecordCount <> 0 Then
            Do Until .EOF
            msgbox rs!RemindMessage, vbOKOnly, "Daily Routine Reminder"
            rs.Edit
            rs!LastRemindDate = Date
            rs.Update
            rs.MoveNext
            Loop
        End If
    End With
    rs.Close
End Sub

Did I miss anything? Is there anything there that doesn't need to be?
One thing I see is the rs!LastRemindDate = Date line seems like it keeps dropping the () after Date. I've added it more than once.

Thanks for the suggestions and thanks for letting me work through it in the thread.

 

[pbaldy]

Sometimes Access will drop the () after Date, so I wouldn't worry about it. You could build a string with all the messages in one if you wanted, so you present 1 message box instead of x.

 

[Foe]

Once the database is in use it will be open 99.9% of the time, making the odds of multiple messages firing off almost nonexistent.

Are there any pros and/or cons to having the query as a permanent object in the navigation pane vice creating it on the fly during recordset creation?