Using Visual Basic for MS Access 2003 in reports

[DB Wynns]

I just recently installed MS Office 2003 and attempted to prepare a report
using MS Access with a Public Variable declared in Visual Basic for MS
Access. When I attempt to run the report the declared variable in VB returns
a “#NAME?†instead of the value. I run the same procedure on a 2002 version
and the correct value is returned.

I have re-installed MS Office with repair and the results are the same. I
have worked with MS Access for a number of years, using every version of MS
Office and not having this problem before.

Is this a bug in MS Access or does MS Access 2003 have different
requirements???

 

[Douglas J. Steele]

It could be a References problem.

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[DB Wynns]

I have checked the references and there doesn’t appear to be anything wrong
there.

My attempt to create this DB was a very, very, simple example created on one
PC and all in MS Access 2003. No other PCs or importing were used.

Option Compare Database

Public TOTAL_TEST As Integer


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TOTAL_TEST = 5
End Sub

Output on Report: Where “=[TOTAL_TEST]†is entered in the Control Source for
an unbounded text box.


WA01AA #Name?
WB01AA #Name?
WB03AA #Name?
WB03AA #Name?
WA01AA #Name?
WB01AA #Name?
WA01Y1 #Name?

Creating the exact same procedure on a 2002 version produces:

WA01AA 5
WB01AA 5
WB03AA 5
WB03AA 5
WA01AA 5
WB01AA 5
WA01Y1 5

 

[Douglas J. Steele]

I'm surprised it works in Access 2000.

Try creating a function that returns the value of Total_Test, and use that
as your control source.

Function Return_Total_Test() As Integer
Return_Total_Test = TOTAL_TEST
End Function

and your control source will be =Return_Total_Test()

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have checked the references and there doesn't appear to be anything wrong
there.

My attempt to create this DB was a very, very, simple example created on
one
PC and all in MS Access 2003. No other PCs or importing were used.

Option Compare Database

Public TOTAL_TEST As Integer


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TOTAL_TEST = 5
End Sub

Output on Report: Where "=[TOTAL_TEST]" is entered in the Control Source
for
an unbounded text box.


WA01AA #Name?
WB01AA #Name?
WB03AA #Name?
WB03AA #Name?
WA01AA #Name?
WB01AA #Name?
WA01Y1 #Name?

Creating the exact same procedure on a 2002 version produces:

WA01AA 5
WB01AA 5
WB03AA 5
WB03AA 5
WA01AA 5
WB01AA 5
WA01Y1 5

It could be a References problem.

This can be caused by differences in either the location or file version
of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open
the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar.
Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back
out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check
out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[DB Wynns]

Thanks, this works!

I guess MS Acess 2003 will require me to sharpen my programing skills, as
there's no more easy short cuts. I will miss that aspect as I liked to be
able to very quickly but together small tools to help analyse data.

Again Thanks

I'm surprised it works in Access 2000.

Try creating a function that returns the value of Total_Test, and use that
as your control source.

Function Return_Total_Test() As Integer
Return_Total_Test = TOTAL_TEST
End Function

and your control source will be =Return_Total_Test()

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have checked the references and there doesn't appear to be anything wrong
there.

My attempt to create this DB was a very, very, simple example created on
one
PC and all in MS Access 2003. No other PCs or importing were used.

Option Compare Database

Public TOTAL_TEST As Integer


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TOTAL_TEST = 5
End Sub

Output on Report: Where "=[TOTAL_TEST]" is entered in the Control Source
for
an unbounded text box.


WA01AA #Name?
WB01AA #Name?
WB03AA #Name?
WB03AA #Name?
WA01AA #Name?
WB01AA #Name?
WA01Y1 #Name?

Creating the exact same procedure on a 2002 version produces:

WA01AA 5
WB01AA 5
WB03AA 5
WB03AA 5
WA01AA 5
WB01AA 5
WA01Y1 5

It could be a References problem.

