Problem with code

[Kay]

Hi
I have created a reminder for my project using a table with the fields,
alarm id, alarm time and message. Iv used the following code to check
each record to see if the alarm time matches the current time. I am
getting an error with the line
"Set rs = Me.RecordsetClone". The error is "Type Mismatch"

Dim rs As Recordset

Set rs = Me.RecordsetClone
With rs
Do Until .EOF
.MoveFirst
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")
End If
.MoveNext
Loop
End With
Set rs = Nothing

Has anyone any idea why this could be, your help is very much
appreciated.

 

[tina]

same question posted a minute later in ...access.formscoding NG with Subject
line "Code error".
please don't multi-post. see
http://home.att.net/~california.db/tips.html#aTip10 for more information.

 

[Dirk Goldgar]

Hi
I have created a reminder for my project using a table with the
fields, alarm id, alarm time and message. Iv used the following code
to check each record to see if the alarm time matches the current
time. I am getting an error with the line
"Set rs = Me.RecordsetClone". The error is "Type Mismatch"

Dim rs As Recordset

Set rs = Me.RecordsetClone
With rs
Do Until .EOF
.MoveFirst
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")
End If
.MoveNext
Loop
End With
Set rs = Nothing

Has anyone any idea why this could be, your help is very much
appreciated.

Both the DAO and the ADO object libraries define a Recordset object. By
default, Access 2000-2002 sets a reference to ADO and not to DAO. Even
if you later add a reference to DAO, it defaults to a lower priority
than the ADO reference, though you can move it up in the priority list.

Therefore, by default, a declaration such as "Dim rs As Recordset" is
going to be declaring an ADO recordset. However, the Recordset and
RecordsetClone of a form in an MDB file are DAO recordsets. Hence, you
get a type mismatch when you try to assign the form's (DAO)
RecordsetClone to the (ADO) recordset you've declared.

To correct this, be sure you've added a reference to the Microsoft DAO
3.6 Object Library (via the Tools -> References... dialog in the VB
Editor), and either remove the ADO (Microsoft ActiveX Data Objects)
reference -- if you're not going to use it -- or qualify your
declaration of DAO objects with the "DAO." prefix, like this:

Dim rs As DAO.Recordset

 

[Kay]

Hi
Thanks for the help, I just tried that but what it does now is it
freezes when running the code

Any ideas on how to overcome this

 

[Dirk Goldgar]

Hi
Thanks for the help, I just tried that but what it does now is it
freezes when running the code

Any ideas on how to overcome this

Sorry, I didn't look at the rest of the code. Now I see this:

With rs
Do Until .EOF
.MoveFirst

That's going to reset the recordset's position to the first record for
each iteration of the loop. So you'll never get out. Change it to
this:

With rs
If .RecordCount <> 0 Then
.MoveFirst
End If
Do Until .EOF

 

[Kay]

Thanks very much Dirk, that solves the continuous looping problem.

Ive got it working now but is there a way I can get the alarm message
to display for records that are not focussed. What I mean by that is at
the moment the msgbox is displayed only if the record is in view. If
record 1 and 2 have an alarm time at the same time and I am viewing
record 2, then the alarm msgbox is only displayed for record2 and not
1.

any help is much appreciated

 

[Dirk Goldgar]

Thanks very much Dirk, that solves the continuous looping problem.

Ive got it working now but is there a way I can get the alarm message
to display for records that are not focussed. What I mean by that is
at the moment the msgbox is displayed only if the record is in view.
If record 1 and 2 have an alarm time at the same time and I am
viewing record 2, then the alarm msgbox is only displayed for record2
and not 1.

any help is much appreciated

Ah. Back to look at the code again ... Oops. It's looping through the
RecordsetClone, but it's not actually using the information from that
recordset. Restructuring the quoted line so it won't break in the
newsreader, you have this:

If Me.[AlarmDate] = Date _
And Me.[AlarmTime] <= Time() _
And Me.[Reset] = False _
Then

All those references to "Me" are pointing to the current record on the
form, not the record in the recordset. Change it to this:

If rs![AlarmDate] = Date _
And rs![AlarmTime] <= Time() _
And rs![Reset] = False _
Then

I suspect that you also have to change this line:

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")

To this:

MsgBox ("A Job is Due") & vbCrLf & _
("ALARM ID = ") & rs!AlarmID & vbCrLf & _
("ALARM MESSAGE = ") & _
rs!AlarmMessage & vbCrLf & _
("Please tick the reset box on the alarm form " & _
"to reset the alarm")


I'm not 100% sure of that, though, because I don't really know much
about your form.

You may also have a problem with the precision of your time values. The
Time() function returns values down to the second. Are you storing
times in your alarm table down with that degree of precision? Maybe you
need to round off the seconds in the value returned by the Time()
function, before comparing it to [AlarmTime].

 

[Kay]

Sorry about this Dirk, but it is displaying the error "Method or Data
member not found" when using the rs.alarmdate etc
How do I declare the fields to be part of the recordset

 

[Dirk Goldgar]

Sorry about this Dirk, but it is displaying the error "Method or Data
member not found" when using the rs.alarmdate etc
How do I declare the fields to be part of the recordset

I didn't say "rs.AlarmDate"; I said "rs!AlarmDate". Note the
difference. On a form, you can usually use the bang (!) and the dot (.)
interchangeably when referring to fields. (Google for "bang vs. dot" if
you're curious about the difference.) But to refer to a field in a
recordset object, you must use the bang (!).

 

[Kay]

Thanks very much Dirk. Got it working

Thanks again and actually im gona check the difference between dang(!)
and the dot(.)
I thought they were the same.

Thanks again

 

[Dirk Goldgar]

Thanks very much Dirk. Got it working
Great!

Thanks again and actually im gona check the difference between dang(!)
and the dot(.)
I thought they were the same.

Here's my stock writeup on the difference:

--------- BANG (!) vs. DOT (.) ----------
It's not so much a question of one or the other being "proper syntax",
but that they mean different things that nevertheless almost always give
the same result. As I understand it, the bang (!) notation specifically
denotes that what follows is a member of a collection; in this case, a
member of the form object's default collection, the Controls collection.
The dot (.) notation denotes that what follows is a property or method
of the preceding object. That would logically make the bang notation
"proper" and the dot notation improper.

But wait. Wherever possible, Access makes the controls on a form and
the fields in its recordsource all available as properties of the form.
It also makes the fields of the recordsource available via the bang
notation. I'm not sure exactly how it does this; maybe if a name is
not found in the Controls collection it checks the Fields collection of
the form's recordset as a fallback position. So for most practical
purposes Me!ControlName and Me.ControlName evaluate to the same thing,
and the timing tests I've seen suggest that there is little to choose
between them as far as execution efficiency is concerned. I seem to
recall that there is a very slight difference, but I can't remember
which way the advantage lies, and it's not much. There's a coding-time
advantage, however, to using the dot notation, as it makes the
"intellisense" dropdown lists available. That's a strong argument for
using the dot notation, in my book.

But wait again! I said above that Access makes the controls available
as properties "wherever possible". There are cases where it can't do
that. Specifically, it can't do it when there is already a property of
the same name as the control in question. For example, if your form
"Form1" has a control or a field foolishly named "Name", currently
displaying the value "John Doe", then executing this statement in the
form's code module:

Debug.Print Me!Name, Me.Name

will print

John Doe Form1

in the Immediate Window. So you must be careful not to use any reserved
words or built-in properties as names for your controls, if you want to
use the dot notation to refer to them. But then, you should avoid doing
that anyway, as it tends in general to confuse poor Access.