wrong result from recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GoalieGW - 12 Dec 2005 01:22 GMT

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

Reply to this Message

Thelma Lubkin - 12 Dec 2005 03:02 GMT

: 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
[quoted text clipped - 6 lines]
: 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

Reply to this Message

GoalieGW - 12 Dec 2005 15:09 GMT

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.

Reply to this Message

Thelma Lubkin - 12 Dec 2005 15:21 GMT

: 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

Reply to this Message

GoalieGW - 12 Dec 2005 15:24 GMT

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?

Reply to this Message

Thelma Lubkin - 12 Dec 2005 15:41 GMT

: 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

Reply to this Message