This can be caused by differences in either the location or file version
of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open
the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar.
Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back
out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check
out
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I just recently installed MS Office 2003 and attempted to prepare a
report
using MS Access with a Public Variable declared in Visual Basic for MS
Access. When I attempt to run the report the declared variable in VB
returns
a "#NAME?" instead of the value. I run the same procedure on a 2002
version
and the correct value is returned.

I have re-installed MS Office with repair and the results are the same.
I
have worked with MS Access for a number of years, using every version
of
MS
Office and not having this problem before.

Is this a bug in MS Access or does MS Access 2003 have different
requirements???

 

[Dirk Goldgar]

I have checked the references and there doesn't appear to be anything
wrong there.

My attempt to create this DB was a very, very, simple example created
on one PC and all in MS Access 2003. No other PCs or importing were
used.

Option Compare Database

Public TOTAL_TEST As Integer


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TOTAL_TEST = 5
End Sub

Output on Report: Where "=[TOTAL_TEST]" is entered in the Control
Source for an unbounded text box.


WA01AA #Name?
WB01AA #Name?
WB03AA #Name?
WB03AA #Name?
WA01AA #Name?
WB01AA #Name?
WA01Y1 #Name?

Creating the exact same procedure on a 2002 version produces:

WA01AA 5
WB01AA 5
WB03AA 5
WB03AA 5
WA01AA 5
WB01AA 5
WA01Y1 5

It's very odd that that works in Access 2002 -- I can't help thinking
that there must be some special circumstances beyond what you've told
us, maybe something you've overlooked. I've never seen that work in any
version of Access.

 

[david epsom dot com dot au]

Public TOTAL_TEST As Integer

It's very odd that works in Access 2002

It's not a global variable in a standard module -
it's a global variable in an object module, which
is created as a property of the object. And properties
of report objects can be used in report controls

But it's not a technique I've ever used, and I have
no idea how well it works in different versions of
Access.

(david)

I have checked the references and there doesn't appear to be anything
wrong there.

My attempt to create this DB was a very, very, simple example created
on one PC and all in MS Access 2003. No other PCs or importing were
used.

Option Compare Database

Public TOTAL_TEST As Integer


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TOTAL_TEST = 5
End Sub

Output on Report: Where "=[TOTAL_TEST]" is entered in the Control
Source for an unbounded text box.


WA01AA #Name?
WB01AA #Name?
WB03AA #Name?
WB03AA #Name?
WA01AA #Name?
WB01AA #Name?
WA01Y1 #Name?

Creating the exact same procedure on a 2002 version produces:

WA01AA 5
WB01AA 5
WB03AA 5
WB03AA 5
WA01AA 5
WB01AA 5
WA01Y1 5

It's very odd that that works in Access 2002 -- I can't help thinking
that there must be some special circumstances beyond what you've told
us, maybe something you've overlooked. I've never seen that work in any
version of Access.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[DB Wynns]

It works in all versions before 2003. It made it nice to be able to quickly
put something together very quickly without using more complex routines. As
I use MS Access at work I need to be able to “quickly†but together routines
that analyze and compare data from several different databases that include
various versions of Sybase and Oracle, using ODBC. This just means that I
will need to sharpen my coding skills and take a little longer to put
together my routines.

Thanks to everyone for the advice and help.

It's very odd that works in Access 2002

It's not a global variable in a standard module -
it's a global variable in an object module, which
is created as a property of the object. And properties
of report objects can be used in report controls

But it's not a technique I've ever used, and I have
no idea how well it works in different versions of
Access.

(david)

I have checked the references and there doesn't appear to be anything
wrong there.

My attempt to create this DB was a very, very, simple example created
on one PC and all in MS Access 2003. No other PCs or importing were
used.

Option Compare Database

Public TOTAL_TEST As Integer


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TOTAL_TEST = 5
End Sub

Output on Report: Where "=[TOTAL_TEST]" is entered in the Control
Source for an unbounded text box.


