Use of DCount

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

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 [MVP]]

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

--

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.

 

[Tony Williams]

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

 

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

 

[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

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

 

[Ken Snell [MVP]]

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

--

Ken Snell
<MS ACCESS MVP>

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

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 and Cancel
to

 

[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

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

--

Ken Snell
<MS ACCESS MVP>

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

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)

 

[Tony Williams]

Ken PS would you email me your email address to (e-mail address removed)
Tony

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

--

Ken Snell
<MS ACCESS MVP>

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

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)