Alert message automatically

[veasna]

Dear all members,

I am a new in Access 2007. Now, i got a problem with system alert message automatically.

Let's me explain my problem as below:

- I have a field name "Arrival Date"
- I want the system alert automatically when the value of "Arrival Date" is equal to "Current Date". I want the system alert this message every 7:00 AM.

Could you please give me any ideas to do this? Which event should i choose to dial with this?

Thanks,
Veasna.

 

[georgedwilkinson]

Store the data in a table in Access.

In a separate Access database application or Excel spreadsheet, write a subroutine in VBA to open the table using DAO or ADO, checking the value(s) in the table against the current date and time.

If you Access, you can create a form and set the timer event to call the subroutine when it is time. Or you can put it in the autoexec macro (or in Autpen in Excel) and call the application from Windows.

 

[veasna]

I am new in Access. I don't know much about DAO or ADO.

If you don't mind could you please tell me a bit on how to use DAO or ADO to check the value in the table against the current date and time.

Thanks,
Veasna

 

[georgedwilkinson]

Hmmmm. There may be an easier way then. How about using a linked table and SQL to do the calculation?

What do you know how to do and how much do you need us to tell you how to do?

 

[veasna]

I am sorry, i have no idea, because i have no experieces with Aceess. I am a fresh one.

If you have the time, please give me one example on how to do a linked table and SQL to deal with this.

Thanks,

Veasna

 

[georgedwilkinson]

Maybe I've overcomplicated this. In the properties for your form that contains the arrival date, on the Event tab, set the Timer Interval to 60000, and click on the ellipses next to "On Timer", select "Code Builder", fill out the rest of the subroutine like this:

Private Sub Form_Timer()
Static b As Boolean
    If Not b Then
        b = True
        If Format(Now, "hh:mm") = "07:00" Then
            If Format(Me.[Arrival Date], "MM/DD/YYYY") = Format(Date, "MM/DD/YYYY") Then
                MsgBox ("It matches")
            End If
        End If
        b = False
    End If
End Sub

Please note, this is just a starting place. This is by no means robust and you should do research to sharpen your skills to make it work for you.

Especially, make sure the date formats match. The above worked on my computer but may not work on everybody's (especially with a different format of date). You'll need to test that. I am frankly very weak on dates and times.

Also, there is absolutely NO guarantee that the timer will fire at exactly the minute mark with this method. You may need to adjust your approach accordingly, including possibly keeping track of the state of the "message" over time or reducing the timer interval a bit (risking the timer firing twice in 1 minute at 7 AM).

By the way, you should never name fields or other database objects with special characters or spaces.

 

[veasna]

Thanks for your time that you have spent with me. I still got a problem and a question to you. Now, the interval timer is work, but it's got an error with this line "If Format(Me.[Arrival Date], "MM/DD/YYYY") = Format(Date, "MM/DD/YYYY")".

One more problem that i forgot to tell you is [Arrival Date] is in the subform name "Customer subform". In my opinion, it's got an error because it could not found the [Arrival Date] object. Do you have any idea to deal with this? Please help me.

Thanks,
Veasna