By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,589 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,589 IT Pros & Developers. It's quick & easy.

beginer with problem

P: n/a
Hi,
In work I have created a database for meter readings in access. I
am not a programmer, but would like to try something. Please respond
with detail as I will be lost otherwise.
What I would like to do is eliminate the possibility of someone
entering in bad data. To do this, I want to compare the same field on
two different records to see if the result is possitive or negative.
If negative, a message box.
Each record has a date 'fldDate' and then the fields for the
readings. So what I was thinking was to compare the last record in a
recordset to the current field input. Is this right? If so, what are
the steps, I am lost.

Dec 6 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
"GoalieGW" <Go******@ptd.net> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Hi,
In work I have created a database for meter readings in access. I
am not a programmer, but would like to try something. Please respond
with detail as I will be lost otherwise.
What I would like to do is eliminate the possibility of someone
entering in bad data. To do this, I want to compare the same field on
two different records to see if the result is possitive or negative.
If negative, a message box.
Each record has a date 'fldDate' and then the fields for the
readings. So what I was thinking was to compare the last record in a
recordset to the current field input. Is this right? If so, what are
the steps, I am lost.


You'd need to open a recordset in code and move to the next-to-last record.
Try something like this in the form's Before Update event:

Dim rs As DAO.Recordset, lngCompare As Long
Set rs = Me.RecordsetClone
With rs
.MoveLast
.MovePrevious
lngCompare = ![MyField]
End With

If Me.txtMyTextBox - lngCompare < 0 Then
'Your message box code
Cancel = True
End If

rs.Close
Set rs = Nothing

This is untested air code off the top of my head but it should get you going
in the right direction. It assumes that the record you're editing has been
saved. Remember to replace MyField and txtMyTextBox with actual names from
you app.

HTH - Keith.
www.keithwilby.com
Dec 6 '05 #2

P: n/a
Use the BeforeUpdate event of the form to check the highest reading so far
for this meter on a previous date. This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If Not (IsNull(Me.[MeterID]) Or IsNull(Me.[fldDate]) Or
IsNull(Me.[Reading]) Then
strWhere = "([MeterID] = " & Me.MeterID & ") AND ([fldDate] < " & _
Format(Me.[fldDate], "\#mm\/dd\/yyyy\#") & ")"
varResult = DMax("Reading", "Table1", strWhere)
If Me.Reading < varResult Then
strMsg = "Previous reading of " & varResult & "." & vbCrLf & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2) = vbNo Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

Assumptions:
- MeterID is a Number field that identifies the meter.
- fldDate is a Date/Time field.
- These fields are found in Table1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"GoalieGW" <Go******@ptd.net> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Hi,
In work I have created a database for meter readings in access. I
am not a programmer, but would like to try something. Please respond
with detail as I will be lost otherwise.
What I would like to do is eliminate the possibility of someone
entering in bad data. To do this, I want to compare the same field on
two different records to see if the result is possitive or negative.
If negative, a message box.
Each record has a date 'fldDate' and then the fields for the
readings. So what I was thinking was to compare the last record in a
recordset to the current field input. Is this right? If so, what are
the steps, I am lost.

Dec 6 '05 #3

P: n/a
GoalieGW wrote:
Hi,
In work I have created a database for meter readings in access. I
am not a programmer, but would like to try something. Please respond
with detail as I will be lost otherwise.
What I would like to do is eliminate the possibility of someone
entering in bad data. To do this, I want to compare the same field on
two different records to see if the result is possitive or negative.
If negative, a message box.
Each record has a date 'fldDate' and then the fields for the
readings. So what I was thinking was to compare the last record in a
recordset to the current field input. Is this right? If so, what are
the steps, I am lost.


Create a query like...

SELECT TOP 1 *
FROM TableName
ORDER BY fldDate DESC

That query will return the one row from the table with the most recent date.
You can then retrieve the required value(s) from this query with either a
Recordset or with DLookup(). The Recordset would be better if you need to
retrieve more than just a couple fields, but will require more code to set
up. The DLookup() will be simpler but less efficient if you are retreving
more than one field.

Actually if you create a Recordset then it could be built against a SQL
Statement like the one above eliminating the need to create a separate query
at all. Creating the query separately does facilitate the use of DLookup()
though by eliminating the need for it to specify the row (which would be
complicated and ineffiecient to do entirely within the DLookup() WHERE
argument).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Dec 6 '05 #4

P: n/a
Can I use something like this?

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim rs1 As recorset 'from current record
Set db = CurrentDb()
Set rs = db.tbl_DRS_READINGS(BOILER_1_GAS_METER, dbOpenSnapshot)
Set rs1 = db.OpenRecordset(BBOILER_1_GAS_METER, dbOpenSnapshot)

