Use of DCount

K

Ken Snell [MVP]

If you'd like to zip up a copy of the database, email it to me (remove the
words this is not real from my reply email address) and I'll take a look.
Something is being validated, and it's just not obvious to me via the
newsgroup posts.

--

Ken Snell
<MS ACCESS MVP>

Tony Williams said:
ken I've tried that and still get the error message. Can you look at my
earlier reply to you which read
"Ken the txtmonth field is formatted as date/time type short date. There is
no
other code running on the form. After tabbing from the txtmonth control the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and year of
txtmonth"
Any fresh ideas?
Tony

Ken Snell said:
Tony - see my reply to Dirk's post for the corrected code that you should
use.
 
K

Ken Snell [MVP]

Be sure to include directions as to which form and how to obtain the error.

--

Ken Snell
<MS ACCESS MVP>

Tony Williams said:
ken I've tried that and still get the error message. Can you look at my
earlier reply to you which read
"Ken the txtmonth field is formatted as date/time type short date. There is
no
other code running on the form. After tabbing from the txtmonth control the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and year of
txtmonth"
Any fresh ideas?
Tony

Ken Snell said:
Tony - see my reply to Dirk's post for the corrected code that you should
use.
 
T

Tony Williams

Ken the type is Date/Time, Short Date, Input Mask 00/00/00;0;_
Any help?
Tony
 
K

Ken Snell [MVP]

Aha!

The input mask expression you're using is requiring that the / characters be
stored with the date/time value; this results because you have a 0 after the
first semicolon. And, of course, a date/time field cannot store an
alphanumeric character.

Change your input mask expression to this:
00/00/00;;_

--

Ken Snell
<MS ACCESS MVP>
 
T

Tony Williams

Thanks for that suggestion Ken I can't make out you email address. if you
email it to me at my hotmail address (e-mail address removed) I don't use that one
normally, too much rubbish but it saves me giving you my real one and you
posting yours on here.
Tony
Ken Snell said:
If you'd like to zip up a copy of the database, email it to me (remove the
words this is not real from my reply email address) and I'll take a look.
Something is being validated, and it's just not obvious to me via the
newsgroup posts.

--

Ken Snell
<MS ACCESS MVP>

Tony Williams said:
ken I've tried that and still get the error message. Can you look at my
earlier reply to you which read
"Ken the txtmonth field is formatted as date/time type short date. There is
no
other code running on the form. After tabbing from the txtmonth control the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and year of
txtmonth"
Any fresh ideas?
Tony

Ken Snell said:
Tony - see my reply to Dirk's post for the corrected code that you should
use.

--

Ken Snell
<MS ACCESS MVP>

Hi Dirk I've used your code and got no msgbox so I changed it to
Cancel = DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#")) and
'Cancel = (DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#") = 0)) and this time the message box didn't popup
but
the
standard Access message about creating duplicate records did and no
validation error . I've tried mm/dd/yy and mm/dd/yyy and dd/mm/yy and
dd/mm/yy
Yes I want the message box if there IS a duplicate record.
Thanks for your help, this is driving me nuts!
Tony
Ken I've checked through all the validation controls and there are
none. Required property is set to No. Data Type is Short Date
and
 
K

Ken Snell [MVP]

see reply in other sub-thread. I think that will fix your problem.

--

Ken Snell
<MS ACCESS MVP>

Tony Williams said:
Thanks for that suggestion Ken I can't make out you email address. if you
email it to me at my hotmail address (e-mail address removed) I don't use that one
normally, too much rubbish but it saves me giving you my real one and you
posting yours on here.
Tony
Ken Snell said:
If you'd like to zip up a copy of the database, email it to me (remove the
words this is not real from my reply email address) and I'll take a look.
Something is being validated, and it's just not obvious to me via the
newsgroup posts.

--

Ken Snell
<MS ACCESS MVP>

Tony Williams said:
ken I've tried that and still get the error message. Can you look at my
earlier reply to you which read
"Ken the txtmonth field is formatted as date/time type short date.
There
is
no
other code running on the form. After tabbing from the txtmonth
control
the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and
year
of and Cancel
to
 
T

Tony Williams

Thanks ken but as you will see that still gave me an error message would you
still be willing to have a look at the database for me?
Tony
Ken Snell said:
see reply in other sub-thread. I think that will fix your problem.

--

Ken Snell
<MS ACCESS MVP>

Tony Williams said:
Thanks for that suggestion Ken I can't make out you email address. if you
email it to me at my hotmail address (e-mail address removed) I don't use that one
normally, too much rubbish but it saves me giving you my real one and you
posting yours on here.
Tony
Ken Snell said:
If you'd like to zip up a copy of the database, email it to me (remove the
words this is not real from my reply email address) and I'll take a look.
Something is being validated, and it's just not obvious to me via the
newsgroup posts.

