Access form and validation

[Hello World]

Hi all,

I have a table called ‘Employee Details’ with 25,000 unique records. It has 4 fields – ID, Surname, Employee Number, Answer?).

I have also created a form called ‘HR form’. This has 3 fields for user data entry - Surname, Employee Number, Answer? The idea is that HR staff will receive a completed paper survey from each employee. They are then required to enter this info into the form. As soon as they have put into the form Surname and Employee Number into the form I would like the form to validate whether both of these fields are correct - i.e. does the employee actually exist? (using the table ‘Employee Details’ to check against).



If it is then nothing happens and they are allowed to enter something into the Answer? box on the form.
If either of them fail then I would like a message box to come up giving them two choices:
Error: do you want to reset the form and try again or submit the form as it is?

If they wish to submit the form as it is then the info gets entered into another table called 'Invalid'

Any pointers?

Hope that makes sense. Thanks for any help. I’m very new to Access.

[rpeare]

That's a very long way to go about things, why not do this through combo boxes and limit the data entry to items that are on your list?

If you have a combo box that lists all last names then have the first name combo box limited by the contents of the last name combo box you will never be allowed to enter a record for a person that does not exist.

[apr pillai]

A Sample VBA solution to your problem is given below:

Private Sub EmployeeNumber_LostFocus()
Dim emp, empno, db As Database
Dim rst As Recordset, criteria As String
Dim rst2 As Recordset

emp = Me![Surname]
empno = Me![Employee Number]

Set db = CurrentDb
Set rst = db.OpenRecordset("EmployeeDetails", dbOpenDynaset)
criteria = "Surname = " & emp & "AND [empno] = " & Me!EmployeeNumber
 rst.FindFirst criteria
If Not rst.NoMatch Then
    GoTo closedb
Else
   If MsgBox("Record Not Found, add Info to Invalid Table?..", vbYesNo + vbDefaultButton2 + vbCritical, "attDate_LostFocus") = vbYes Then
      'Open the Invalid Table and add the details
      Set rst2 = db.OpenRecordset("Invalid", dbOpenDynaset)
      With rst2
         .AddNew
         ![FieldName1] = Me![Surname]
         ![FieldName2] = Me![EmployeeNumber]
         ![FieldName3] = Me![Answer]
         .Update
      End With
      rst2.Close
      Set rst2 = Nothing
      'reset the fields to null
      Me.Undo
   End If
End If

closedb:
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

When you comple the Surname & EmployeeNumber field values on the 'HR Form' and leave out of the Employee Number field the program will run. It searches the Employee Details table for a match, if found then does nothing, if not a message pops up asking for confirmation to add the values entered into the Invalid table. When the user's response is Yes then adds the values from the Form controls into the Invalid Table and reset the controls of the HR Form.

[Hello World]

Thanks guys! I'm gonna try this tomorrow when i get back to work! Thank you.