Question on building a reminder application

[Ayiramala]

Hi,
As part of learning Access, I am trying to build a db to remind us of upcoming events.
This is what I did:
There is a table 'tblReminder' where I have these fields: EventDate, EventDescription, RemindDate, Dismiss (a check box). When you enter the date and description of an event, you also put a date in the RemindDate field. Later, when you view it on a form, the records should show up only from the RemindDate onwards.


(Let's say that a wedding comes up on 25/04/2015. I put 20/04/2015 as the RemindDate so that I get time to buy a gift.This record should then be visible from 20/04/2015 onwards). I hope that is clear.
How can I do this? A bit of code in the OnLoad event, perhaps?
I'll greatly appreciate it if anyone can help me with this.
Thanks.
PS: What I mentioned above is just my idea. If there is a better way to do this please tell me about that. I am using Access 2010.

[ranman256]

Event not needed.
The main form that opens is connected to a query that pulls tReminders for Date().
when today is 20/4/15 ,then THAT record shows.

[Ayiramala]

Event not needed.
The main form that opens is connected to a query that pulls tReminders for Date().
when today is 20/4/15 ,then THAT record shows.

Hi,
Thanks for the reply.
But what I want is that, the records should show not just on that day, but EVERYDAY, from a date I set.

[rpeare]

So let's say this is your table:

tblEvents
Event_ID  Event_Date  Remind_Date  Dismiss
1         1/1/2015    12/15/2014   -1 (checkboxes are stored as -1 for 'yes' or checked, 0 as 'no' or unchecked)
2         2/1/2015    01/15/2014   
3         6/1/2015    05/15/2014

The query to pull the data you want (only record 2)

would be

SELECT * FROM tblEvents WHERE Remind_Date <= Date() AND Dismiss <> -1

Then you just base your form (assuming you're using a bound form) on this query, instead of the table.

[Ayiramala]

That worked! Thank you very much.

May I ask just one more question?
Imagine there are a lot of events on the form. I want to delete some of them. There is the check box on the form. I want to select some of them, and press a button to delete them. I added a command button on the form, with the following code attached to the OnClick event:
Private Sub cmdDelete_Click()
Dim Dismiss As CheckBox
Dim LResponse As Integer
LResponse = MsgBox("This will delete the record permanently. Do you wish to continue?", vbYesNo)
If LResponse = vbYes Then
If Me.Dismiss = True Then
Me.Recordset.Delete
End If
End If
End Sub

Is the code alright? If I select any one record it works perfectly, but if I select more than one only one of them is deleted. What should I do?
Thanks.

[rpeare]

Personally I do not delete data from my databases if I can possibly avoid it. In your case you have two possible outcomes.

The event date has passed or the event is cancelled.

The first can be handled by adding another criteria to your query on the EVENT_DATE

>= Date()

In other words the event date is greater than or equal to today's date, and the remind date is less than or equal to today's date

The second part of removing a cancelled event can be handled by adding another text or yes/no field to your table CANCELLED

Then, if you have a cancelled event just check this box off and in the criteria of your query driving the form add the criteria to the CANCELLED field of

<>-1

[Ayiramala]

Sorry for responding late. Got a little busy with a marriage..

Thanks for all the suggestions. Even now, I can't really understand it when you said:

Personally I do not delete data from my databases if I can possibly avoid it.

What is wrong with deleting records? What is the point of keeping records that we know we will never need again? Don't you think it would be a waste of space?

[rpeare]

No, you never know when an old record might be useful, for instance if you have to reproduce an invoice or produce a list of events for a previous year, etc. data can always be archived at a later date but your database would have to be extremely complex and store a pretty vast amount of data before you would have to consider archiving data. In short, no, I don't think it's a waste of space. Quite the opposite, I find it space well spent to keep historical information.

[Ayiramala]

Thanks for pointing it out. Much appreciated.