Variable not define

[Guest]

Hi,

I am trying to create lots of reports using ACCESS. We need to enter a user
defined interface to filter the records from crosstab query. I am looking for
a good example of database for our case that I can get right way to get job
down. Do you have any suggestion to find good example database?

I download sample Access database "Using Parameters with Queries and
Reports" from microsoft web site. When I try to run the report "Sales By
Category", it tell the error message "Variable not defined." and
acCurViewDesign is highlighted. Could you tell me how to correct it? Thanks.

jy

 

[Danny J. Lesandrini]

Well, I won't say that this is a "good" example, but I've played around with
complex report filters for years, and some of what I've implimented is available
at my web site.

http://amazecreations.com/datafast/daaug.aspx

There are a number of ways to pass filter criteria to reports:

1) Filter in the DoCmd.OpenReport method "[LastName] = 'Smythe'"
2) Pass OpenArgs that are used in the Report Open event (requires coding)
3) Parameters in the report query WHERE [LastName] = [Enter Last Name]
4) Reference form control from query WHERE [LastName] = Forms!frmMain!LastName
5) Function call in criteria of query WHERE [LastName] = GetLastName()

6) Complex Criteria using filter tables.

This method is very flexible and allows for multi-select of criteria. Say you
wanted to allow the user to select multiple employees or categories or etc.
You open a report criteria collection form, allow the user to make selections
populate the appropriate criteria tables (or toggle a ReportOnMe flag in the
entity table) and then perform an INNER JOIN link to these filter tables in
the query to the report.

I wrote about this at DBJ also, but the article probably closely resembles the
download at my site. Here's the link to the article ...
http://www.databasejournal.com/features/msaccess/article.php/3104211

There is an image in the article that graphically describes what I'm talking about
and you can view it directly from here ...
http://www.databasejournal.com/img/dl_tricks_ReportFilter.jpg

Once again, I'm not saying that I love this approach, but I suppose it depends on
what your client requires. If they want multi-select, the simple methods mentioned
above won't cut it for you.

 

[Douglas J. Steele]

Where exactly did you get the sample from (and what's the exact line of code
that contains acCurViewDesign)?

As far as I can tell, that constant isn't part of the set of constants
defined with the standard references. You might try checking your references
(Tools | References when you're in the VB Editor and no code is running). If
any of the selected ones have MISSING: in front of them, you need to uncheck
(and recheck if the reference is actually required)

 

[Douglas J. Steele]

Also, what version of Access are you using?

After doing a little more investigation, it would appear that
acCurViewDesign is defined for Access 2002 and Access 2003, but not for
Access 2000 or earlier.

 

[Guest]

Danny,

Thank you for sending me the references. I will spend some time to take a
look. Thanks agian.

JY

Well, I won't say that this is a "good" example, but I've played around with
complex report filters for years, and some of what I've implimented is available
at my web site.

http://amazecreations.com/datafast/daaug.aspx

There are a number of ways to pass filter criteria to reports:

1) Filter in the DoCmd.OpenReport method "[LastName] = 'Smythe'"
2) Pass OpenArgs that are used in the Report Open event (requires coding)
3) Parameters in the report query WHERE [LastName] = [Enter Last Name]
4) Reference form control from query WHERE [LastName] = Forms!frmMain!LastName
5) Function call in criteria of query WHERE [LastName] = GetLastName()

6) Complex Criteria using filter tables.

This method is very flexible and allows for multi-select of criteria. Say you
wanted to allow the user to select multiple employees or categories or etc.
You open a report criteria collection form, allow the user to make selections
populate the appropriate criteria tables (or toggle a ReportOnMe flag in the
entity table) and then perform an INNER JOIN link to these filter tables in
the query to the report.

I wrote about this at DBJ also, but the article probably closely resembles the
download at my site. Here's the link to the article ...
http://www.databasejournal.com/features/msaccess/article.php/3104211

There is an image in the article that graphically describes what I'm talking about
and you can view it directly from here ...
http://www.databasejournal.com/img/dl_tricks_ReportFilter.jpg

Once again, I'm not saying that I love this approach, but I suppose it depends on
what your client requires. If they want multi-select, the simple methods mentioned
above won't cut it for you.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

Hi,

I am trying to create lots of reports using ACCESS. We need to enter a user
defined interface to filter the records from crosstab query. I am looking for
a good example of database for our case that I can get right way to get job
down. Do you have any suggestion to find good example database?

I download sample Access database "Using Parameters with Queries and
Reports" from microsoft web site. When I try to run the report "Sales By
Category", it tell the error message "Variable not defined." and
acCurViewDesign is highlighted. Could you tell me how to correct it? Thanks.

jy

 

[Guest]

Douglas,

Here is the link for sample database "Using parameters with queries and
reports sample database":

http://www.microsoft.com/downloads/...9A-315F-4138-8A63-0AC8E6A3AC0C&displaylang=en