--

Ken Snell
<MS ACCESS MVP>

ken I've tried that and still get the error message. Can you look at my
earlier reply to you which read
"Ken the txtmonth field is formatted as date/time type short date. There
is
no
other code running on the form. After tabbing from the txtmonth control
the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and
year
of
txtmonth"
Any fresh ideas?
Tony

Tony - see my reply to Dirk's post for the corrected code that you
should
use.

--

Ken Snell
<MS ACCESS MVP>

Hi Dirk I've used your code and got no msgbox so I changed it to
Cancel = DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#")) and
'Cancel = (DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#") = 0)) and this time the message box didn't
popup
but
the
standard Access message about creating duplicate records did and no
validation error . I've tried mm/dd/yy and mm/dd/yyy and
dd/mm/yy
and
dd/mm/yy
Yes I want the message box if there IS a duplicate record.
Thanks for your help, this is driving me nuts!
Tony
Ken I've checked through all the validation controls and
there
are
none. Required property is set to No. Data Type is Short
Date
and
I
am entering say 30/06/04.
When I enter a value in the txtmonth control on my form as say
30/06/04 I want to check that that date has not been input before.
The txtmonth field in my table tblmonth is a key field and indexed
with no duplicates. Does that help?

I don't know if this affects what's going on or not, but if
you
are
entering your dates in dd/mm/yy format, you may want to change the
code
to

Cancel = (DCount("*", "tblmonth", "txtMonth = " & _
Format(Me.txtMonth, "\#mm/dd/yyyy\#") = 0)

But I'm confused, because this looks to me like it will set Cancel
to
True if the date in Me.txtMonth is *not* found in the table, where
your
MsgBox seemed to imply your intention to cancel if it *is* found.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
T

Tony Williams

Ken PS would you email me your email address to (e-mail address removed)
Tony
Ken Snell said:
see reply in other sub-thread. I think that will fix your problem.

--

Ken Snell
<MS ACCESS MVP>

Tony Williams said:
Thanks for that suggestion Ken I can't make out you email address. if you
email it to me at my hotmail address (e-mail address removed) I don't use that one
normally, too much rubbish but it saves me giving you my real one and you
posting yours on here.
Tony
Ken Snell said:
If you'd like to zip up a copy of the database, email it to me (remove the
words this is not real from my reply email address) and I'll take a look.
Something is being validated, and it's just not obvious to me via the
newsgroup posts.

--

Ken Snell
<MS ACCESS MVP>

ken I've tried that and still get the error message. Can you look at my
earlier reply to you which read
"Ken the txtmonth field is formatted as date/time type short date. There
is
no
other code running on the form. After tabbing from the txtmonth control
the
user goes to a calculated control called txtmonthlabel with the formula
=Format([txtMonth],"mmmm yyyy"), is that the problem? The change of format
in that calculation. I want txtmonthlabel to be just the month and
year
of
txtmonth"
Any fresh ideas?
Tony

Tony - see my reply to Dirk's post for the corrected code that you
should
use.

--

Ken Snell
<MS ACCESS MVP>

Hi Dirk I've used your code and got no msgbox so I changed it to
Cancel = DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#")) and
'Cancel = (DCount("*", "tblmonth", "txtMonth = " & Format(Me.txtMonth,
"\#dd/mm/yy\#") = 0)) and this time the message box didn't
popup
but
the
standard Access message about creating duplicate records did and no
validation error . I've tried mm/dd/yy and mm/dd/yyy and
dd/mm/yy
and
dd/mm/yy
Yes I want the message box if there IS a duplicate record.
Thanks for your help, this is driving me nuts!
Tony
Ken I've checked through all the validation controls and
there
are
none. Required property is set to No. Data Type is Short
Date
and
I
am entering say 30/06/04.
When I enter a value in the txtmonth control on my form as say
30/06/04 I want to check that that date has not been input before.
The txtmonth field in my table tblmonth is a key field and indexed
with no duplicates. Does that help?

I don't know if this affects what's going on or not, but if
you
are
entering your dates in dd/mm/yy format, you may want to change the
code
to

Cancel = (DCount("*", "tblmonth", "txtMonth = " & _
Format(Me.txtMonth, "\#mm/dd/yyyy\#") = 0)

But I'm confused, because this looks to me like it will set Cancel
to
True if the date in Me.txtMonth is *not* found in the table, where
your
MsgBox seemed to imply your intention to cancel if it *is* found.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top