Dec 6 '05 #5

P: n/a
"GoalieGW" <Go******@ptd.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Can I use something like this?

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim rs1 As recorset 'from current record
Set db = CurrentDb()
Set rs = db.tbl_DRS_READINGS(BOILER_1_GAS_METER, dbOpenSnapshot)
Set rs1 = db.OpenRecordset(BBOILER_1_GAS_METER, dbOpenSnapshot)

There's no need to open a recordset for the current record since you can get
all the data you need from the form's controls. There's no need to define a
database object for the form's recordsource because that's what
RecordsetClone is.

Dim rs As DAO.Recordset, lngCompare As Long
Set rs = Me.RecordsetClone ** This sets rs to the same recordset as your
form **
With rs
.MoveLast
.MovePrevious ** This moves the pointer to the second to last record
(you may need to modify this if it's not quite what you want) **
lngCompare = ![MyField] ** This assigns the content of the previous
record's field to a variable **
End With

If Me.txtMyTextBox - lngCompare < 0 Then ** This is the comparison of the
two values **
'Your message box code
Cancel = True
End If

rs.Close
Set rs = Nothing
Dec 6 '05 #6

P: n/a
Here is what I have so far. I am getting an error on the lngCompare
line. Says object not found.

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim rs As DAO.Recordset, lngCompare As Long
Dim strmsg As String
Set rs = Me.RecordsetClone
With rs
.MoveLast
.MovePrevious
lngCompare = ![BOILER_1_GAS_METER]
End With

strmsg = "Please enter a value greater than yesterdays value of" &
lngCompare & "."

If Me.BOILER_1_GAS_METER - lngCompare < 0 Then
If MsgBox(strmsg, vbOKOnly, [Invalid entry]) = vbOK Then
Cancel = True
End If
End If

rs.Close
Set rs = Nothing
End Sub

Dec 6 '05 #7

P: n/a
"GoalieGW" <Go******@ptd.net> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Here is what I have so far. I am getting an error on the lngCompare
line. Says object not found.

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim rs As DAO.Recordset, lngCompare As Long
Dim strmsg As String
Set rs = Me.RecordsetClone
With rs
.MoveLast
.MovePrevious
lngCompare = ![BOILER_1_GAS_METER]
End With

strmsg = "Please enter a value greater than yesterdays value of" &
lngCompare & "."

If Me.BOILER_1_GAS_METER - lngCompare < 0 Then
If MsgBox(strmsg, vbOKOnly, [Invalid entry]) = vbOK Then
Cancel = True
End If
End If

rs.Close
Set rs = Nothing
End Sub

Hmm, not sure, looks OK to me. It might be that you're using ADO (I assumed
DAO). Look under Tools/References and see if DAO is checked.

Keith.
Dec 7 '05 #8

P: n/a
Dear Goalie

Apologies for jumping in here. I am not going to talk technical stuff
....

I have had much to do with meter readings over the years (at
considerable cost to my sanity!) and have a couple of hard-earned
snippets to pass on ...

A later reading is invalid if it is lower than an earlier reading, yes?

Alas, not necessarily! There are a couple of possibilities you'll have
to look out for (and they WILL crop up, I promise you).

Firstly, the earlier reading could be incorrect (even tho' it was more
that its previous reading).
Secondly, the meter concerned might have been replaced with a new meter
(cos the old one broke, say). In this case you cannot assume that the
new meter starts at zero so you have to set up a routine that allows
you to store the opening reading ...
Thirdly, if there has been a new meter you have to account for the
final reading on the old one before starting to consider the reading on
the new meter.

