New to macros i know what i want it to but dont know how to do it

[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.

what i want to do is enter the information then when i try to add that record the macro would come in with beforeupdate, then it would search my tech_vehicle table for that reg and see if it appears anywhere with the Date_Returned field blank and if it does open that record so the user could then unassigned the vehicle before continuing with his data entry.

im using access 2010 with the macro builder but still getting a bit confused

 

[MSAccessRookie]

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.

what i want to do is enter the information then when i try to add that record the macro would come in with beforeupdate, then it would search my tech_vehicle table for that reg and see if it appears anywhere with the Date_Returned field blank and if it does open that record so the user could then unassigned the vehicle before continuing with his data entry.

im using access 2010 with the macro builder but still getting a bit confused

You list the structure of a single Table, and make reference to possible additional Tables. While a Macro (or VBA Code) might be able to do this for you, have you considered a preemptive strike?

You could have the SQL Code behind the dropdown box for vehicles only allow vehicles to be considered if they are available, so that you would not need to test after the selection is made.

If this is not a possibility, then I imagine that there might be some level of normalization issues here. Listing the structures of all of the Tables involved wouild be necessary for further analysis.

 

[boblarson]

Isn't this a duplicate of this thread?

 

[MSAccessRookie]

Isn't this a duplicate of this thread?

It would appear that way. I did not see the other copy when I responded.

 

[Bopsgtir]

yes it is sorry i realised it was more of a macro question so i posted in this section,

i dont think is was clear as there is only one table structure concerned being the tech_vehicle table, i have a tech_vehicle form which is where all vehicle changes are done, so if on the form you put a reg number that applies to record already in the table without a return date then it would open that record so you could check the vehicle back in (add return date) and then finish your entry.

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.

sorry for posting twice i will make sure i dont do it again Bob

 

[Bopsgtir]

it just comes back as

Option Compare Database
Private Sub Vehicle_Reg_AfterUpdate()
End Sub