Automatic Reminder

[MarieLine77]

Access 2007 – Setting an automatic reminder 15 days prior to (see below)

I have an “UPDATE DUE DATE” field in one table and a “NEXT SUPERVISION DUE” field in another table:

tblTrainingRecord
UpdateDueDate

tblSupervisions
NextSupervisionDue

I have a Switchboard that is loaded when the database is started and I want this the reminder to open on the switchboard.

I have selected EVENT, then ON LOAD on the switchboard property sheet and when I click on the 3 dots another window opens up asking me to select from:
Macro Builder
Expression Builder
Code Builder

Can someone tell me which option to select and what is the exact instruction that I need to type in, using my exact fields and tables name?

If there is a better way of achieving this on the switchboard please feel free to let me know.

Thank you for your help.

 

[plog]

I'm not entirely sure what you need done, but I'd create a query to identify the records that you want the user to be made aware of and then at startup launch that query if there are records in it.

 

[MarieLine77]

I'm not entirely sure what you need done

Could you please let me know what don't you understand?

 

[plog]

All of it. But we can start here:

...and I want this the reminder to open on the switchboard

What is 'this reminder'? Where does it exist?

At times you make it sound like you've done work on it and just need to insert code somewhere so that this ('this reminder') will pop up. At others it sounds like this (the entire concept) is a vague idea you have.

 

[MarieLine77]

All of it. But we can start here:



What is 'this reminder'? Where does it exist?

At times you make it sound like you've done work on it and just need to insert code somewhere so that this ('this reminder') will pop up. At others it sounds like this (the entire concept) is a vague idea you have.

The reminder does not exist yet. I clearly said that I wanted to set an auto reminder hence the title of my post and the first line of my post:
Access 2007 – Setting an automatic reminder 15 days prior to (see below)

I have an “UPDATE DUE DATE” field in one table and a “NEXT SUPERVISION DUE” field in another table:

tblTrainingRecord
UpdateDueDate

tblSupervisions
NextSupervisionDue

Your first assumption is correct:
At times you make it sound like you've done work on it and just need to insert code somewhere so that this ('this reminder') will pop up.

I am a beginner and have only been using Access 2007 in the last 4 weeks. I know this is going to be a long learning curb for me...

Thank you.

 

[MarieLine77]

I would appreciate it if someone can help me please.
If you have any questions please do not hesitate to ask me as I know many of you are finding hard to understand my posts.

See attached a print screen of my database (relationship screen).
I really am working on a database. I do not understand why some of you do not believe me.

Thank you.

 

[plog]

I'm getting a better idea, still not all the way there, though.

What does this reminder notice you envision look like? What data does it provide you? Provide a specific example with sample data if applicable.

 

[Pat Hartman]

Choose the Code Builder option because we're going to write some VBA Access will open the code window and create the header and footer for the new procedure.

1. Write a query that selects the reminders using the QBE and save it. Name it qSupervisionReminder It should look something like:

Select * From tblSupervisions Where DateAdd("d",-15,NextSupervisionDue) <= Date();

This will select all the records from the supervisions table where the next supervision due date - 15 days is less than or equal to today's date.
2. Create a form that displays the querydef we just created. Name the form frmSupervisionReminder
3. In the code procedure, we're going to open the new form if the query returned any records using code like:

If Dcount("*","qSupervisionReminder") > 0 Then
    DoCmd.Open "frmSupervisionReminder"
End If

 

[MarieLine77]

Choose the Code Builder option because we're going to write some VBA Access will open the code window and create the header and footer for the new procedure.

1. Write a query that selects the reminders using the QBE and save it. Name it qSupervisionReminder It should look something like:

Select * From tblSupervisions Where DateAdd("d",-15,NextSupervisionDue) <= Date();

This will select all the records from the supervisions table where the next supervision due date - 15 days is less than or equal to today's date.

I don't think I understood that.
Please see the attached and it is not working.
Could you be more specific and give step by step instruction.
If it is easier please kindly email me spollak77@hotmail.com

Many thanks.

 

[Pat Hartman]

Unfortunately, you can't just write SQL in a code module.
Sorry, I wasn't clear in my steps. Writing the code is the last thing you'll do not the first. First you need to create the query and save it. Then you need to create the Form and save it. And finally, you get to write code

I've included a picture of the query designer. Go to the Create Ribbon and choose the Query Design icon. If you're using an earlier version of Access, you can switch to the query tab on the Database Container and press the New button.

 

[MarieLine77]

Unfortunately, you can't just write SQL in a code module.
Sorry, I wasn't clear in my steps. Writing the code is the last thing you'll do not the first. First you need to create the query and save it. Then you need to create the Form and save it. And finally, you get to write code

I've included a picture of the query designer. Go to the Create Ribbon and choose the Query Design icon. If you're using an earlier version of Access, you can switch to the query tab on the Database Container and press the New button.

Thank you for the above

How do I delete the code that I created (draft5 attached)?
I have created a query qSupervisions (draft6 attached)
I have created a form frmSupervisions (draft7 attached)

From what screen do I create the Code Builder to insert:
Select * From tblSupervisions Where DateAdd("d",-15,NextSupervisionDue) <= Date();

a) The Switchboard?
b) The Query?
c) The Form?

Thank again for your patience.

 

[MarieLine77]

..................bump

 

[Beetle]

How do I delete the code that I created (draft5 attached)?

Highlight that line and delete it. That line from Pat was not a code suggestion, but rather a suggested SQL statement for your query.

