HI! June7
i have tried to answer your questions
So what makes a location 'available'? If it already has a Lodged_Date it is not available? Maybe open form to only 'available' records? If the Lodged_Date must match a record in Master - which record? They all show the same date now. Will there be other dates? Need to do data entry validation for Lodged_Date? Use a combobox for selection of valid date. So if the Lodged_Date must already be associated with MICR code, can't really know what correct date is until the MICR code is calculated.
tbl_Master_Location already has all the locations. UserB will acknowledge the docs by selecting anyone of the location but the date will be the same date UserA has selected. UserB will select another location when he reaches maximum number of docs that can accommodate in that location, e.g. 700 docs, above that he selects another location but the date will be same. So here there will be two locations for one date.
Will MICR code be unique in Master_MICR or can it be repeated with a different Lodged_Date?
No it will unique for one lodged date. but if you see the 33 digit number has the last 6 digits as date which will change if it received again for that date.
MICR. Below example
6286848020201257100540010120619
6286848020201257100540010150719
Still not clear to me what is considered a 'duplicate' record in NewMICR. If a MICR code must be unique then the date and location are not considered. If MICR code can be repeated with different date and/or location then duplication would occur when the compound index is repeated
UserB accidentally scans the same MICR again with the same date. There is already Indexed to Yes, noDuplicates but to stop this through vba is what i am looking. If UserB scans with another date it will not match with tbl_Master_MICR, so one he gets these mis-match he can rectify or simple delete that row.
Similarly, will Loc_Code be unique in Master_Location? Will never be more than these 30 records or will this grow?
Yes, it will grow depending on the filing area. Now its one chub, tomorrow it may more.