AAARGH!!! Help needed with misbehaving form link criteria

[David Anderson]

I'm tearing my hair out over a bug that has just arisen in one of my Access
2000 forms - at least I would be, if I had any hair left.

I have a bound continuous form of payment records (called PaymentsList) with
a command button that opens a modal popup form (called EditPayments) for
editing a specific payment. The standard Access 2000 command button wizard
click event code is as follows,

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EditPayments"

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria


Up to three days ago, this worked fine. I could create a new payment and
edit it immediately. Yesterday, everything went pear-shaped. Any old record
in my Payments table (saved 3 or more days ago) can be edited but no newly
created record is found via the [PaymentDate] linking criterion. The popup
recordset has a RecordCount of zero why I try to edit a recent record. This
behaviour is consistent, i.e. all old payment records can be edited but none
of the new ones. I can't remember making any recent design changes of
potential relevance to this problem (but my memory is fallible).

The underlying query of my PaymentsList form is quite happy to find a newly
created payment record using a specified payment date, so why is my
DoCmd.openForm process failing? How can it find one record but not another,
when they both have the same type of information stored in the Payments
table?

Suspecting some form of database corruption, I have created new versions of
both my front and back end, importing all the tables, etc, from the original
versions. I have tried deleting all the indexes from my Payments table
definition, saving it, and then recreating the indexes. So far, nothing has
worked.

I'm totally flummoxed. Any guidance on this would be greatly appreciated!

David

 

[ruralguy via AccessMonster.com]

As a test, put:
If Me.Dirty Then Me.Dirty = False
before you open the PopUp form.

I'm tearing my hair out over a bug that has just arisen in one of my Access
2000 forms - at least I would be, if I had any hair left.

I have a bound continuous form of payment records (called PaymentsList) with
a command button that opens a modal popup form (called EditPayments) for
editing a specific payment. The standard Access 2000 command button wizard
click event code is as follows,

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EditPayments"

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria

Up to three days ago, this worked fine. I could create a new payment and
edit it immediately. Yesterday, everything went pear-shaped. Any old record
in my Payments table (saved 3 or more days ago) can be edited but no newly
created record is found via the [PaymentDate] linking criterion. The popup
recordset has a RecordCount of zero why I try to edit a recent record. This
behaviour is consistent, i.e. all old payment records can be edited but none
of the new ones. I can't remember making any recent design changes of
potential relevance to this problem (but my memory is fallible).

The underlying query of my PaymentsList form is quite happy to find a newly
created payment record using a specified payment date, so why is my
DoCmd.openForm process failing? How can it find one record but not another,
when they both have the same type of information stored in the Payments
table?

Suspecting some form of database corruption, I have created new versions of
both my front and back end, importing all the tables, etc, from the original
versions. I have tried deleting all the indexes from my Payments table
definition, saving it, and then recreating the indexes. So far, nothing has
worked.

I'm totally flummoxed. Any guidance on this would be greatly appreciated!

David

 

[David Anderson]

Hi Allan,
I made the change you suggested, but (as I expected) it made no difference.
I understand your reasoning, but the problem is not associated with records
not yet fully saved. Records saved yesterday are still not found by my
EditPayments form. Only those saved 3 or more days ago can be edited.

David

 

[ruralguy via AccessMonster.com]

Hi David,
Hmm... well it was worth a try. How about looking at the WhereCondition
string before it is used?
stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
MsgBox "[" & stLinkCriteria & "]"
DoCmd.openForm stDocName, , , stLinkCriteria

You might also check if Filters are allowed on the next form since that is
what the WhereCondition argument imposes.

 

[David Anderson]

