Explicity Referencing Field on Form Failing in Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following is used in a control souce in an Access 2000 Application and
works fine:
=[TotalAmnt]-nz([Forms]![Add Expenses Record]![ExpenseLinSub]!LineTotal,0)
In Access 2003 the explicit reference alway returns NULL. This is also true
when similar syntax is used in VB code in other areas of the system. The
migration notes state that we shouldn't have to change anything to migrate.
I'm told that Access 2003 has been loaded as it comes at the user site. Is
there a patch to fix this or can the above statement be recoded in some way?
Where possible I've replaced explicit references with the Me. syntax which
seems to resolve the issue - but there are places where the app uses data
from other forms or sub-forms where I'm not sure what I can do. Any advice
would be much appreciated!
 
Try:
=[TotalAmnt]-nz([Forms]![Add Expenses
Record]![ExpenseLinSub].Form!LineTotal,0)

If the ".Form" bit is new, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

As you discovered, Access 2003 does seem to be more finicky about this than
2000 was.
 
Thanks Allen. Your document on referring to subforms was excellent too!

Do you know if there is anything else 2003 is finicky about? I'm still on
2000 but the user wants to upgrade to 2003 so it'd be useful if I had a head
start on any issues rather than finding out via the user testing. The
application doesn't use data access pages or macros but there's
relationships, forms, queries reports and a fair bit of VB including
dynamically created SQL. Unfortunately the migration notes on this site say
there's no need for any changes which rather put me off my guard.

Allen Browne said:
Try:
=[TotalAmnt]-nz([Forms]![Add Expenses
Record]![ExpenseLinSub].Form!LineTotal,0)

If the ".Form" bit is new, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

As you discovered, Access 2003 does seem to be more finicky about this than
2000 was.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John Pritchard said:
The following is used in a control souce in an Access 2000 Application and
works fine:
=[TotalAmnt]-nz([Forms]![Add Expenses Record]![ExpenseLinSub]!LineTotal,0)
In Access 2003 the explicit reference alway returns NULL. This is also
true
when similar syntax is used in VB code in other areas of the system. The
migration notes state that we shouldn't have to change anything to
migrate.
I'm told that Access 2003 has been loaded as it comes at the user site. Is
there a patch to fix this or can the above statement be recoded in some
way?
Where possible I've replaced explicit references with the Me. syntax which
seems to resolve the issue - but there are places where the app uses data
from other forms or sub-forms where I'm not sure what I can do. Any advice
would be much appreciated!
Click to expand...
Click to expand...
 
The other issue that A2003 seems finicky about is network printers. Some
users find they need to reinstall the printer drivers before A2003 can find
the printer, whereas previous versions and other products in the Office 2003
work with the printer okay.

In this article:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html
issue #3 under "development" relates to an issue with subforms that started
in A2002 and contiues in A2003.

Other than that, A2003 is generally very stable to work with.

There is no difference with the SQL statements: both call the same JET 4
engine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John Pritchard said:
Thanks Allen. Your document on referring to subforms was excellent too!

Do you know if there is anything else 2003 is finicky about? I'm still on
2000 but the user wants to upgrade to 2003 so it'd be useful if I had a
head
start on any issues rather than finding out via the user testing. The
application doesn't use data access pages or macros but there's
relationships, forms, queries reports and a fair bit of VB including
dynamically created SQL. Unfortunately the migration notes on this site
say
there's no need for any changes which rather put me off my guard.

Allen Browne said:
Try:
=[TotalAmnt]-nz([Forms]![Add Expenses
Record]![ExpenseLinSub].Form!LineTotal,0)

If the ".Form" bit is new, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

As you discovered, Access 2003 does seem to be more finicky about this
than
2000 was.

message
The following is used in a control souce in an Access 2000 Application
and
works fine:
=[TotalAmnt]-nz([Forms]![Add Expenses
Record]![ExpenseLinSub]!LineTotal,0)
In Access 2003 the explicit reference alway returns NULL. This is also
true
when similar syntax is used in VB code in other areas of the system.
The
migration notes state that we shouldn't have to change anything to
migrate.
I'm told that Access 2003 has been loaded as it comes at the user site.
Is
there a patch to fix this or can the above statement be recoded in some
way?
Where possible I've replaced explicit references with the Me. syntax
which
seems to resolve the issue - but there are places where the app uses
data
from other forms or sub-forms where I'm not sure what I can do. Any
advice
would be much appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Allen and for another excellent article. #3 does apply - I've
also hit the corruption problem but I've been around a while so always make
plenty of backups - still irritatiing to lose a mornings work though!

Many Thanks

John Pritchard.

Allen Browne said:
The other issue that A2003 seems finicky about is network printers. Some
users find they need to reinstall the printer drivers before A2003 can find
the printer, whereas previous versions and other products in the Office 2003
work with the printer okay.

In this article:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html
issue #3 under "development" relates to an issue with subforms that started
in A2002 and contiues in A2003.

Other than that, A2003 is generally very stable to work with.

There is no difference with the SQL statements: both call the same JET 4
engine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John Pritchard said:
Thanks Allen. Your document on referring to subforms was excellent too!

Do you know if there is anything else 2003 is finicky about? I'm still on
2000 but the user wants to upgrade to 2003 so it'd be useful if I had a
head
start on any issues rather than finding out via the user testing. The
application doesn't use data access pages or macros but there's
relationships, forms, queries reports and a fair bit of VB including
dynamically created SQL. Unfortunately the migration notes on this site
say
there's no need for any changes which rather put me off my guard.

Allen Browne said:
Try:
=[TotalAmnt]-nz([Forms]![Add Expenses
Record]![ExpenseLinSub].Form!LineTotal,0)

If the ".Form" bit is new, see:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

As you discovered, Access 2003 does seem to be more finicky about this
than
2000 was.

message
The following is used in a control souce in an Access 2000 Application
and
works fine:
=[TotalAmnt]-nz([Forms]![Add Expenses
Record]![ExpenseLinSub]!LineTotal,0)
In Access 2003 the explicit reference alway returns NULL. This is also
true
when similar syntax is used in VB code in other areas of the system.
The
migration notes state that we shouldn't have to change anything to
migrate.
I'm told that Access 2003 has been loaded as it comes at the user site.
Is
there a patch to fix this or can the above statement be recoded in some
way?
Where possible I've replaced explicit references with the Me. syntax
which
seems to resolve the issue - but there are places where the app uses
data
from other forms or sub-forms where I'm not sure what I can do. Any
advice
would be much appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top