Date Validation

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
Click to expand...
the calendar is used. Can anyone help?
 
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
 
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, :-)
 
DianeandChipps said:
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, :-)
Click to expand...

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
 
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, 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
 
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

DianeandChipps said:
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 said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
if I
try
to
enter a date in the past using the popup calendar the date text
Click to expand...
box
stays
blank but if I type in a date in the past I get the validation
Click to expand...
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
Click to expand...
bug
that
is surpressing the error message from the Validation Rule.

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

Locate the procedure 'cmdOk_Click' which looks like this:
Click to expand...
--------------------------------------------------------------------------
--
------------------
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
Click to expand...
--------------------------------------------------------------------------
--
------------------

And replace with what's here:
Click to expand...
-------------------------------------------------------------------------- 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
Click to expand...
-------------------------------------------------------------------------- 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
Click to expand...
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
Click to expand...
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
Click to expand...
the
date
is
=date() but this only works if the date is typed into the
Click to expand...
field
and
not
if
the calendar is used. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Wayne, Thanks very much for your time today. I will go with your
recommendation and leave well enough alone.

Cheers
Diane
 
Back
Top