prevent duplicate event on Date (access VBA ) English

hi in many cases we need to prevent the publication of an event on the same date or time something like if you try to book a room which is already booked on a date the system is supposed to declare new requests or if you try to assign a staff member to a task while he is assigned to another task on the same day the system is supposed to diffuse direction and in this very simple example if I try to assign a staff member let's say Roger and 21st of February so data saved successfully if I try to assign him again on the exact same date the system will refuse will decline my request and data would be raised so actually he is assigned only once was the second sign was refused by the system I would create another task table and another form just to explain how we can using VBA code did the access to refuse the application of an event in the same date or time so I'm going to create a table design this table the start was ID which is just an auto number and this one is the primary key then I have the stuff remember which is a lock up wizard it will come from another table from the HR table I will take the ID and the name and ID is the primary key so is supposed to be hidden if I need to display it I uncheck hide key column of course I need to enforce the integrity between the two table change then we will have task date which is a different time now I would create a calculated field which would use stuff ID with the date to create a new value let's say ID this new value I would use to blue pencil application so I'm going to take the staff member added to the date by default the system when I say we'll take the data type to be a date on time I will change this one to be double so every time I'm going to assign a staff member with a specific date a unique idea would be created using the ID entity so if I tell the axis this field cannot be duplicated it means the staff member cannot be assigned twice with the same date of course I can do this using unique if the field is any other type roses are calculated because calculated has not indexed option so I'm going to type a VP a code there's a basic application code to do this consultation so I'm going to create a form wizard using task to ID is just an auto number so I'm going to hide it from the design I don't need to be displayed this one is a calculated field so it is not enabled it will come automatically every time the user assigned a stuff them okay and this form I would add I supposed to hide all the navigation button I add my own buttons but we are going to add only one button which is the Save button this button will examine the table tasks to table and try to find if the staff member is already assigned at the same date so it will decline the new aside otherwise it will accept as eloquent so event unclick so I'm going to them a variable let's say X as X is equal to D count so I will count ID from the table task is to my condition is has to Bart one of the Bart is static which is the ID from the table and the other part is dynamic coming from the form so I'm going another to code and me dot the ID and to quote another to code close the blacks just to make sure the statement is correct and boom - message box X so this button if I try to assign a staff member Edward it's assigned 29 this is the first assign of Edward so the result when X is zero if I try to assign it word again the result will be one which it means edward is already assigned on 29 of february so instead of message box x i will check if X is greater than zero so if X is greater than zero it means this a stuffed member is assigned in that date so I will message box that the staff member then I'm going to erase the record so me dude undo then I will exit sub and don't forget to end if so every time if I try to assign it word again so it word is already assigned but if I send a new one let's say George 29 February okay it is done so if George is not assigned of that date it means X is not greater than 0 so in this case I will save the record then I would message box that all data has been saved then I will go to a new record they could go to you so if I try to assign visa in 2009 the first time so what it has been saved if I try to assign Lisa again on that date the system will refuse because these are already assigned ones I did

Channel: askfarouk

Views: 3,600

Rating: 5 out of 5

Keywords:

Id: M4c1td7f6F8

Length: 7min 40sec (460 seconds)

Published: Wed Feb 19 2020