Access 97/2000 conversion problems

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.

re: Access 97/2000 conversion problems

"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" <bradleyw@magma.ca> wrote in message
news:QIqdnb6lWdqXCRvdRVn-tw@magma.ca...[color=blue]
> 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[/color]
in[color=blue]
> 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"[/color]
field.[color=blue]
> 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.[/color]

re: Access 97/2000 conversion problems

William Bradley wrote:[color=blue]
> 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)[/color]

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.
[color=blue]
> 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,[/color]

--
regards,

Bradley

re: Access 97/2000 conversion problems

"Bradley" <bradley@REMOVETHIScomcen.com.au> wrote in
news:40871c0d@nexus.comcen.com.au:
[color=blue]
> William Bradley wrote:[color=green]
>> 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)[/color]
>
> 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])[/color]

Don't you mean "Catastrophe"?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

re: Access 97/2000 conversion problems

Lyle Fairfield wrote:[color=blue]
> "Bradley" <bradley@REMOVETHIScomcen.com.au> wrote in
> news:40871c0d@nexus.comcen.com.au:
>[color=green]
>> William Bradley wrote:[color=darkred]
>>> 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)[/color]
>>
>> 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])[/color]
>
> Don't you mean "Catastrophe"?[/color]

hehe ;)

--
regards,

Bradley

re: Access 97/2000 conversion problems

> I think you should use the "correct" syntax... the "dot" operator is[color=blue]
> used for methods/properties of the object eg. Me.Visible. For fields[/color]

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" <bradley@REMOVETHIScomcen.com.au> wrote in message
news:40871c0d@nexus.comcen.com.au...[color=blue]
> William Bradley wrote:[color=green]
> > 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)[/color]
>
> 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.
>[color=green]
> > 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,[/color]
>
> --
> regards,
>
> Bradley
>
>[/color]

re: Access 97/2000 conversion problems

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.
[color=blue]
> "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.[/color]


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

re: Access 97/2000 conversion problems

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:40887449$0$27649$61ce578d@news.syd.swiftdsl.c om.au:
[color=blue]
> I am not aware of any disadvantages to using 'control' properties.[/color]

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

re: Access 97/2000 conversion problems

> 1. you are depending on the behaviour of something over which you[color=blue]
> have no control: VBA creates a hidden wrapper around each controls[/color]

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?
[color=blue]
> 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.[/color]

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.
[color=blue]
> 3. code written using the ! operator is easier to read, as it
> tells you you're referring to a control or field.[/color]

Code written using the ! operator is more difficult to read,
as it obscures the difference between controls and fields.

(david)







"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns94D3E54B4411dfentonbwaynetinvali@24.168.12 8.86...[color=blue]
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
> news:40887449$0$27649$61ce578d@news.syd.swiftdsl.c om.au:
>[color=green]
> > I am not aware of any disadvantages to using 'control' properties.[/color]
>
> 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[/color]