Alarm message

[Kay]

Hi
I have created a reminder in my database. I have created a reminder
table with the fields alarm id, alarm date, alarm time and alarm
message. I use a form to enter the alarm details into my table. I have
used the following code in my form timer event:

If Me.AlarmDate = Date And Me.AlarmTime <= Time() And Me.Reset = False
Then
MsgBox ("A Job is Due") & vbCrLf & _
("ALARM ID = ") & AlarmID & vbCrLf & ("ALARM MESSAGE = ") &
AlarmMessage & vbCrLf & ("Please tick the reset box on the alarm form
to reset the alarm")

If the alarm time is passed, the alarm message is shown after every
update so I have used a rest checkbox to stop an alarm that already has
gone off. The problem I have is that a message is only shown for an
alarm that the focus is set on. By that I mean If alarm id 123 is
displayed in the form, then the alarm message will only be shown for
that alarm when the time has come. What I need it to do is to shown
the message for any alarm not just the one that is being viewed.
I just hope Im clear because I think im beginning to confuse myself
now. Any help will be much appreciated

 

[Arvin Meyer [MVP]]

You might try building a recordset and walking through it like (aircode):

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set db = CurrentDB
Set rst = db.OpenRecordset("Select * tblAlarms Where Reset = False",
dbOpenSnapshot)

rst MoveLast
rst.MoveFirst
For i = 1 To rst.RecordCount
' Your code here
Next i
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Kay]

Hi
I tried this code but it is not recognising the variables
Dim db As DAO.Database
Dim rst As DAO.Recordset

The message is: User-Defined type not defined

Any ideas

 

[Arvin Meyer [MVP]]

Yes, you need to set a reference to DAO in your database. From any code
window, go to Tools ... References and set a reference to Microsoft DAO 3.6
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Kay]

Hi Arvin

I compiled the code now and it shows an error at the end of the for
loop by....... Next i
The error is Compile Error - "Next without For"

Ive double checked this code against other for loop code and it seems
to be fine so I cannot figure out why it is showing this error.

Please could you help

 

[Arvin Meyer [MVP]]

I'll try. Are you sure you have the line: For i = 1 to rst.RecordsetCount

If so, please post your entire code and I'll try to find out what's wrong.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Kay]

Hi Arvin

I have got the for i = 1 to rst.RecordsetCount statement. Here is my
entire code:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * AlarmTable Where Reset = False",
dbOpenSnapshot)
rst.MoveLast
rst.MoveFirst

For i = 1 To rst.RecordCount
If Me.[AlarmDate] = Date And Me.[AlarmTime] <= Time() And Me.[Reset] =
False Then
MsgBox ("A Job is Due") & vbCrLf & _
("ALARM ID = ") & AlarmID & vbCrLf & ("ALARM MESSAGE = ") &
AlarmMessage & vbCrLf & ("Please tick the reset box on the alarm form
to reset the alarm")

Next i

Your help is much appreciated Arvin

 

[Arvin Meyer [MVP]]

There's no End If before the Next i. Your error message should be Block If
Without End If.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access