I have checked the reference(Tool | reference in VB editor). There are
listed lots of objects. I only check 4 box: " Visual Basic for Application",
"Microsoft Access 9.0 objects library", "OLE Automation", "Microsoft ActiveX
data objects 2.1 library", "Microsoft DAO 3.6 object library". All others are
not checked. Should I check some of the left ?

When I ran the sample "sales by category report", the error message is
coming from:

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function

acCurViewDesign is not defined. Please let me know when you have any idea
on that. Thanks.

Happy Holiday.

Jy

 

[Guest]

If it is early version, how can I run these code? Thanks.

Jy

 

[Douglas J. Steele]

As I said in another post, acCurViewDesign only appears to exist in Access
2002 and Access 2003, and you're using Access 2000. (The page you're citing
is labelled "Access 2003/2002 Sample: Using Parameters with Queries and
Reports Sample Database")

See whether changing

If oAccessObject.CurrentView <> acCurViewDesign Then

to

If oAccessObject.CurrentView <> 0 Then

solves the problem.

 

[Guest]

Hi Doug and Jy,

I just downloaded the sample and did some testing. Changing:

If oAccessObject.CurrentView <> acCurViewDesign Then
to
If oAccessObject.CurrentView <> 0 Then

does not help, because one then receives a run-time error 438, Object
doesn't support this property of method, with this line of code highlighted
in yellow:

If oAccessObject.CurrentView <> 0 Then

However, there is an easy fix. The IsLoaded function from the copy of
Northwind that comes with Access 2000 can be substituted for the current
IsLoaded function. Here it is (watch for line wrap):

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If

End Function


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

As I said in another post, acCurViewDesign only appears to exist in Access
2002 and Access 2003, and you're using Access 2000. (The page you're citing
is labelled "Access 2003/2002 Sample: Using Parameters with Queries and
Reports Sample Database")

See whether changing

If oAccessObject.CurrentView <> acCurViewDesign Then

to

If oAccessObject.CurrentView <> 0 Then

solves the problem.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
__________________________________________


Douglas,

Here is the link for sample database "Using parameters with queries and
reports sample database":

http://www.microsoft.com/downloads/...9A-315F-4138-8A63-0AC8E6A3AC0C&displaylang=en

I have checked the reference(Tool | reference in VB editor). There are
listed lots of objects. I only check 4 box: " Visual Basic for Application",
"Microsoft Access 9.0 objects library", "OLE Automation", "Microsoft ActiveX
data objects 2.1 library", "Microsoft DAO 3.6 object library". All others are
not checked. Should I check some of the left ?

When I ran the sample "sales by category report", the error message is
coming from:

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function

acCurViewDesign is not defined. Please let me know when you have any idea
on that. Thanks.

Happy Holiday.

Jy

 

[Guest]

Hey Danny,

I am not sure if you can help me. I tired to go through you database and
recreate your form and code to my database. I seem to be a little stuck.

Basically, the problem is that, now I cannot select the filtered parameters.
The reports are based off of queries from three main tables.

If possible, do you think you can look over my database and see where I went
wrong. Thanks.

Well, I won't say that this is a "good" example, but I've played around with
complex report filters for years, and some of what I've implimented is available
at my web site.

http://amazecreations.com/datafast/daaug.aspx

There are a number of ways to pass filter criteria to reports:

1) Filter in the DoCmd.OpenReport method "[LastName] = 'Smythe'"
2) Pass OpenArgs that are used in the Report Open event (requires coding)
3) Parameters in the report query WHERE [LastName] = [Enter Last Name]
4) Reference form control from query WHERE [LastName] = Forms!frmMain!LastName
5) Function call in criteria of query WHERE [LastName] = GetLastName()

6) Complex Criteria using filter tables.

This method is very flexible and allows for multi-select of criteria. Say you
wanted to allow the user to select multiple employees or categories or etc.
You open a report criteria collection form, allow the user to make selections
populate the appropriate criteria tables (or toggle a ReportOnMe flag in the
entity table) and then perform an INNER JOIN link to these filter tables in
the query to the report.

I wrote about this at DBJ also, but the article probably closely resembles the
download at my site. Here's the link to the article ...
http://www.databasejournal.com/features/msaccess/article.php/3104211

There is an image in the article that graphically describes what I'm talking about
and you can view it directly from here ...
http://www.databasejournal.com/img/dl_tricks_ReportFilter.jpg

Once again, I'm not saying that I love this approach, but I suppose it depends on
what your client requires. If they want multi-select, the simple methods mentioned
above won't cut it for you.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

Hi,

I am trying to create lots of reports using ACCESS. We need to enter a user
defined interface to filter the records from crosstab query. I am looking for
a good example of database for our case that I can get right way to get job
down. Do you have any suggestion to find good example database?

I download sample Access database "Using Parameters with Queries and
Reports" from microsoft web site. When I try to run the report "Sales By
Category", it tell the error message "Variable not defined." and
acCurViewDesign is highlighted. Could you tell me how to correct it? Thanks.

jy

 

[Danny J. Lesandrini]

Drop me a line on my Hotmail account and we can discuss it.