Date Validation

[Guest]

I have a database for room bookings but am having problems with the date
validation. On the booking form I have a popup calendar which populates the
booking date field. I have been able to set a validation so the date is

=date() but this only works if the date is typed into the field and not if

the calendar is used. Can anyone help?

 

[Wayne Phillips]

Hi,

Is the control bound to the field? If it is then the validation rule should
still work.

Also, make sure you've put the validation rule into the Table design, not
just the form design. (Open the table in design view, select the field and
check the 'Validation Rule' property)

If this doesn't help, please reply indicating *which* calendar control you
are using.

Regards,

Wayne Phillips
http://www.everythingaccess.com

 

[Guest]

Many thanks Wayne, I was given the link to the calendar in one of the Access
newsgroups, it is http://members.iinet.net.au/~allenbrowne/ser-51.html I have
entered the validation rule =date() or >date() into the booking table without
it updating the previous entries (as they are in the past). Now if I try to
enter a date in the past using the popup calendar the date text box stays
blank but if I type in a date in the past I get the validation text. I am
still quite new to access so simple answers would be appreciated,

 

[Wayne Phillips]

Many thanks Wayne, I was given the link to the calendar in one of the Access
newsgroups, it is http://members.iinet.net.au/~allenbrowne/ser-51.html I have
entered the validation rule =date() or >date() into the booking table without
it updating the previous entries (as they are in the past). Now if I try to
enter a date in the past using the popup calendar the date text box stays
blank but if I type in a date in the past I get the validation text. I am
still quite new to access so simple answers would be appreciated,

I've just tested Allen Browne's Calendar control and found a small bug that
is surpressing the error message from the Validation Rule.

You will need to open the form called frmCalendar in the Visual Basic Editor
to fix this.

Locate the procedure 'cmdOk_Click' which looks like this:
----------------------------------------------------------------------------
------------------
Private Sub cmdOk_Click()
On Error Resume Next
'Purpose: Transfer the result back to the calling text box (if there
is one), and close.

If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
----------------------------------------------------------------------------
------------------

And replace with what's here:

----------------------------------------------------------------------------
------------------
Private Sub cmdOk_Click()
'Purpose: Transfer the result back to the calling text box (if there is
one), and close.

On Error GoTo ErrorHandler:

If Not gtxtCalTarget Is Nothing Then

If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If

gtxtCalTarget.SetFocus

End If

DoCmd.Close acForm, Me.Name, acSaveNo

Exit Sub

ErrorHandler:
MsgBox Err.Description

End Sub
----------------------------------------------------------------------------
------------------

I've copied this message to Allen's e-mail so hopefully it can be fixed for
future.

Regards,

Wayne Phillips
http://www.everythingaccess.com

 

[Guest]

Thanks again, I copied and pasted as you suggested but I keep on getting an
error message: Compile error, syntax error. When I click on OK the 'Private
Sub cmdOk_Click()' section is in a yellow box and the: 'one), and close'
section at the end of the next sentence is in red text while the start of
that sentence is in green. Sorry to be a pest.

 

[Wayne Phillips]

Sorry, 'if there is one), and close' should all be on one line - it got
split after posting

Cheers,

Wayne
http://www.everythingaccess.com

 

[Guest]

Many thanks, it is working now. Could I ask one more question? I have been
able to fix the tab order in the booking form but I was wondering if it is
possible to fix the form so that the user can only move through the form by
using the tab button and not with the mouse?

Diane

 

[Wayne Phillips]

Hi Diane,

Interesting question, I've never been asked that before...

I take it that the reason for this is to ensure all fields have something
entered and therefore cannot be blank? I think you would be better do a
validation check at the end of entering data instead of trying to stop the
user using the mouse as I'm sure it would frustrate some users.

There is a way of doing it, but it's a bit 'dodgy'...
You could write some VBA code to check each control has something entered,
one by one and use the SetFocus method of the first control that is blank.
You would then call this VBA function from within each controls 'On Got
Focus' event.
This way, a user could change the focus to another field by using the mouse
but the focus would immediately jump back to the first field that is empty.

Like I said, I would NOT recommend doing this, but it is possible. If you
do want to do it like this, I can help you with the code.

Regards,

Wayne
http://www.everythingaccess.com

Many thanks, it is working now. Could I ask one more question? I have been
able to fix the tab order in the booking form but I was wondering if it is
possible to fix the form so that the user can only move through the form by
using the tab button and not with the mouse?

Diane

Sorry, 'if there is one), and close' should all be on one line - it got
split after posting

Cheers,

Wayne
http://www.everythingaccess.com

Thanks again, I copied and pasted as you suggested but I keep on

getting
an

error message: Compile error, syntax error. When I click on OK the 'Private
Sub cmdOk_Click()' section is in a yellow box and the: 'one), and close'
section at the end of the next sentence is in red text while the start of
that sentence is in green. Sorry to be a pest.

:

Many thanks Wayne, I was given the link to the calendar in one of the
Access
newsgroups, it is

http://members.iinet.net.au/~allenbrowne/ser-51.html
I

have
entered the validation rule =date() or >date() into the booking table
without
it updating the previous entries (as they are in the past). Now

if I
try

to
enter a date in the past using the popup calendar the date text

box
stays

blank but if I type in a date in the past I get the validation

text.
I am

still quite new to access so simple answers would be appreciated,

I've just tested Allen Browne's Calendar control and found a small

bug
that

is surpressing the error message from the Validation Rule.

You will need to open the form called frmCalendar in the Visual

Basic
Editor

to fix this.

Locate the procedure 'cmdOk_Click' which looks like this:

--------------------------------------------------------------------------

--
------------------
Private Sub cmdOk_Click()
On Error Resume Next
'Purpose: Transfer the result back to the calling text box (if there
is one), and close.

If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If
gtxtCalTarget.SetFocus
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

--------------------------------------------------------------------------

--
------------------

And replace with what's here:

-------------------------------------------------------------------------- there
is

one), and close.

On Error GoTo ErrorHandler:

If Not gtxtCalTarget Is Nothing Then

If gtxtCalTarget = Me.txtDate Then
'do nothing
Else
gtxtCalTarget = Me.txtDate
End If

gtxtCalTarget.SetFocus

End If

DoCmd.Close acForm, Me.Name, acSaveNo

Exit Sub

ErrorHandler:
MsgBox Err.Description

End Sub

-------------------------------------------------------------------------- fixed
for

future.

Regards,

Wayne Phillips
http://www.everythingaccess.com


:

Hi,

Is the control bound to the field? If it is then the validation rule
should
still work.

Also, make sure you've put the validation rule into the Table design,
not
just the form design. (Open the table in design view, select

the
field

and
check the 'Validation Rule' property)

If this doesn't help, please reply indicating *which* calendar control
you
are using.

Regards,

Wayne Phillips
http://www.everythingaccess.com

message
I have a database for room bookings but am having problems

with
the

date
validation. On the booking form I have a popup calendar which
populates
the
booking date field. I have been able to set a validation so

the
date

is
=date() but this only works if the date is typed into the

field
and

not
if
the calendar is used. Can anyone help?

 

[Guest]

Wayne, Thanks very much for your time today. I will go with your
recommendation and leave well enough alone.

Cheers
Diane