By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,451 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Access 97/2000 conversion problems

P: n/a
First of all I have been working with Access 97 and this morning the owner
of the business phoned me to inform me that he had updated to Access 2000
and parts of my forms would not work anymore.

On my form I have a field "Expiry Date".When I put the following into the
AfterUpdate field, it adds 183 days to the current date, and displays it in
the "Expiry Date" field.

Me.ExpDate = DateAdd("d", 183, Me.Date)

Then I have a "Lot Number" that only exists on the form. When the date is
entered it produces a whole number that comes up in the "Lot Number" field.
This is done by pasting the following formula in the in the "Control
Source" field on the Data Tab of the "Lot Number" field.

=Right(Year([Date]),1) & Right("000" & Format([Date],"y"),3)

What changes do I have to make to make the above Access 2000 compatible.

Any help would be appreciated.

Thank you,

--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"Date" is a reserved word -- the builtin Date function returns the current
date -- and it is not good to use reserved words for the name of a Field, or
a Control. It is also not good to use the name of the Field for the Control
in which it is displayed, because it is confusing to us whether it is the
Field or the Control that is being referenced.

Access 2000 is "stricter" about some things of this kind than Access 97 is.
I'd guess if you changed these things, it would be a start. At least the
issue wouldn't be confused with some things that can, but don't always,
cause a problem.

Also, be sure you have a clean compile before you convert, and be sure you
are running all the Service Packs for Access 2000 (there are now three SPs
and, perhaps, a few hot-fixes). A trip to Office Update would be a good
idea, just to make sure... it's an option on the Windows Update screen that
you see when you click Windows Update in the Start Menu.

Larry Linson
Microsoft Access MVP
"William Bradley" <br******@magma.ca> wrote in message
news:QI********************@magma.ca...
First of all I have been working with Access 97 and this morning the owner
of the business phoned me to inform me that he had updated to Access 2000
and parts of my forms would not work anymore.

On my form I have a field "Expiry Date".When I put the following into the
AfterUpdate field, it adds 183 days to the current date, and displays it in the "Expiry Date" field.

Me.ExpDate = DateAdd("d", 183, Me.Date)

Then I have a "Lot Number" that only exists on the form. When the date is
entered it produces a whole number that comes up in the "Lot Number" field. This is done by pasting the following formula in the in the "Control
Source" field on the Data Tab of the "Lot Number" field.

=Right(Year([Date]),1) & Right("000" & Format([Date],"y"),3)

What changes do I have to make to make the above Access 2000 compatible.

Any help would be appreciated.

Thank you,

--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

Nov 12 '05 #2

P: n/a
William Bradley wrote:
First of all I have been working with Access 97 and this morning the
owner of the business phoned me to inform me that he had updated to
Access 2000 and parts of my forms would not work anymore.

On my form I have a field "Expiry Date".When I put the following into
the AfterUpdate field, it adds 183 days to the current date, and
displays it in the "Expiry Date" field.

Me.ExpDate = DateAdd("d", 183, Me.Date)
I think you should use the "correct" syntax... the "dot" operator is
used for methods/properties of the object eg. Me.Visible. For fields
use an apostraphy.

