Data validation on a form

  • Thread starter Thread starter news
  • Start date Start date
N

news

Hi,

I have a table (tblPumpData) which contains the fields PumpDataID
(Primary Key), SiteNameID (which is linked to SiteNameID in
tblSiteName), ReadDate, and Pump1.

I have produced a form which asks for the SiteName (via a combo box),
the ReadDate and Pump1.

I would like to produce the following validation rules if possible:

(1) Duplicate ReadDat ,& Pump1 can not be entered for a particular
SiteNameID.

(2) For a particular SiteNameID, the value entered in Pump1 can not be
less than the previously stored Pump1 value for that same SiteNameID.

For the first problem I have tried using the following code in Before
Update, but it does not seem to work:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim sWhere As String
Dim sVal

sWhere = "SiteNameID=" & tboSiteNameID.Value & " AND ReadDate = #"
& tboReadDate.Value & "# AND Pump1 = " & tboPump1.Value

sVal = DLookup("PumpDataID", "tblPumpData", sWhere)

If Nz(sVal, "") = "" Then
MsgBox "Didnt find anything with the criteria " & vbCrLf &
vbCrLf & sWhere
Else
MsgBox "Found a record using the criteria " & vbCrLf &
vbCrLf & sWhere & vbCrLf & vbCrLf & "Found PumpDataID >" & sVal & "<"
Cancel = True
End If
End Sub

Any ideas?

Not sure how to go about the second problem.

cheers

Michael
 
Hi Michael

The best way to achieve (1) is to add a unique index to your table based on
the three fields. That way, there can never be a duplicate record entered.
You can trap the duplicate index error in your Form_Error event.

For (2), the highest Pump1 value entered so far for a given site is:
Nz(DMax("Pump1", "tblPumpData", "SiteNameID=" & tboSiteNameID), 0)

(The Nz part ensures that 0 is returned if there are no entries for the
given site).

Just compare this value with the newly entered one.
 
Hi Michael

The best way to achieve (1) is to add a unique index to your table based on
the three fields. That way, there can never be a duplicate record entered.
You can trap the duplicate index error in your Form_Error event.

For (2), the highest Pump1 value entered so far for a given site is:
Nz(DMax("Pump1", "tblPumpData", "SiteNameID=" & tboSiteNameID), 0)

(The Nz part ensures that 0 is returned if there are no entries for the
given site).

Just compare this value with the newly entered one.
Click to expand...


Thanks, works a treat!
 
Back
Top