Date Validation

[Zac Harvey]

Ok, so my other little problem. I can't seem to get a working validation on the BookingDate field of frmBookings. I've tried everything immaginable and even though a lot of them worked they also came up with extra message boxes which I hadn't set. So not quite sure what to do with that. Any ideas?

** Edit **
This new question was split from Time Interval Overlapping (MS Access 2003).

[NeoPa]

Zac, I'm sorry but this new question is also very short of the necessary information for us to help. You'd need to give details of what you're trying to do and what you've tried already etc. It needs quite a bit of work to be a proper question.

[Zac Harvey]

Well the reason for that Is that I supplied my datbase in the previous thread and therefore thought it would be easier for someone to just look at that rather than me posting the code etc. But nevermind, I will do that now. Makes sense since you've moved it to a new thread anyway.

[Zac Harvey]

On my booking form I have a field which records the date for which a room is being booked and it is a mandatory field. I simply wanted to ensure that the user could not enter a date which preceeded the date on which it was being entered i.e. Date()

Now one thing to point out is that whenever I try and include Date() in an If function it removes the brackets then when it checks it says it cannot find the date field. So I've tried putting the statement within other brackets both rounded and squared.

But other that that I have tried using various Macros and expressions in the validation box of 'BookingDate' on both the form and the table itself. (One thing I'd like to clarify here.. am I right to assume that the validation should be done on the form and not in thet table as then whenever the date of that particular booking passed it would become invalid and possibly cause problems?)

Anyway, I've tried doing it so many different ways I cant remember but heres one of the basic ones I tried which wont work because of the Date() problem described above.

Private Sub BookingDate_BeforeUpdate(Cancel As Integer)
    If Me.BookingDate > Date Then
    MsgBox ("Please check your booking date. It can't preceed todays date.")
    Me.BookingDate.Undo
    End If
End Sub
 

[NeoPa]

A few small issues to cover here. Let's start with the easiest.

It's always a good idea to post your question in text in the post rather than expect people to look through your database to determine what you need. It's a rule, but it benefits you just as much as it does the site. Consider how many of the experts are going to go to the trouble of downloading your database and having a look through it (which may be more complicated than it sounds as they wouldn't necessarily know where to start), just to determine what the question should be? I'm not trying to sound critical here, as the interaction we've already shared tells me that you're ready to learn new things when they're explained. I'm just trying to explain the situation so it's more easily understood why this rule is as it is. There are more reasons than just this (but I won't bore you with them).

Moving on again to the technical points :

1. I've seen the editor remove the parentheses from after a function call before (particularly with Date()), but I've never seen it resolving to a field reference when ambiguous (IE. without it being written as [Date]). I may have to have a look in your database after all. Can you tell me where I can find this code? Which form is it associated with?
2. Your line #4 uses Undo, whereas the Cancel parameter is set up specifically to do that job in a BeforeUpdate event procedure. Simply say :
   

   Cancel = True

3. Other than that - I would certainly expect that code to work as you envision it.

[Zac Harvey]

Thanks for replying Neo, Hope you enjoyed the extended weekend. Sorry about the seperate posts above, I will bear this in mind in future posts.

I have taken your advice and changed the action to 'Cancel = True'.

The date function in question can be found under Forms > frmBookings if you still intended on taking a look.

Also I have realised another major problem with this date business. Because I usea pop-up callander to give the user the option select the date it proved a way around the validation. Are you aware of anything I can do about this?

Cheers

[NeoPa]

I will try to look at this for you Zac. It's been pretty hectic the last few days I'm afraid (Good when you're self-employed as I am - but still a nuisance).

[Zac Harvey]

No problem, I get it. Much appreciated as always Neo.

[NeoPa]

Zac, you're using the CalendarFor() procedure directly in the OnClick property of your CommandButton control. This isn't something I'm very familiar with, but it seems it updates the specified TextBox control without triggering any BeforeUpdate event of that control.

This seems a bit of an awkward approach to me (compared to using a Calendar Control 11.0 object for instance), but if you're set on that then I can only suggest creating a hidden (and unbound) TextBox and have the CommandButton's OnClick point to an event procedure that executes the CalendarFor() procedure then tests the results. Only if the selection is correctly validated would you copy the date to the control [BookingDate].