I checked the stLinkCriteria value before (and I've just rechecked it). It
looks fine, and is identical (apart from the date itself) for both working
and non-working records. Filters are not allowed on the popup form, but I
don't think that is relevant. If it were an issue, surely it would have
prevented any record from being displayed rather than just the older
records? I'm no expert, but I think that the filter property controls
whether or not you can filter the form's contents once it has opened. In any
case, it makes no difference if I change it.

Keep the ideas coming. There has got to be a solution!

David

 

[ruralguy via AccessMonster.com]

Have you tried opening the EditPayments form on its own to see what records
it can see?

I checked the stLinkCriteria value before (and I've just rechecked it). It
looks fine, and is identical (apart from the date itself) for both working
and non-working records. Filters are not allowed on the popup form, but I
don't think that is relevant. If it were an issue, surely it would have
prevented any record from being displayed rather than just the older
records? I'm no expert, but I think that the filter property controls
whether or not you can filter the form's contents once it has opened. In any
case, it makes no difference if I change it.

Keep the ideas coming. There has got to be a solution!

David

Hi David,
Hmm... well it was worth a try. How about looking at the WhereCondition

[quoted text clipped - 11 lines]

Message posted via AccessMonster.com

 

[David Anderson]

Yup. Run separately, EditPayments will happily display all the records in
the Payments table.

 

[ruralguy via AccessMonster.com]

Well David, sorry but I'm fresh out of ideas! I'll let it stew in the back
of my mind for a while and post back if I think of something.

 

[David Anderson]

After recreating a simple test version of every single element of my problem
from the Payments table onwards in a brand new database, and still getting
exactly the same results, I tried a new tack. Instead of using PaymentDate
(sometimes in conjunction with other fields) as my link I used another
field - and all was OK. I find it hard to believe, but there appears to be
an issue in Access 2000 with using dates in what is essentially an SQL WHERE
clause.

Has anyone else experienced this type of behaviour? I struggle to understand
why such a simple process is failing.

David

 

[Stuart McCall]

After recreating a simple test version of every single element of my
problem from the Payments table onwards in a brand new database, and still
getting exactly the same results, I tried a new tack. Instead of using
PaymentDate (sometimes in conjunction with other fields) as my link I used
another field - and all was OK. I find it hard to believe, but there
appears to be an issue in Access 2000 with using dates in what is
essentially an SQL WHERE clause.

Has anyone else experienced this type of behaviour? I struggle to
understand why such a simple process is failing.

David

I'm tearing my hair out over a bug that has just arisen in one of my
Access 2000 forms - at least I would be, if I had any hair left.

I have a bound continuous form of payment records (called PaymentsList)
with a command button that opens a modal popup form (called EditPayments)
for editing a specific payment. The standard Access 2000 command button
wizard click event code is as follows,

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EditPayments"

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria


Up to three days ago, this worked fine. I could create a new payment and
edit it immediately. Yesterday, everything went pear-shaped. Any old
record in my Payments table (saved 3 or more days ago) can be edited but
no newly created record is found via the [PaymentDate] linking criterion.
The popup recordset has a RecordCount of zero why I try to edit a recent
record. This behaviour is consistent, i.e. all old payment records can be
edited but none of the new ones. I can't remember making any recent
design changes of potential relevance to this problem (but my memory is
fallible).

The underlying query of my PaymentsList form is quite happy to find a
newly created payment record using a specified payment date, so why is my
DoCmd.openForm process failing? How can it find one record but not
another, when they both have the same type of information stored in the
Payments table?

Suspecting some form of database corruption, I have created new versions
of both my front and back end, importing all the tables, etc, from the
original versions. I have tried deleting all the indexes from my Payments
table definition, saving it, and then recreating the indexes. So far,
nothing has worked.

I'm totally flummoxed. Any guidance on this would be greatly appreciated!

David

Are you absolutely sure PaymentDate is a date/time field? Also, does
Me![PaymentDate] contain the correct info? If yes to both questions, I'm as
flummoxed as you. I have almost countless exact duplicates of this code, all
of which work fine...

 

[ruralguy via AccessMonster.com]

Interesting David! I'll bet this is done but have you applied all of the
updates?

After recreating a simple test version of every single element of my problem
from the Payments table onwards in a brand new database, and still getting
exactly the same results, I tried a new tack. Instead of using PaymentDate
(sometimes in conjunction with other fields) as my link I used another
field - and all was OK. I find it hard to believe, but there appears to be
an issue in Access 2000 with using dates in what is essentially an SQL WHERE
clause.

Has anyone else experienced this type of behaviour? I struggle to understand
why such a simple process is failing.

David

I'm tearing my hair out over a bug that has just arisen in one of my
Access 2000 forms - at least I would be, if I had any hair left.

[quoted text clipped - 37 lines]

David

 

[Albert D. Kallal]

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria

Your criteria statement is wrong, and you just been lucky it works.

What likely changed on your computer is a date/time settings.

When you build sql in code, YOU MUST USE USA FORMAT FOR THE ALL OF THE
DATES!!!

So, the others here have been leading you on a wild goose chase.


You need to use:

stLinkCriteria = "[PaymentDate]=" & _
"#" & format(Me![PaymentDate],"mm/dd/yyyy") & "#"

Hence, you must *always* force the format to usa date format, regardless of
your regional settings.

the reason why your code is not working, even after re-installing ms-access
is because it is your date format that causing this problem...

the other poster is apparently NOT aware that all date strings in code MUST
be forced to usa format....

Give the above a try...

 

[David Anderson]

Albert,
The Regional and Language Options in the Windows XP Control Panel on my PC
are set to English (United Kingdom). This setting has never been changed.

Since the USA is probably the only major country on the planet using the
somewhat illogical m-d-y sequence (as opposed to d-m-y, or even y-d-m), my
first reaction to your post was to think that Microsoft needs a good kicking
for implementing such a chauvinistic US-centric policy, and an even bigger
kicking for not documenting it in all the appropriate places. However,
making the simple code change you recommend has not resolved my
problem.......

Where is this USA date format restriction documented? So far, I can't find
anything in the VBA help.

David

stDocName = "EditPayments"

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria

Your criteria statement is wrong, and you just been lucky it works.

What likely changed on your computer is a date/time settings.

When you build sql in code, YOU MUST USE USA FORMAT FOR THE ALL OF THE
DATES!!!

So, the others here have been leading you on a wild goose chase.


You need to use:

stLinkCriteria = "[PaymentDate]=" & _
"#" & format(Me![PaymentDate],"mm/dd/yyyy") & "#"

Hence, you must *always* force the format to usa date format, regardless
of your regional settings.

the reason why your code is not working, even after re-installing
ms-access is because it is your date format that causing this problem...

the other poster is apparently NOT aware that all date strings in code
MUST be forced to usa format....

Give the above a try...

 

[Pieter Wijnen]

Not entirely true
I always succesfully use the military format
ie
yyyy-mm-dd

stLinkCriteria = "[PaymentDate]=" &
format(Me![PaymentDate],"\#yyyy-mm-dd\#")

HtH

Pieter

Albert,
The Regional and Language Options in the Windows XP Control Panel on my PC
are set to English (United Kingdom). This setting has never been changed.

Since the USA is probably the only major country on the planet using the
somewhat illogical m-d-y sequence (as opposed to d-m-y, or even y-d-m), my
first reaction to your post was to think that Microsoft needs a good
kicking for implementing such a chauvinistic US-centric policy, and an
even bigger kicking for not documenting it in all the appropriate places.
However, making the simple code change you recommend has not resolved my
problem.......

Where is this USA date format restriction documented? So far, I can't find
anything in the VBA help.

David

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria

Your criteria statement is wrong, and you just been lucky it works.

What likely changed on your computer is a date/time settings.

When you build sql in code, YOU MUST USE USA FORMAT FOR THE ALL OF THE
DATES!!!

So, the others here have been leading you on a wild goose chase.


You need to use:

stLinkCriteria = "[PaymentDate]=" & _
"#" & format(Me![PaymentDate],"mm/dd/yyyy") & "#"

Hence, you must *always* force the format to usa date format, regardless
of your regional settings.

the reason why your code is not working, even after re-installing
ms-access is because it is your date format that causing this problem...

the other poster is apparently NOT aware that all date strings in code
MUST be forced to usa format....

Give the above a try...

 

[ruralguy via AccessMonster.com]

Here are two additional links on the International Date issue:
http://www.mvps.org/access/datetime/date0005.htm
http://allenbrowne.com/ser-36.html

...but I'm pretty sure that is not the problem that David is experiencing.

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria

Your criteria statement is wrong, and you just been lucky it works.

What likely changed on your computer is a date/time settings.

When you build sql in code, YOU MUST USE USA FORMAT FOR THE ALL OF THE
DATES!!!

So, the others here have been leading you on a wild goose chase.

You need to use:

stLinkCriteria = "[PaymentDate]=" & _
"#" & format(Me![PaymentDate],"mm/dd/yyyy") & "#"

Hence, you must *always* force the format to usa date format, regardless of
your regional settings.

the reason why your code is not working, even after re-installing ms-access
is because it is your date format that causing this problem...

the other poster is apparently NOT aware that all date strings in code MUST
be forced to usa format....

Give the above a try...

 

[David Anderson]

I note from both the Allen Browne link and Pieter's recent post that
backslashes are also required when reformatting a date (Albert didn't
mention those). However, even when I add backslashes, the criteria statement
on my form still does not find one of my newer records via the date. I have
now modified my app to link on a different field and all is now well - but
I'd still like to get to the bottom of this date linking problem.

David

 

[ruralguy via AccessMonster.com]

The back slashes are only necessary if you are using the Format() function to
insert them. Albert already had them in the string. Any chance you would
care to send me a stripped down copy of your db that demonstrates the problem
so I could look at it? If so, I'll supply an email addy.

I note from both the Allen Browne link and Pieter's recent post that
backslashes are also required when reformatting a date (Albert didn't
mention those). However, even when I add backslashes, the criteria statement
on my form still does not find one of my newer records via the date. I have
now modified my app to link on a different field and all is now well - but
I'd still like to get to the bottom of this date linking problem.

David

Here are two additional links on the International Date issue:
http://www.mvps.org/access/datetime/date0005.htm

[quoted text clipped - 7 lines]

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

That's insert the octothorps "#". Missed that on the previous post.

I note from both the Allen Browne link and Pieter's recent post that
backslashes are also required when reformatting a date (Albert didn't
mention those). However, even when I add backslashes, the criteria statement
on my form still does not find one of my newer records via the date. I have
now modified my app to link on a different field and all is now well - but
I'd still like to get to the bottom of this date linking problem.

David

Here are two additional links on the International Date issue:
http://www.mvps.org/access/datetime/date0005.htm

[quoted text clipped - 7 lines]

Message posted via AccessMonster.com

 

[David Anderson]

Allan,
I'll be happy to provide a stripped down Access 2000 MDB file that
demonstrates my date linking problem. Thanks for the kind offer. Thanks also
for expanding my vocabulary with the word 'octothorp'!! That's not a word we
use on this side of the pond (it doesn't even get a mention in my
heavyweight two-volume edition of the Oxford English Dictionary). I've only
ever seen it referred to as the hash symbol before, but octothorp certainly
sounds a bit classier.

Albert had both octothorps and forward slashes in his code sample but he
didn't have backslashes. To quote your Allan Browne link, "Unfortunately,
Format() replaces the slashes with the date separator character defined in
Control Panel | Regional Settings, so you must specify literal slashes in
the format string by preceding the slash with backslashes". For example, his
code sample included the following: Format(Me.StartDate, "mm\/dd\/yyyy").

I tried that method, but it didn't work for me. In any case, my UK regional
settings use the same forward slash date separator as US dates so it
shouldn't make any difference in my case, as indeed I found.

David

 

[Albert D. Kallal]

Albert,
The Regional and Language Options in the Windows XP Control Panel on my PC
are set to English (United Kingdom). This setting has never been changed.

Ok, no problem. It was a educational guess on my part. There not many other
reasons I can think of as to why your date "where" clause query does not
work.

I suppose it possible that you using "now()" for a default of date when you
should be using "date"

(now includes a time competent..and thus date match only queries will fail -
you have to include the a time part).

Where is this USA date format restriction documented? So far, I can't find
anything in the VBA help.

It has noting to do with VBA. It has to do with he choice of the database
engine. If you use Oracle with ms-access, or MySql, or ms-sql, then things
might be different.

As the other poster mentioned, you can usually use the "international"
format (iso date format) and it will work fine. However, if you going to use
date format to "cast" the date string to ISO format, you might as use the
native format (it been that way for more then 15+ years). But, to be
sticky, you can use the iso format in that internal code (not much advantage
to doing so however).

And, yes, while this is NOT anything to do with VBA, it is in the VBA help:

If you using a2003 help, in the code editor, if you go:

date format

The first hit is:

Use international Date formats in SQL statements

The text from the above says:
<quote>
You must use English (United States) date formats in SQL statements in
Visual Basic. However, you can use international date formats in the query
design grid.

<quote>

At this point, I can only figure your problem is a date format issue (but,
you seem to say otherwise). The next thing I would check is if date defaults
are "now" in place of "date".

also, in your code, you could as test go:

stLinkCriteria = "[PaymentDate] = " & "#" & _
format(Me![PaymentDate],"mm/dd/yyyy") & "#"

As mentoend, for *furhter* caution, you can add in the "\"

so, try:

stLinkCriteria = "[PaymentDate] = " & "#" & _
format(Me![PaymentDate],"mm/\dd/\yyyy") & "#"
debug.print strLinkCriteria

Add the debug.print. When you run the code...if you go back into the
debugger, you be able to
cut/paste the actual "where" clause string. Perahps posting that resutling
string here might shead some results.

As mentioned, also check your defaults used in the form, or even code....DO
NOT use now(), but use date.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

David

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria

Your criteria statement is wrong, and you just been lucky it works.

What likely changed on your computer is a date/time settings.

When you build sql in code, YOU MUST USE USA FORMAT FOR THE ALL OF THE
DATES!!!

So, the others here have been leading you on a wild goose chase.


You need to use:

stLinkCriteria = "[PaymentDate]=" & _
"#" & format(Me![PaymentDate],"mm/dd/yyyy") & "#"

Hence, you must *always* force the format to usa date format, regardless
of your regional settings.

the reason why your code is not working, even after re-installing
ms-access is because it is your date format that causing this problem...

the other poster is apparently NOT aware that all date strings in code
MUST be forced to usa format....

Give the above a try...