WA01AA #Name?
WB01AA #Name?
WB03AA #Name?
WB03AA #Name?
WA01AA #Name?
WB01AA #Name?
WA01Y1 #Name?

Creating the exact same procedure on a 2002 version produces:

WA01AA 5
WB01AA 5
WB03AA 5
WB03AA 5
WA01AA 5
WB01AA 5
WA01Y1 5

It's very odd that that works in Access 2002 -- I can't help thinking
that there must be some special circumstances beyond what you've told
us, maybe something you've overlooked. I've never seen that work in any
version of Access.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

It works in all versions before 2003. It made it nice to be able to
quickly put something together very quickly without using more
complex routines. As I use MS Access at work I need to be able to
"quickly" but together routines that analyze and compare data from
several different databases that include various versions of Sybase
and Oracle, using ODBC. This just means that I will need to sharpen
my coding skills and take a little longer to put together my routines.

It seems to me that one possible universal solution would be to put an
invisible text box on your report, and refer to that instead of the
public variable.

 

[Dirk Goldgar]

It's not a global variable in a standard module -
it's a global variable in an object module, which
is created as a property of the object. And properties
of report objects can be used in report controls

Ah, I see. I hadn't picked up on the fact that the variable was defined
in the report's class module.

 

[david epsom dot com dot au]

You need to check that there is not a naming conflict
between the variable name and anything else - control,
field, report, property etc. Also, it would be worth
trying some alternate syntax:

=report.total_test
=[report].[total_test]
=total_test


(david)

It works in all versions before 2003. It made it nice to be able to quickly
put something together very quickly without using more complex routines. As
I use MS Access at work I need to be able to “quickly†but together routines
that analyze and compare data from several different databases that include
various versions of Sybase and Oracle, using ODBC. This just means that I
will need to sharpen my coding skills and take a little longer to put
together my routines.

Thanks to everyone for the advice and help.

Public TOTAL_TEST As Integer

It's very odd that works in Access 2002

It's not a global variable in a standard module -
it's a global variable in an object module, which
is created as a property of the object. And properties
of report objects can be used in report controls

But it's not a technique I've ever used, and I have
no idea how well it works in different versions of
Access.

(david)

I have checked the references and there doesn't appear to be anything
wrong there.

My attempt to create this DB was a very, very, simple example created
on one PC and all in MS Access 2003. No other PCs or importing were
used.

Option Compare Database

Public TOTAL_TEST As Integer


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
TOTAL_TEST = 5
End Sub

Output on Report: Where "=[TOTAL_TEST]" is entered in the Control
Source for an unbounded text box.


WA01AA #Name?
WB01AA #Name?
WB03AA #Name?
WB03AA #Name?
WA01AA #Name?
WB01AA #Name?
WA01Y1 #Name?

Creating the exact same procedure on a 2002 version produces:

WA01AA 5
WB01AA 5
WB03AA 5
WB03AA 5
WA01AA 5
WB01AA 5
WA01Y1 5

It's very odd that that works in Access 2002 -- I can't help thinking
that there must be some special circumstances beyond what you've told
us, maybe something you've overlooked. I've never seen that work in any
version of Access.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Marshall Barton]

That was my first thought too David, but in A2002
=report.total_test
wouldn't work, even when total_test was a Property Get
procedure.

However, a full reference did work:
=Reports!reportname.total_test

Unfortunately, I'm not set up to try it in A2003 so I can't
address the question of it being different.
--
Marsh
MVP [MS Access]

Also, it would be worth
trying some alternate syntax:

=report.total_test
=[report].[total_test]
=total_test

It works in all versions before 2003. It made it nice to be able to quickly
put something together very quickly without using more complex routines. As
I use MS Access at work I need to be able to “quickly� but together routines
that analyze and compare data from several different databases that include
various versions of Sybase and Oracle, using ODBC. This just means that I
will need to sharpen my coding skills and take a little longer to put
together my routines.