Restricting an entry question

[stephent]

Hi,
I am in the process of building a db and need some help please.
The db contains 3 tables: Employees, Cars and Trips (all related)
When an employee is first registered in tblEmployee, the driver's licence
details (including expiry date) are recorded in the appropriate fields, also
they are given an unique Employee_ID.
As the employees come to 'hire' a car from the vehicle pool, their Employ_ID
needs to be entered into the tblTrip table. What I would like to do is to
only allow their Employ_ID to be entered if their licence is still valid -
in other words not allow any employee to hire a vehicle if their licence has
expired.
Would it work better if the validation blocked entry into the next field
(after Employee_ID), which is Trip_ID (primary key)?
I'm still struggling with writing code, so would appreciate help with this
one please.
Basically a 'what to put where' question I think.
Thanks in advance,
Steve.


Regards,
Steve Thomas

 

[Jeff Boyce]

Steve

If you are working directly in the tables, sorry, you're out of luck. If
you are working in forms, you can put "code behind form" that you can use to
inspect the "expiry date". In fact, if you use a combo box to list
available/valid Employees, you can use a query that ONLY gets folks with an
unexpired license!

Good luck

Jeff Boyce
<Access MVP>

 

[stephent]

Hi Jeff,
Thanks for that prompt reply...I'll have to try using a query then!
Cheers,
Steve.

 

[Tim Ferguson]

What I would like to do is to
only allow their Employ_ID to be entered if their licence is still
valid - in other words not allow any employee to hire a vehicle if
their licence has expired.

If you have some kind of picklist arrangement for getting the Employ_ID
onto the New Trips form, then it would be easy just to filter what names
appear in the list. On a combo box or listbox, you would just change the
RowSource to something like

SELECT Employ_ID,
LName & ", " & FName & " [" & Office & "]" AS DisplayName
FROM Employees
WHERE Date() < LicenseExpiryDate
ORDER BY 2;

-- obviously you would hide the first column. In this way, the user does
not get the opportunity to choose an expired driver.

An alternative would be to use a BeforeUpdate event on the Employ_ID
control to do a

DLookUp( _
"LicenseExpiryDate", _
"Employees", _
"Employ_ID=" & me!txtEmploy_ID)

kind of thing and warn or forbid the user to go ahead.

Hope that helps


Tim F

 

[stephent]

Thanks very much for that Tim,
I'll have a fiddle around with those suggestions and see if I can find where
exactly they go!!

Cheers again,
Steve

 

[David]

I hope the drivers licence numbers are encrypted to guard
against identity theft!