Hi guys. I have a problem with a validation rule for a field on a form.

I have a form called frmRMDowntime, which records the downtime (or days off) for any employee in the database. I also have a form called frmRMGlobalHolidays which records the global holidays for the business as a whole. The frmRMDowntime form has a tabular subform that shows filtered results for the analyst selected in a combo box and allows the user to enter new downtime. However, since it's important that downtime not already be entered as a global holiday, I have the subform do a Dlookup to assure the date isn't a duplicate value in either Global Holidays or Downtime. In the Date field on the subform, I have this Validation Rule:

DLookUp("Date","Downtime","[Date] = [Forms]![Downtime subform]![Date] AND [AnalystID]= [Forms]![Downtime subform]![AnalystID]") Is Null And DLookUp("Date","GlobalHolidays","[Date] = [Forms]![Downtime subform]![Date]") Is Null

This works fine if you view the subform as a standalone form. However, when it is imbedded into the frmRMDowntime form, any attempt at entering a date results in the status bar reading "Calculating" and then not allowing the user to leave the field or for the form to be closed. I can't figure this out for the life of me. Does anyone have any ideas? I've attached a (very) stripped down version of the database to illustrate the problem.

Hi
I haven't looked at your db, but from your example in your post your using the word "Date" as a field name and this could/will be a problem.
Change it....
Date is in the List of reserved words in Access .

The syntax needs to be

[Forms]![Main Form Name]![Sub Form].Form![Date]

However, a better method than the validation rule would be to use VBA code on the after update event of the date field.



Also - as Larry suggests - I would rename your [Date] field.

Thanks guys! The [Date] wasn't a problem (and it's a bit late in the game to go changing that field name), but the syntax was the issue. Thanks for all your help and the quick solution!

Happy to help.

Unbelievably, I'm having a related problem with this again. I have another form that is identical to the form mentioned above, using the exact same data (it isn't the same form because it has different text on it, but everything else is the same) and I need to do the exact same validation. In my original form (where frmFPdowntime was the main form, Downtime subform was the tabular subform and Downtime/GlobalHolidays were the tables to be validated), I put in this formula to validate the Date field in the Downtime subform and make sure that no downtime has already been entered per analyst and doesn't already exist as a GlobalHoliday.

DLookUp("Date","Downtime","[Date] = [Forms]![frmFPdowntime]![Downtime subform].Form![Date] AND [AnalystID]= [Forms]![frmFPdowntime]![Downtime subform].Form![AnalystID]") Is Null And DLookUp("Date","GlobalHolidays","[Date] = [Forms]![frmFPdowntime]![Downtime subform].Form![Date]") Is Null

This works perfectly. The new form I'm working on is the same, except the Downtime subform is Downtime subform 2 and the frmFPdowntime is frmRMdowntime. This is the validation rule I entered:

DLookUp("Date","Downtime","[Date] = [Forms]![frmRMdowntime]![Downtime subform 2].Form![Date] AND [AnalystID]= [Forms]![frmRMdowntime]![Downtime subform 2].Form![AnalystID]") Is Null And DLookUp("Date","GlobalHolidays","[Date] = [Forms]![frmRMdowntime]![Downtime subform 2].Form![Date]") Is Null

And now this one isn't working! It's just giving me the endless Calculating error I mentioned before. I know people advise against using Date as a field name, but I have a truly hard time believing this is the issue, considering a slightly modified version of the same formula works in the same database on the same tables. What could I be doing wrong?

Just to better show the issue, I'm attaching a very stripped down version of my database to demonstrate the problem. Open frmFPdowntime and try entering 25/12/2005 as a new downtime. It won't let you. Then try doing it on frmRMdowntime... you get the calculating bug. The underlying subforms are exactly the same except for name.

Any help would be appreciated. Thanks!

Anyone have any ideas? Hate to bump, but still perpexed and mystified.

DLookup("Date", "Downtime", "[Date] = #" & [Forms]![frmRMdowntime]![Downtime subform 2].Form![Date] & "#" And [AnalystID] = " & [Forms]![frmRMdowntime]![Downtime subform 2].Form![AnalystID] & ") Is Null And DLookup("Date", "GlobalHolidays", "[Date] = #" & [Forms]![frmRMdowntime]![Downtime subform 2].Form![Date] & "#") Is Null

Please read:
http://www.mvps.org/access/general/gen0018.htm