How do you know if there's a new meter? It's not impossible (and
therefore it's safe to assume that this will occur at the worst
possible time) that the new meter will display a reading that is
theoretically possible - but incorrect! (It depends on what it is
showing when it is introduced - it may not be brand-new but second-hand
and therefore showing a reading which just might be in the 'correct
range'.)

DON'T BELIEVE WHAT YOU ARE TOLD WHEN PEOPLE SAY THIS CANNOT HAPPEN!
DON'T BELIEVE WHAT YOU ARE TOLD WHEN PEOPLE SAY THIS CANNOT HAPPEN!
DON'T BELIEVE WHAT YOU ARE TOLD WHEN PEOPLE SAY THIS CANNOT HAPPEN!

Sorry for the repetition ... old scars twinged there.

As you can see, there's plenty of scope for lunacy to break out. In my
case (originally) I was assured that there would never be a change of
meter (WRONG!) and then I was assured that the new meter would start at
zero (WRONG!). I was assured that the readings presented to me would
be accurate (WRONG) and there wouldn't be any need to make subsequent
changes once data had been entered. I was very niaive and believed all
this rubbish and it cost me a large amount of time and grief to sort
out the problems that (inevitable) arose.

GoalieGW wrote:
Here is what I have so far. I am getting an error on the lngCompare
line. Says object not found.

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim rs As DAO.Recordset, lngCompare As Long
Dim strmsg As String
Set rs = Me.RecordsetClone
With rs
.MoveLast
.MovePrevious
lngCompare = ![BOILER_1_GAS_METER]
End With

strmsg = "Please enter a value greater than yesterdays value of" &
lngCompare & "."

If Me.BOILER_1_GAS_METER - lngCompare < 0 Then
If MsgBox(strmsg, vbOKOnly, [Invalid entry]) = vbOK Then
Cancel = True
End If
End If

rs.Close
Set rs = Nothing
End Sub


Dec 7 '05 #9

P: n/a
Here is what I have. The question is this, which number is from the
open form? Is it the IngCompare or the Me.BOILER_1_GAS_METER? Also,
if I were to go back and edit another record, this would compare that
open record to the latest date, not the previous date. Is there a
better way to do this?

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim rs As DAO.Recordset
Dim IngCompare As Long
Dim strmsg As String
Set rs = Me.RecordsetClone
With rs
.MoveFirst
.MoveNext
IngCompare = Forms![frm_DRS_READINGS]![BOILER 1 GAS METER]
End With

strmsg = "Please enter a value greater than yesterdays value of" &
IngCompare & "."

If Me.BOILER_1_GAS_METER > IngCompare Then
If MsgBox(strmsg, vbOKOnly, [Invalid entry]) = vbOK Then
Cancel = True
End If
End If

rs.Close
Set rs = Nothing
End Sub

Dec 7 '05 #10

P: n/a
"GoalieGW" <Go******@ptd.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Here is what I have. The question is this, which number is from the
open form? Is it the IngCompare or the Me.BOILER_1_GAS_METER?
"Me" is the keyword for the current object, so Me.BOILER_1_GAS_METER is from
the open form.
Also,
if I were to go back and edit another record, this would compare that
open record to the latest date, not the previous date. Is there a
better way to do this?
This code will compare the currently edited record with the second-to-last
record in the recordset, so it won't work for editing a record in, say, the
middle or beginning of the recordset. To do it like that you'd need add
some code that will look for the current record and go back one. The
"bookmark" property might be the thing to use, pehaps someone could jump in
if they have an example to hand ...

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim rs As DAO.Recordset
Dim IngCompare As Long
Dim strmsg As String
Set rs = Me.RecordsetClone
With rs
.MoveFirst
.MoveNext
IngCompare = Forms![frm_DRS_READINGS]![BOILER 1 GAS METER]
End With

strmsg = "Please enter a value greater than yesterdays value of" &
IngCompare & "."

If Me.BOILER_1_GAS_METER > IngCompare Then
If MsgBox(strmsg, vbOKOnly, [Invalid entry]) = vbOK Then
Cancel = True
End If
End If

rs.Close
Set rs = Nothing
End Sub

Dec 7 '05 #11

P: n/a
I am getting an error message on this line set. It says can't find the
field "I" reffered to in expression. The highlighted line is 'If
MsgBox(strmsg, vbOKOnly, [Invalid entry]) = vbOK Then'

If Me.BOILER_1_GAS_METER < lngCompare Then
If MsgBox(strmsg, vbOKOnly, [Invalid entry]) = vbOK Then
Cancel = True
End If

Dec 7 '05 #12

P: n/a
I now have this. This seems to work except for some reason the
recordset for comparrison is incorrect. Is there a way to set the
recordset to the record corrisponding to the currently open forms date
-1?

Private Sub BOILER_1_GAS_METER_LostFocus()
Dim rs As DAO.Recordset
Dim lngCompare As Long
Dim strmsg As String
Dim Msg, Style, Title, Response
Set rs = Me.RecordsetClone
With rs
.MoveNext
lngCompare = ![BOILER 1 GAS METER]
End With

Msg = "Please enter a value greater " & vbCrLf & "than yesterdays value
of " & lngCompare & "." ' Define message.
Style = vbOKOnly ' Define buttons.
Title = "Invalid Entry" ' Define title.

If Me.BOILER_1_GAS_METER < lngCompare Then
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose Yes.
Cancel = True
End If
End If

rs.Close
Set rs = Nothing
End Sub

Dec 7 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.