Validation Rules

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Merry Xmas to all.

I need to do some "Advanced Validation" on MS Access. I believe this is a
common problem, so someone should know a good trick

Person Project Dic Ene Feb Mar
OSORIO L Pro 1 100% 100% 100% 50%
OSORIO L., Pro 2 40%
Editing

Editong the record
OSORIO L., Pro3 30%
This guy has only 10% Availability in march

The idea is simple, when you assigned someone 30% of his month to a project
the validation rule should checked the guy has 30 availability (adding the
other projects that he already work)

In simple words, a validation rule/procedure, that checks that the sum of
other projects + actual entry is less than 100%.

I already try using validation rules that calls a procedure, but it couldn't
do it.

I hope thas someo has some procedures for this

Here is the function that is called by the validation, it seems


Function valida_horas1(PerNo As String, mes As Integer) As Double

Dim sql As String
Dim rst_consulta As Recordset
Dim db As Database
Dim total As Double

Set db = CurrentDb

sql = "SELECT PerNo, Sum(" + Trim(Str(mes)) + ") AS total FROM Plan "
sql = sql + " GROUP BY Plan.PerNo HAVING Plan.PerNo='" + Trim(PerNo) + "';"

'MsgBox sql
Set rst_consulta = db.OpenRecordset(sql, dbOpenSnapshot)
If rst_consulta.RecordCount > 1 Then
valida_horas = rst_consulta("total")
Else
valida_horas = rst_consulta("total")
End If
End Function

-------------------------------------
 
When the validation you need to do becomes too complex to make into a simple
formula in your ValidationRule property, use the BeforeUpdate event
procedure of the control to run your code.
This procedure has a Cancel argument; if you set it to True, focus does
not move off that control.
One way to do it would be like this:

Private Sub txtMar_BeforeUpdate(Cancel as Integer)
if DSum("txtMar","Plan","Perno='" & PerNo & "'") + txtMar >100 then
msgbox "This guy can't work so much!",,PerNo
Cancel=True
endif

HTH
And Merry Christmas to you, too!
 
Back
Top