wrong result from recordset

[GoalieGW]

The below code is not giving me the desired result. I am trying to
compare the same field on two seperate records. One being the current
record, the other being the day before. The code works to a degree,
but the "lngcompare" keeps resulting in 0. I am not getting the field
data from the recordset. Any thoughts?

Private Sub BOILER_1_GAS_METER_BeforeUpdate(Cancel As Integer)
Dim rs As Recordset
Dim lngcompare As Long
Dim strmsg As String
Dim msg, style, title, respose
msg = "Please enter a value greater " & vbCrLf & "than previous value
of " & lngcompare & "."
style = vbOKOnly
title = "Invalid Entry"
Set rs = Me.RecordsetClone
With rs
.MoveNext
lngcompare = ![boiler 1 gas meter]
End With
If Me.BOILER_1_GAS_METER < lngcompare Then
Response = MsgBox(msg, style, title)
If Response = vbOK Then
Cancel = True
End If
End If
rs.Close
Set re = Nothing
End Sub

[Thelma Lubkin]

GoalieGW <Go******@ptd.net> wrote:
: The below code is not giving me the desired result. I am trying to
: compare the same field on two seperate records. One being the current
: record, the other being the day before. The code works to a degree,
: but the "lngcompare" keeps resulting in 0. I am not getting the field
: data from the recordset. Any thoughts?

<snip>
: msg = "Please enter a value greater " & vbCrLf & "than previous value
: of " & lngcompare & "."

<snip>
: With rs
: .MoveNext
: lngcompare = ![boiler 1 gas meter]
: End With
: If Me.BOILER_1_GAS_METER < lngcompare Then
: Response = MsgBox(msg, style, title)
: If Response = vbOK Then
: Cancel = True
: End If
: End If
msg is defined before lngcompare has gotten any value from the
recordset: inside the loop it's pure text: put msg inside or
after the
With rs loop
so that the lngcompare value can be updated.
--thelma

[GoalieGW]

Thank you, that helped. Now I need the code to reset each time a new
record is selected. If I jump a few records, the code is not following
me.

[Thelma Lubkin]

GoalieGW <Go******@ptd.net> wrote:
: Thank you, that helped. Now I need the code to reset each time a new
: record is selected. If I jump a few records, the code is not following
: me.

How do you select a record? Don't you have a control on the
form that's bound to the value that is that record's
lngcompare? I don't understand why you need to work through
the recordset instead of using that.

...but please note that I'm not one of the experts
here: I still don't see lots of things...

--thelma

[GoalieGW]

Each record has a field "fldDate" that identifies it. The problem I am
having is that if I continue to hit OK and then tab, the recordset is
not reseting and starting over. It is continuing with the MoveNext
procedure. Do you know how I can use the "fldDate" to define the
recordset as current "fldDate"-1?

[Thelma Lubkin]

GoalieGW <Go******@ptd.net> wrote:
: Each record has a field "fldDate" that identifies it. The problem I am
: having is that if I continue to hit OK and then tab, the recordset is
: not reseting and starting over. It is continuing with the MoveNext
: procedure. Do you know how I can use the "fldDate" to define the
: recordset as current "fldDate"-1?

I *think* that you're working w/ two recordsets: the form's
recordset that is handling record navigation and the one that you open
in code. These are getting out of phase as you continue to change
records using the form's navigation buttons. The control that shows
the current record's fldDate is what you want to use as lngcompare.

I don't really understand what your recordset in code is for, but if
you need it, then you'll need to set its bookmark to the form's
recordset current bookmark.

Please try to open this discussion to wider participation,
because I could be completely wrong and both of us can learn from the
answers of people who do know.
--thelma