Valadation required

[bopsgtir]

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?

[RuralGuy]

Have you tried using a ComboBox on a form for this function?

[bopsgtir]

it is a form used to assigned a van to a tech, but you can assigned the same van to lots of techs, i need a way of stopping you choosing a van if it is already with someone, and ideally then bring up that record in a pop up so you can then unassigned the van and then continue with your assign vehicle form.

[RuralGuy]

The ComboBox would allow you to pick a VALID van and then the BeforeUpdate event of the cbo could check and see if it was already assigned and pop up the reassignment form.

[bopsgtir]

so a vaild van is any van in my vehicle list even ones assigned to another tech but it wouldnt let me add that entry till it had checked it that vehicle was with somelse. thats exactly what i want, so would i then use the macro builder to write the beforeupdate event.

This may take sometime.

[RuralGuy]

I don't use Macros so I'm not a lot of help here. I only use code except for the AutoExec macro. If you let the wizard create a cbo that will look up a record (Option #3) you will see the code you need for this function.

[bopsgtir]

would it be better to move this tread to programming and ask fir help on the macro in there

[RuralGuy]

Those that assist here read all of the forums so where it is is not particularly important.

[bopsgtir]

ok, ive started to look at the macro builder and im ashuming that i would use

search for record
object type table
object name tech_vehicle

then the criteria would be where the reg matches the one in the form and shows date-returned as null, but not to sure how to write that

[RuralGuy]

Did you let the wizard create a ComboBox for you as an example?

[bopsgtir]

yes it didnt really help, im quite a bit out of my confort zone here,

Ive been playing about a little bit and im guessing i need to use the macro builder on afterupdate, then id want to open table tech_vehicle and search for record then its the where condition = is the bit im struggling.

i want to say if reg in tech_vehicle form equals reg in tech vehicle table and date_returned is null then open that record to sign the vehicle off (add return date) then continue with my form entry.

[RuralGuy]

What is the RecordSource for your form? Is it the table you described in the first post?

[bopsgtir]

yes it is, the reg is pulled from the vehicle table but the record for which tech has it is in tech_vehicle tbl and uses a form with the same name.

[RuralGuy]

Did the wizard create a ComboBox that uses code or macro's?

[bopsgtir]

it just comes back as

Option Compare Database
Private Sub Vehicle_Reg_AfterUpdate()
End Sub