beginer with problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GoalieGW - 06 Dec 2005 13:12 GMT

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.

Reply to this Message

Keith W - 06 Dec 2005 13:51 GMT

> Hi,
> In work I have created a database for meter readings in access. I
[quoted text clipped - 8 lines]
> 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

Reply to this Message

GoalieGW - 06 Dec 2005 14:49 GMT

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)

Reply to this Message

Keith W - 06 Dec 2005 15:00 GMT

> Can I use something like this?
>
[quoted text clipped - 5 lines]
> 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

Reply to this Message

GoalieGW - 06 Dec 2005 17:12 GMT

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

Reply to this Message

jbguernsey@aol.com - 07 Dec 2005 08:52 GMT

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.

> Here is what I have so far. I am getting an error on the lngCompare
> line. Says object not found.
[quoted text clipped - 21 lines]
> Set rs = Nothing
> End Sub

Reply to this Message

Keith W - 07 Dec 2005 08:54 GMT

> Here is what I have so far. I am getting an error on the lngCompare
> line. Says object not found.
[quoted text clipped - 21 lines]
> 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.

Reply to this Message

GoalieGW - 07 Dec 2005 15:39 GMT

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

Reply to this Message

Keith W - 07 Dec 2005 16:05 GMT

> 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
[quoted text clipped - 19 lines]
> Set rs = Nothing
> End Sub

Reply to this Message

GoalieGW - 07 Dec 2005 17:07 GMT

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

Reply to this Message

GoalieGW - 07 Dec 2005 17:31 GMT

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

Reply to this Message

Allen Browne - 06 Dec 2005 13:55 GMT

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.

Signature

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.

Reply to this Message

Rick Brandt - 06 Dec 2005 14:02 GMT

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

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message