Me![ExpDate] = DateAdd("d', 183, Me![Date])

Also 'date' is a reserved word. Try calling your field something else.
Then I have a "Lot Number" that only exists on the form. When the
date is entered it produces a whole number that comes up in the "Lot
Number" field. This is done by pasting the following formula in the
in the "Control Source" field on the Data Tab of the "Lot Number"
field.

=Right(Year([Date]),1) & Right("000" & Format([Date],"y"),3)

What changes do I have to make to make the above Access 2000
compatible.

Any help would be appreciated.

Thank you,


--
regards,

Bradley
Nov 12 '05 #3

P: n/a
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:40******@nexus.comcen.com.au:
William Bradley wrote:
First of all I have been working with Access 97 and this morning the
owner of the business phoned me to inform me that he had updated to
Access 2000 and parts of my forms would not work anymore.

On my form I have a field "Expiry Date".When I put the following into
the AfterUpdate field, it adds 183 days to the current date, and
displays it in the "Expiry Date" field.

Me.ExpDate = DateAdd("d", 183, Me.Date)


I think you should use the "correct" syntax... the "dot" operator is
used for methods/properties of the object eg. Me.Visible. For fields
use an apostraphy.

Me![ExpDate] = DateAdd("d', 183, Me![Date])


Don't you mean "Catastrophe"?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #4

P: n/a
Lyle Fairfield wrote:
"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in
news:40******@nexus.comcen.com.au:
William Bradley wrote:
First of all I have been working with Access 97 and this morning the
owner of the business phoned me to inform me that he had updated to
Access 2000 and parts of my forms would not work anymore.

On my form I have a field "Expiry Date".When I put the following
into the AfterUpdate field, it adds 183 days to the current date,
and displays it in the "Expiry Date" field.

Me.ExpDate = DateAdd("d", 183, Me.Date)


I think you should use the "correct" syntax... the "dot" operator is
used for methods/properties of the object eg. Me.Visible. For fields
use an apostraphy.

Me![ExpDate] = DateAdd("d', 183, Me![Date])


Don't you mean "Catastrophe"?


hehe ;)

--
regards,

Bradley
Nov 12 '05 #5

P: n/a
> I think you should use the "correct" syntax... the "dot" operator is
used for methods/properties of the object eg. Me.Visible. For fields
Which is why 'dot' can be used for the object properties that refer
to controls on the report, and to fields in the bound recordsource.

An advantage of using the form properties, instead of using members
from the fields collection or the controls collection, is that you
get 'autocomplete'. Another, more important advantage, is that you
get compile time syntax checking for form properties, which you do
not get for membership of collections.

Disadvantages of using field properties of a form are (1) 'Field'
properties are not created correctly when using LoadFromText,
(2) 'Field' properties are not available for late-bound recordsources.

I am not aware of any disadvantages to using 'control' properties.

I use the form properties when referring to a control on a form,
and use the default collection when referring to a field from the
recordsource (this gives me an additional visual indication for
when I am using a field without a bound control, and is tolerant
to forms that are unbound at compile time).
When I use the default collection of the form (frm!....) I
can refer both to controls and to unbound fields. There is no
overlap if I use the fields and controls collections (frm!controls!...
frm.recordsetclone.fields!...) By what magic is this possible?
I would think that perhaps using the default collection is a
kludge that should be avoided ????

(david)

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:40******@nexus.comcen.com.au... William Bradley wrote:
First of all I have been working with Access 97 and this morning the
owner of the business phoned me to inform me that he had updated to
Access 2000 and parts of my forms would not work anymore.

On my form I have a field "Expiry Date".When I put the following into
the AfterUpdate field, it adds 183 days to the current date, and
displays it in the "Expiry Date" field.

Me.ExpDate = DateAdd("d", 183, Me.Date)


I think you should use the "correct" syntax... the "dot" operator is
used for methods/properties of the object eg. Me.Visible. For fields
use an apostraphy.

Me![ExpDate] = DateAdd("d', 183, Me![Date])

Also 'date' is a reserved word. Try calling your field something else.
Then I have a "Lot Number" that only exists on the form. When the
date is entered it produces a whole number that comes up in the "Lot
Number" field. This is done by pasting the following formula in the
in the "Control Source" field on the Data Tab of the "Lot Number"
field.

=Right(Year([Date]),1) & Right("000" & Format([Date],"y"),3)

What changes do I have to make to make the above Access 2000
compatible.

Any help would be appreciated.

Thank you,


--
regards,

Bradley

Nov 12 '05 #6

P: n/a
Larry Linson wrote:

Thank you to everyone who responded to my problem. I finished by creating a
fresh DB in Access 2000. It was not as though I had a big system to deal
with. The main reason was that I could not get a clean compile when 2000
was trying to update 97's DB. Once I did that, took not of what was told me
about the date being a reserved word, everything began to work as expected.

Thanks again, Bill.
"Date" is a reserved word -- the builtin Date function returns the current
date -- and it is not good to use reserved words for the name of a Field,
or a Control. It is also not good to use the name of the Field for the
Control in which it is displayed, because it is confusing to us whether it
is the Field or the Control that is being referenced.

William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.
Nov 12 '05 #7

P: n/a
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:40***********************@news.syd.swiftdsl.c om.au:
I am not aware of any disadvantages to using 'control' properties.


If you mean referring to controls using the . operator, here are
some:

1. you are depending on the behavior of something over which you
have no control: VBA creates a hidden wrapper around each controls
that allows you to treat them as properties/members of the form.

2. in certain circumstances, the dot operator can lead to corruption
of the form. I've lost track of what those circumstances are,
though. Steve Jorgensen experienced one case of it, I think.

3. code written using the ! operator is easier to read, as it tells
you you're referring to a control or field.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #8

P: n/a
> 1. you are depending on the behaviour of something over which you
have no control: VBA creates a hidden wrapper around each controls
Whereas when I refer to the 'visible' property of a form
I am depending on the behaviour of something over which I
do have control? And when I refer to the magic default
collection of the form I am depending on the behaviour of
something over which I do have control?
2. in certain circumstances, the dot operator can lead to corruption
of the form. I've lost track of what those circumstances are,
though. Steve Jorgensen experienced one case of it, I think.
Steve experienced a lot of corruption when he started using
Access 2000: Later, he didn't get as much corruption. The
same thing happened to me... I stopped using MDE libraries:
I couldn't get them to work reliably at all. He stopped using
form properties. Now, we have all our libraries back again,
and we continue to use dynamically created form properties as
well as public form variables, Let, and Get.

AFAIK, Steve never posted 'circumstances' that led to corruption,
except that developing VBA in Access 2000 can lead to project
corruption, and project corruption can lead to database
corruption. Always close and save after making changes: never
modify a form opened in view mode: always decompile after
setting break points: close, backup and decompile often.
Steve has gone on to write elaborate code to replace the
functionality of form properties: it would not be appropriate
to advise people to just abandon the use of form properties.
3. code written using the ! operator is easier to read, as it
tells you you're referring to a control or field.
Code written using the ! operator is more difficult to read,
as it obscures the difference between controls and fields.

(david)



"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn*********************************@24.168.12 8.86... "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:40***********************@news.syd.swiftdsl.c om.au:
I am not aware of any disadvantages to using 'control' properties.


If you mean referring to controls using the . operator, here are
some:

1. you are depending on the behavior of something over which you
have no control: VBA creates a hidden wrapper around each controls
that allows you to treat them as properties/members of the form.

2. in certain circumstances, the dot operator can lead to corruption
of the form. I've lost track of what those circumstances are,
though. Steve Jorgensen experienced one case of it, I think.

3. code written using the ! operator is easier to read, as it tells
you you're referring to a control or field.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.