I have created a query qSupervisions (draft6 attached)

Yes, but you still need some criteria to limit the records that are returned by this query. In the criteria row of the NextSupervisionDue column put something like;

<=DateAdd("d", 15, Date())

Keep in mind that you may need to modify this criteria slightly if it doesn't return the records you want.

I have created a form frmSupervisions (draft7 attached)

The form should use the query as its Record Source (if that's not already the case). Also, if this form is more for display, you may want to consider making it a Continuous, rather than a Single form.

From what screen do I create the Code Builder to insert:

The suggestion was to use some event on your swithboard form, (like the Open event of the form or the Click event of a command button on the form), but the code Pat suggested to place there is;

If Dcount("*","qSupervisionReminder") > 0 Then
    DoCmd.Open "frmSupervisionReminder"
End If

What this will do is check to see if there are any records in the query. If so, it will open the new form you created. If not, nothing will happen.

 

[MarieLine77]

Highlight that line and delete it. That line from Pat was not a code suggestion, but rather a suggested SQL statement for your query.



Yes, but you still need some criteria to limit the records that are returned by this query. In the criteria row of the NextSupervisionDue column put something like;

<=DateAdd("d", 15, Date())

Keep in mind that you may need to modify this criteria slightly if it doesn't return the records you want.



The form should use the query as its Record Source (if that's not already the case). Also, if this form is more for display, you may want to consider making it a Continuous, rather than a Single form.



The suggestion was to use some event on your swithboard form, (like the Open event of the form or the Click event of a command button on the form), but the code Pat suggested to place there is;

If Dcount("*","qSupervisionReminder") > 0 Then
    DoCmd.Open "frmSupervisionReminder"
End If

What this will do is check to see if there are any records in the query. If so, it will open the new form you created. If not, nothing will happen.

Hello Beetle and thank you for your kind help.

I think that I have managed to do everything (see attachments 8, 9 and 10).

However, I seem to be having a problem with the last instruction (on the Switchboard, Event, On Load, Code Builder) - See attachment 11.

When I close, save and re open the Database nothing comes up on the switchboard screen i.e. frmqSupervisions should open as there are 2 appt. within 15 days from the current date.

I also tried to delete the first line and the last line of the code but it made no difference (Private Sub Form_Load () and End Sub).

Any further assistance that you can provide to me would be most appreciated.

Thank you.

 

[MarieLine77]

Another question if I may.

Do I have to redo everything that I did for SupervisionsDueDate (above) for all the tables/fields that I require a reminder for?

i.e. I need another reminder for the tblTrainingRecord (field UpdateDueDate).

 

[Beetle]

Sorry, I didn't notice before, but change this line;

DoCmd.Open "frmSupervisionReminder"

to;

DoCmd.OpenForm "frmSupervisionReminder"

Do I have to redo everything that I did for SupervisionsDueDate (above) for all the tables/fields that I require a reminder for?

This or something similar. There are almost always multiple ways to approach a problem like this. For example, if you need several reminder "lists" you may want to use List Boxes directly on your switchboard and use the queries you create (like qSupervisions) as the Row Sources for the list boxes. Then you could use the Double Click event of each list box to open a form for editing the record if necessary. Just an idea.

 

[MarieLine77]

Sorry, I didn't notice before, but change this line;

DoCmd.Open "frmSupervisionReminder"

to;

DoCmd.OpenForm "frmSupervisionReminder"

I have tried this both of the following and nothing works:


Private Sub Form_Load()
If DCount("*", "qSupervisions") > 0 Then
DoCmd.OpenForm "frmqSupervisions"
End If
End Sub

AND

Private Sub Form_Load()
If DCount("*", "qSupervisionsReminder") > 0 Then
DoCmd.OpenForm "frmqSupervisionsReminder"
End If
End Sub

 

[Beetle]

If you want to upload a copy of your app with some dummy data I can take a look at it.

 

[MarieLine77]

If you want to upload a copy of your app with some dummy data I can take a look at it.

Could I email it to you?

 

[Beetle]

OK, I took a look at your app. Right away I had to correct the spelling of your query and form names in the code form this;

Private Sub Form_Load()
If DCount("*", "qSupervisionsReminder") > 0 Then
    DoCmd.OpenForm "frmqSupervisionsReminder"
End If
End Sub

to this;

Private Sub Form_Load()
If DCount("*", "qSupervisions") > 0 Then
    DoCmd.OpenForm "frmqSupervisions"
End If
End Sub

I realize that this is probably just from you trying to get the code to work, but just make sure it's corrected on your end so that it is referencing the actual query and form names.

Having said that, you should have been getting an error message. You didn't mention anything about that, so my guess is that your code is not running at all because your database is not in a trusted location. In A2007 and later, code will not run if the application is not in a trusted location. You can add your database folder to the list of trusted locations, in Access 2010, by going to the File tab / Options / Trust Center / Trust Center Settings / Trusted Locations. In Access 2007 it will be something similar but your will get there through the "Office" button in the upper left instead of the File tab.

Once you have that solved the code should work provided that you corrected the query and form names. Keep in mind that since you are using tabbed forms, the frmqSupervisions form will not pop up right in front of you. It will open on another tab and you may need to click the tab to see it. If you want it to pop up in front of the user, you can open it in Dialog mode by modifying your code as follows;

Private Sub Form_Load()
If DCount("*", "qSupervisions") > 0 Then
    DoCmd.OpenForm "frmqSupervisions", , , , , acDialog
End If
End Sub