Access - Database - Form - Field Validation - LookUp function

[Faye Gorne]

New to coding and need help with field validation and LookUp function.

Tables:
01. Emploee (fields = Employee ID, Fname, Lname)
02. Week Ending Dates (fields = Week Ending, Employee ID)

I am trying to build a form based on these tables.
First Employee ID is entered. Before update I want to validate this field
then look it up in the Employee table and validate it again against the table
value and if it is found in the table, populate Fname and Lname fields with
the corresponding values. Bellow are the steps.

Validate Employee ID
Before Update
If invalid, display error message "Invalid value"
If valid, Look it up in the Employee table and compare
If not found in the table, display message "Emploee ID is not found.
Contact
Administrator".
If found, LookUp Employee ID in the Employee table and populate Fname
& Lname fileds.

Validate Week Ending
Before Update
If invalid, display error message "Invalid value"
If valid, LookUp in Week Ending Date table.
If not found, display message "Incorect week ending date"


Could you please help me with directions and coding?

 

[Ken Sheridan]

You say the form is based on the Employee and Week Ending Dates tables, but
at the same time you want to validate data entry in the form against rows in
those tables. It sounds to me more like data is being entered into another
table which is the form's RecordSource. If that's the scenario then its
worth outlining a few basic principles here:

1. The Fname and Lname values should not be entered in any table other than
the Employee table. Only the EmployeeID should be entered as from that you
know the names. To have them in more than one table introduces redundancy
and the risk of inconsistent data.

2. Users don't need to know an employee's EmployeeID value to enter it in
another table; they can use a combo box to look the employee up by name.

3. Is there only one row per EmployeeID in the Week Ending Dates table?
i.e. does each employee have one week ending date. If so then this can
simply be another column (field) in the Employee table. Tables are sometimes
legitimately related on-to-one but only where one table represents a sub-type
of the type represented by the other. If, however, there can be more than
one week ending date per employee then the relationship is many-to-many and
you do need both tables.

With you present two table scenario the way to enter data into a
'referencing' table to which the form is bound would be firstly to have a
combo box bound to the EmployeeID column. Its properties would be like this:

RowSource: SELECT EmployeeID, Fname & " " & Lname AS FullName FROM
Employee ORDER BY Lname, Fname;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The user can then simply select an employee by name but the value of the
control, and thus the column in the underlying table would be the EmployeeID.
No validation is needed as the user is selecting from a list of known
employees.

There would also be a combo box on the form bound to the Week Ending column.
This would be correlated with the EmployeeID combo box by means of a
RowSource property of:

SELECT [Week Ending] FROM [Week Ending Dates] WHERE EmployeeID =
Form!EmployeeID ORDER BY [Week Ending];

Its other properties can be left as the defaults.

So that the Week Ending combo box lists only dates for the employee selected
in the EmployeeID combo box you need to requery the former in the AfterUpdate
event procedure with:

Me.[Week Ending] = Null
Me.[Week Ending].Requery

With a single Employee table, including a Week Ending column the EmployeeID
combo box would be set up as follows:

RowSource: SELECT EmployeeID, Fname & " " & Lname AS FullName, [Week
Ending] FROM Employee ORDER BY Lname, Fname;

BoundColum: 1
ColumnCount: 3
ColumnWidths 0cm;8cm;0cm

Instead of having a Week ending bound combo box on the form you'd now have
an unbound text box which references the EmployeeID combo box by having a
ControlSource of:

=[EmployeeID].Column(2)

Note that the Column property is zero based, so Column(2) is the third
column, i.e. the Week Ending column. When an employee is selected in the
combo box the relevant Week Ending date will show in the text box. Again, no
validation is necessary.

Ken Sheridan
Stafford, England

 

[Klatuu]

In the Before Update event of the employee id text box:

Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("Employee", dbOpenDynaset)
With rst
.FindFirst "[Employee ID] = " & Me.txtEmployeeID
If .NoMatch Then
MsgBox "Employee ID Not Found"
Cancel = True
Else
Me.txtEmpFname = ![FName]
Me.txtEmpLname = ![LName]
End If
.Close
End With

In the WeekEnding Before Update event:

If Not IsDate(Me.txtWeekEndingDate) Then
Msgbox "Invalid Date"
Cancel = True
Elseif IsNull(Me.txtEmployeeID) Then
MsgBox = "Enter an Employee Before Selecting a Date"
Cancel = True
Me.txtEmployeeID.SetFocus
Elseif IsNull(DLookup("[Week Ending]", "[Week Ending Dates]", "[Employee
ID] = " & Me.txtEmployeeID & " AND [Week Ending] = #" & Me.txtWeekEndingDate
& "#")) Then
MsgBox txtMe.WeekEndingDate & " Not Found For " & Me.txtEmployeeID
Cancel = True
End If

If invalid, display error message "Invalid value"
If valid, LookUp in Week Ending Date table.
If not found, display message "Incorect week ending date"

 

[George Nicholson]

Assuming: 1) EmployeeID is stored as a numerical value (not text)
2) txtEmployee and txtWeekEnding are textboxes containing
the values you want to validate.
'********
If Not IsNumber(me.txtEmployeeID) Then
MsgBox "Employee ID must be a number."
? Exit Sub?
Else
strLName = nz(Dlookup("Lname","Employee","EmployeeID = " &
me.txtEmployeeID),"")

If strLName = ""
' No matching record
MsgBox "Employee ID is not found. Contact Administrator."
? Exit Sub?
Else
' Record exists
Me.txtLname = strLName
Me.txtFname = nz(Dlookup("Fname","Employee","EmployeeID = " &
me.txtEmployeeID),"")
End If
End If

If Not IsDate(me.txtWeekEnding) Then
MsgBox "Week Ending must be a date."
? Exit Sub?
Else
dtmWeekEnding =
nz(Dlookup("WeekEnding","WeekEndingDates","(EmployeeID = " &
me.txtEmployeeID ") AND (WeekEnding = #" & cdate(me.txtWeekEnding) & "#)",0)
If dtmWeekEnding = 0
' No matching record
MsgBox "Employee ID and WeekEnding combination does not exist.
Contact Administrator."
? Exit Sub?
Else
' Record exists
' Do Something?
End If
End If
'********

If Dlookup does not find a matching record, it will return Null, which will
cause an error if you are trying to assign the Dlookup results to anything
other than a variant. Therefore, the above code wraps Dlookup within NZ() so
you can easily tell if the record exists or not without raising an error.