validation rule

Hi currently i have a table for which i assigned an engineer a vehicle, what i want is a way of not being able to assign a tech a vehicle that is already assigned


field names
Tech_Vehicle_ID
Date_Assigned_To_Tech
Date_Returned
Vehicle_Reg
Assigned_To_Tech

so if the date_returned was blank then that means its still assigned to a tech so you would not be able to use that vehicle reg untill it has been returned by the tech, this is entered by a form, what the ideal solution would be is when you enter a reg that is already assigned i want a message box, stating that this is with another tech and then that record to automatically pop up for you to unassign it (by entering a Date_Returned. is this something that can be done?

How about using Dlookup to check the datereturned for the vehicle.

Brian

Thank you for the reply, to be honest i dont know how to do that. ive never used access before.

I want to try and do as much of this myself as possible even if its people telling me how to do it then me copying it, as i really am intrested in learning this programme. I know exactly what i want from the database and an idea of how i want it to look, its now just down to translating my ideas to the actual db.

OK you can find out about DLookup in VBA help, so open a module, click new if you haven't got one then click help and search for Dlookup.

This can be used in the control source of a text bx on your form, something like

=IIF(Dlookup("[Date_returned]","yourtablename","[vehicleid] = whatever") is Null,entry if null,entryifnot)

The syntax can be difficult, eg if the vehicle id is a string (text) the surround it in single quotes ='ade123'"

Brian

is there a way of doing it with the macro builder say once ive input the information there was a before update macro that would search and check and then pop up a the record if the vehicle was with somelse