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

why use a class module

P: n/a
Dear All

Can you tell me why you use a class module???

Thank you

Nicolaas
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #1
Share this Question
Share on Google+
42 Replies


P: n/a
WindAndWaves wrote:
Dear All

Can you tell me why you use a class module???
If you want to create your own objects/classes of course :) Never had
cause to do it myself...
Thank you

Nicolaas

--
regards,

Bradley
Nov 13 '05 #2

P: n/a
WindAndWaves wrote:
Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.
Nov 13 '05 #3

P: n/a
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #4

P: n/a
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #5

P: n/a
WindAndWaves wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain
date range. I use this procedure in a lot of queries and it runs
rather slow (there may be 5,000 records). Would it be faster if I
made it a class module????
I wouldn't have thought so...

Perhaps look for improvements in how you are doing your check?

Bradley
"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes
of forms as they close, and returns the form to that state upon
reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers
a words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this
email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


--
regards,

Bradley
Nov 13 '05 #6

P: n/a
WindAndWaves wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain
date range. I use this procedure in a lot of queries and it runs
rather slow (there may be 5,000 records). Would it be faster if I
made it a class module????
I wouldn't have thought so...

Perhaps look for improvements in how you are doing your check?

Bradley
"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes
of forms as they close, and returns the form to that state upon
reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers
a words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this
email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


--
regards,

Bradley
Nov 13 '05 #7

P: n/a
the reason why i thought so was because then it could be initialised at the
same time... while another is still running

currently it is slow, because the function has be called 5000 times, run and
closed off.
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #8

P: n/a
the reason why i thought so was because then it could be initialised at the
same time... while another is still running

currently it is slow, because the function has be called 5000 times, run and
closed off.
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #9

P: n/a
A class module is almost never a way of improving code performance. Usually,
using class modules reduces performance, but fortunately, in most code,
performance is not the biggest issue, clarity, maintainability, and
reliability are. Class modules are often useful in helping with -those-
concerns.

One thing class modules do is to provide an obvious and transparent place to
put some categories of code, and a way to reduce duplication, and allow code
to be extended globally by making changes in only one place.

Take dates. We already have a date class, but let's say you have an
application that does a lot of complex date manipulation. In this case, it
may make sense to make a date handling class

On Tue, 25 May 2004 14:41:36 +1200, "WindAndWaves" <ac****@ngaru.com> wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
> Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


Nov 13 '05 #10

P: n/a
A class module is almost never a way of improving code performance. Usually,
using class modules reduces performance, but fortunately, in most code,
performance is not the biggest issue, clarity, maintainability, and
reliability are. Class modules are often useful in helping with -those-
concerns.

One thing class modules do is to provide an obvious and transparent place to
put some categories of code, and a way to reduce duplication, and allow code
to be extended globally by making changes in only one place.

Take dates. We already have a date class, but let's say you have an
application that does a lot of complex date manipulation. In this case, it
may make sense to make a date handling class

On Tue, 25 May 2004 14:41:36 +1200, "WindAndWaves" <ac****@ngaru.com> wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
> Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


Nov 13 '05 #11

P: n/a
Sorry - I accidentally hit send too soon on the previous reply...

A class module is almost never a way of improving code performance. Usually,
using class modules reduces performance, but fortunately, in most code,
performance is not the biggest issue, clarity, maintainability, and
reliability are. Class modules are often useful in helping with -those-
concerns.

One thing class modules do is to provide an obvious and transparent place to
put some categories of code, and a way to reduce duplication, and allow code
to be extended globally by making changes in only one place.

Take dates. We already have a date data type, but let's say you have an
application that does a lot of complex date manipulation. In this case, it
may make sense to make your own custom date class.

At first, you might think, why not just have a module with a bunch of date
handling functions, but there are some reasons to consider the class approach
instead. First, the fact that you use the date class makes it obvious where
to look for the date handling functions in your code (they're in the class
module, and that's the class used for the variable in the code). Second, if
you need to add some alternate value assignment/retrieval behavior, you can do
that in one place without chaning any other code. For instance, let's say you
want to add a time zone, and you want the Value property to return the value
in the current time zone, though the Date and Zone properties are what's
actually stored.

On Tue, 25 May 2004 14:41:36 +1200, "WindAndWaves" <ac****@ngaru.com> wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
> Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


Nov 13 '05 #12

P: n/a
Sorry - I accidentally hit send too soon on the previous reply...

A class module is almost never a way of improving code performance. Usually,
using class modules reduces performance, but fortunately, in most code,
performance is not the biggest issue, clarity, maintainability, and
reliability are. Class modules are often useful in helping with -those-
concerns.

One thing class modules do is to provide an obvious and transparent place to
put some categories of code, and a way to reduce duplication, and allow code
to be extended globally by making changes in only one place.

Take dates. We already have a date data type, but let's say you have an
application that does a lot of complex date manipulation. In this case, it
may make sense to make your own custom date class.

At first, you might think, why not just have a module with a bunch of date
handling functions, but there are some reasons to consider the class approach
instead. First, the fact that you use the date class makes it obvious where
to look for the date handling functions in your code (they're in the class
module, and that's the class used for the variable in the code). Second, if
you need to add some alternate value assignment/retrieval behavior, you can do
that in one place without chaning any other code. For instance, let's say you
want to add a time zone, and you want the Value property to return the value
in the current time zone, though the Date and Zone properties are what's
actually stored.

On Tue, 25 May 2004 14:41:36 +1200, "WindAndWaves" <ac****@ngaru.com> wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:
> Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


Nov 13 '05 #13

P: n/a
WindAndWaves wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


I guess not. Classes are slower then standard modules, in my experience.
If you post the code, you may get suggestions about how to speed it up.
Nov 13 '05 #14

P: n/a
WindAndWaves wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


I guess not. Classes are slower then standard modules, in my experience.
If you post the code, you may get suggestions about how to speed it up.
Nov 13 '05 #15

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in
news:8r*******************@news.xtra.co.nz:
currently it is slow, because the function has be called 5000
times, run and closed off.


If it's initializing any data structures such as an array, or
looking up data from a recordset, it might be improved by making the
part that takes all the work persistent. You don't need to use a
class module to do that, though.

In any event, methods of class modules cannot be called directly in
queries -- you have to wrap them in a function.

Short answer: no, a class module is not going to improve things any
more than a proper restructuring of the function will.

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

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in
news:8r*******************@news.xtra.co.nz:
currently it is slow, because the function has be called 5000
times, run and closed off.


If it's initializing any data structures such as an array, or
looking up data from a recordset, it might be improved by making the
part that takes all the work persistent. You don't need to use a
class module to do that, though.

In any event, methods of class modules cannot be called directly in
queries -- you have to wrap them in a function.

Short answer: no, a class module is not going to improve things any
more than a proper restructuring of the function will.

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

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:g1********************************@4ax.com:
Usually,
using class modules reduces performance


I don't agree with this. It's generally neutral, that is, as likely
to improve performance as decrease, but in most cases, having no
effect whatsoever.

And, of course, there are plenty of things that can be done in a
class module that can't really be done at all without them.

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

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:g1********************************@4ax.com:
Usually,
using class modules reduces performance


I don't agree with this. It's generally neutral, that is, as likely
to improve performance as decrease, but in most cases, having no
effect whatsoever.

And, of course, there are plenty of things that can be done in a
class module that can't really be done at all without them.

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

P: n/a
WindAndWaves wrote:
Thank you all for your answers...

So, i have a procedure that evaluates if a date is within a certain date
range. I use this procedure in a lot of queries and it runs rather slow
(there may be 5,000 records). Would it be faster if I made it a class
module????


"Lyle Fairfield" <ly***********@netscape.net> wrote in message
news:2h************@uni-berlin.de...
WindAndWaves wrote:

Can you tell me why you use a class module???


I use a class when:

1. There may be multiple instances of the class "running" at the same
time, with the variables of the classes having distinct values, an
example being a class which remembers some properties or attributes of
forms as they close, and returns the form to that state upon reopening;
2. There are many variables to be initialized for use by a procedure
which is repeated many times, an example being code to write numbers a
words;
3. There is a need to standardize a procedure across the work of
multiple developers, an example being record saving.


5000 records is nothing. The query should be near instantaneous.
Something is up with your code if not.

Nov 13 '05 #20

P: n/a
Always cache the return value of slow functions called by
queries. Queries will typically call the function multiple
times for the same record (at least once for each time the
value is used!). For example:

a:fnInterest([b],[c],[d])

public function fnInterest(b,c,d) as double
static saveReturn as double
static saveParam as string
dim sParam as string
dim db as dao.database
dim rs as dao.recordset

sParam = a & b & c
if sParam = saveParam then
fnInterest = saveReturn
exit function
....

If the return value is used ten times, you have just reduced
the time taken to run the query to perhaps 1/10th of the
previous value.

(david)
http://www.hyphenologist.co.uk/killf...filefaqhtm.htm
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:zO*******************@news.xtra.co.nz...
Dear All

Can you tell me why you use a class module???

Thank you

Nicolaas
---
Please immediately let us know (by phone or return email) if (a) this email contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Nov 13 '05 #21

P: n/a
You can read my comments and thoughts on using class objects here:

http://www.attcanada.net/%7ekallal.m.../WhyClass.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #22

P: n/a
this is my code that takes a while to run:

I have put in the checks, because some fields may not contain proper date
fields or what have you.
Function fsimdat(Optional VFRO1, Optional VUNT1, Optional VFRO2, Optional
VUNT2) As Byte
'works out whether or not two date periods coincide
'0 = do not coincide
'1 = coincide
'2 = error
on error GoTo err
'--------------------------------------
Dim Fro1 As Date
Dim Unt1 As Date
Dim Fro2 As Date
Dim Unt2 As Date
'--------------------------------------
If IsNull(VFRO1) = True Or IsDate(VFRO1) = False Then GoTo data_error
Else Fro1 = VFRO1
If IsNull(VUNT1) = True Or IsDate(VUNT1) = False Then GoTo data_error
Else Unt1 = VUNT1
If IsNull(VFRO2) = True Or IsDate(VFRO2) = False Then GoTo data_error
Else Fro2 = VFRO2
If IsNull(VUNT2) = True Or IsDate(VUNT2) = False Then GoTo data_error
Else Unt2 = VUNT2
If Fro1 > Unt1 Or Fro2 > Unt2 Then GoTo data_error
If Fro1 = Unt1 Then Unt1 = Unt1 + 1
If Fro2 = Unt2 Then Unt2 = Unt2 + 1
'----------------------------'----------------------------
fsimdat = 2
'----------------------------'----------------------------
'1: |----|
'2: |---|
If Unt2 < Fro1 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |---|
If Unt1 < Fro2 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |-------|
If Fro1 >= Fro2 And Unt1 <= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |---------|
'2: |-------|
If Fro2 >= Fro1 And Unt2 <= Unt1 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |------|
'2: |-----|
If Fro1 >= Fro2 And Unt1 >= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |-----|
If Fro1 <= Fro2 And Unt1 <= Unt2 Then GoTo CoinciDe

data_error:
fsimdat = 3
xit:
Exit Function
CoinciDe:
fsimdat = 1
GoTo xit
UnCoinciDe:
fsimdat = 0
GoTo xit
err:
fsimdat = 2
Resume Next
End Function
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #23

P: n/a
that 'static' idea seems a really good solution to make things faster!

because I am sure that my date function will be called several time for each
record.

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:40**********************@news.syd.swiftdsl.co m.au...
Always cache the return value of slow functions called by
queries. Queries will typically call the function multiple
times for the same record (at least once for each time the
value is used!). For example:

a:fnInterest([b],[c],[d])

public function fnInterest(b,c,d) as double
static saveReturn as double
static saveParam as string
dim sParam as string
dim db as dao.database
dim rs as dao.recordset

sParam = a & b & c
if sParam = saveParam then
fnInterest = saveReturn
exit function
...

If the return value is used ten times, you have just reduced
the time taken to run the query to perhaps 1/10th of the
previous value.

(david)
http://www.hyphenologist.co.uk/killf...filefaqhtm.htm
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:zO*******************@news.xtra.co.nz...
Dear All

Can you tell me why you use a class module???

Thank you

Nicolaas
---
Please immediately let us know (by phone or return email) if (a) this

email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #24

P: n/a
thank you for all your help in this thread.
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #25

P: n/a
WindAndWaves wrote:
this is my code that takes a while to run:

I have put in the checks, because some fields may not contain proper date
fields or what have you.
Function fsimdat(Optional VFRO1, Optional VUNT1, Optional VFRO2, Optional
VUNT2) As Byte
Not sure why you have Optional as you don't check for IsMissing and use
Null instead. Which produces different results Ex:

Public Function test(Optional var1, Optional var2) As Long
If IsMissing(var1) And IsMissing(var2) Then test = 1
End Function
Public Function test1(Optional var1, Optional var2) As Long
If IsNull(var1) And IsNull(var2) Then test = 1
End Function
? test
echoes 1
? test1
echoes 0
But that doesn't affect much if anything.

I've never seen so many GOTOs since GWBasic, but I doubt that slows
things down much. We used to call your code spaghetti code.

Anyway, I modified your function at the bottom of the post. You will
notice it is much cleaner than your current code. Even so, I doubt it
should speed things up much. The only thing to do is check. Do you
have a filter on your other fields or links to slow things down?

Since it's a calculated expression, your column normally repaints when
viewed. Thus the results should be presented near instantaneously.

Of well, good luck.
'works out whether or not two date periods coincide
'0 = do not coincide
'1 = coincide
'2 = error
on error GoTo err
'--------------------------------------
Dim Fro1 As Date
Dim Unt1 As Date
Dim Fro2 As Date
Dim Unt2 As Date
'--------------------------------------
If IsNull(VFRO1) = True Or IsDate(VFRO1) = False Then GoTo data_error
Else Fro1 = VFRO1
If IsNull(VUNT1) = True Or IsDate(VUNT1) = False Then GoTo data_error
Else Unt1 = VUNT1
If IsNull(VFRO2) = True Or IsDate(VFRO2) = False Then GoTo data_error
Else Fro2 = VFRO2
If IsNull(VUNT2) = True Or IsDate(VUNT2) = False Then GoTo data_error
Else Unt2 = VUNT2
If Fro1 > Unt1 Or Fro2 > Unt2 Then GoTo data_error
If Fro1 = Unt1 Then Unt1 = Unt1 + 1
If Fro2 = Unt2 Then Unt2 = Unt2 + 1
'----------------------------'----------------------------
fsimdat = 2
'----------------------------'----------------------------
'1: |----|
'2: |---|
If Unt2 < Fro1 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |---|
If Unt1 < Fro2 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |-------|
If Fro1 >= Fro2 And Unt1 <= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |---------|
'2: |-------|
If Fro2 >= Fro1 And Unt2 <= Unt1 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |------|
'2: |-----|
If Fro1 >= Fro2 And Unt1 >= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |-----|
If Fro1 <= Fro2 And Unt1 <= Unt2 Then GoTo CoinciDe

data_error:
fsimdat = 3
xit:
Exit Function
CoinciDe:
fsimdat = 1
GoTo xit
UnCoinciDe:
fsimdat = 0
GoTo xit
err:
fsimdat = 2
Resume Next
End Function

Function fsimdat(ByVal FRO1, ByVal UNT1, ByVal FRO2, ByVal UNT2) As Byte
'using ByVal does not change the values. ByRef, if values changed, they
'change on return. In case you care.

On Error Goto Err_Routine

'if the date field is null, it is not considered a date so
'there is no need to check IsNull()
If IsDate(FRO1) And _
IsDate(UNT1) And _
IsDate(FRO2) And _
IsDate(UNT2) then
IF FRO1 <= FRO2 And FRO2 <= UNIT2 then
If Fro1 = Unt1 Then Unt1 = Unt1 + 1
If Fro2 = Unt2 Then Unt2 = Unt2 + 1

If Unt2 < Fro1 Or If Unt1 < Fro2 Then
fsimdat = 0
ElseIf (Fro1 >= Fro2 And Unt1 <= Unt2)
(Fro2 >= Fro1 And Unt2 <= Unt1) Or _
(Fro1 >= Fro2 And Unt1 >= Unt2) Or _
(Fro1 <= Fro2 And Unt1 <= Unt2) Then

'I don't even know if the above checks
'even need to be made. I don't have the
'time to check see if they are irrelevent
'I would suggest you comment what you want
'to accomplish and why.

fsimdat = 1
Else
fsimdat = 2
endif

else
fsimdat = 3
endif
else
fsimdat = 3
endif

Exit_Routine:
Exit Function
Err_Routine:
fsimdat = 2
Resume ExitRoutine
End Function
Nov 13 '05 #26

P: n/a
1. i use optional, because sometimes the dates are not available
2. is use the goto as to exit ASAP

My basic premise is that every line of execution takes time and that was my
real aim in writing this code (reducing the number of lines that are
executed).

The 1 and 2 comments show the situations, as it is actually a little harder
then it seems to catch all situations where two periods coincide (that is,
they can overlap, but one can also totally overlap the other, so there are
three distinct situations).


Function fsimdat(Optional VFRO1, Optional VUNT1, Optional VFRO2, Optional
VUNT2) As Byte
'works out whether or not two date periods coincide
'the goto statement is used so that the procedure exits as soon as an answer
has been found
'0 = do not coincide
'1 = coincide
'2 = error
Const ProEro = 3: 'on error GoTo err
'--------------------------------------
Static Fro1 As Single
Static Unt1 As Single
Static Fro2 As Single
Static Unt2 As Single
'------check validity of input--------------------------------
If IsMissing(VFRO1) = True Or IsDate(VFRO1) = False Then GoTo data_error
Else Fro1 = VFRO1
If IsMissing(VUNT1) = True Or IsDate(VUNT1) = False Then GoTo data_error
Else Unt1 = VUNT1
If IsMissing(VFRO2) = True Or IsDate(VFRO2) = False Then GoTo data_error
Else Fro2 = VFRO2
If IsMissing(VUNT2) = True Or IsDate(VUNT2) = False Then GoTo data_error
Else Unt2 = VUNT2
If Fro1 > Unt1 Or Fro2 > Unt2 Then GoTo data_error 'end dates should be
after start datea
If Fro1 = Unt1 Then Unt1 = Unt1 + 1 ' in case the start = end date then
the user probably meant during that one day
If Fro2 = Unt2 Then Unt2 = Unt2 + 1 ' in case the start = end date then
the user probably meant during that one day
'-----go through all options;
'-----comments show situation of the two dates (1 = first date, 2 = second
date)
'-----* shows which dates are compared
'----------------------------
'1: *-1--|
'2: |-2-*
If Unt2 < Fro1 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: |-1--*
'2: *-2-|
If Unt1 < Fro2 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: *-1--|
'2: *------2-------*
If Fro1 >= Fro2 And Fro1 <= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: *-----1---------*
'2: *---2---|
If Fro2 >= Fro1 And Fro2 <= Unt1 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: *--1---|
'2: *---2-*
If Fro1 >= Fro2 And Fro1 <= Unt2 Then GoTo CoinciDe
data_error:
fsimdat = 3
xit:
Exit Function
CoinciDe:
fsimdat = 1
GoTo xit
UnCoinciDe:
fsimdat = 0
GoTo xit
err:
fsimdat = 2
Resume Next
End Function

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #27

P: n/a
WindAndWaves wrote:
1. i use optional, because sometimes the dates are not available
That's why you have the on error routine set to 2. Except that you
resume next. No checking for IsMissing. Who knows why. Oh well, you
seem adept at coding and you know what is best. One can lead a horse to
water but one can't make it drink.
2. is use the goto as to exit ASAP
Uh Huh.

My basic premise is that every line of execution takes time and that was my
real aim in writing this code (reducing the number of lines that are
executed).
Uh Huh.
The 1 and 2 comments show the situations, as it is actually a little harder
then it seems to catch all situations where two periods coincide (that is,
they can overlap, but one can also totally overlap the other, so there are
three distinct situations).
You didn't study my code. Like I said, horse to water, no drink.

As I also stated, the results should be near instantaneous. I would
look at your structures, filters, and general programming code.
Something is horribly wrong if you wait over 1 second in a 5000 record
table. And even 1 second may be too long. Then again, I am basing my
opinion on my experience, not others.



Function fsimdat(Optional VFRO1, Optional VUNT1, Optional VFRO2, Optional
VUNT2) As Byte
'works out whether or not two date periods coincide
'the goto statement is used so that the procedure exits as soon as an answer
has been found
'0 = do not coincide
'1 = coincide
'2 = error
Const ProEro = 3: 'on error GoTo err
'--------------------------------------
Static Fro1 As Single
Static Unt1 As Single
Static Fro2 As Single
Static Unt2 As Single
'------check validity of input--------------------------------
If IsMissing(VFRO1) = True Or IsDate(VFRO1) = False Then GoTo data_error
Else Fro1 = VFRO1
If IsMissing(VUNT1) = True Or IsDate(VUNT1) = False Then GoTo data_error
Else Unt1 = VUNT1
If IsMissing(VFRO2) = True Or IsDate(VFRO2) = False Then GoTo data_error
Else Fro2 = VFRO2
If IsMissing(VUNT2) = True Or IsDate(VUNT2) = False Then GoTo data_error
Else Unt2 = VUNT2
If Fro1 > Unt1 Or Fro2 > Unt2 Then GoTo data_error 'end dates should be
after start datea
If Fro1 = Unt1 Then Unt1 = Unt1 + 1 ' in case the start = end date then
the user probably meant during that one day
If Fro2 = Unt2 Then Unt2 = Unt2 + 1 ' in case the start = end date then
the user probably meant during that one day
'-----go through all options;
'-----comments show situation of the two dates (1 = first date, 2 = second
date)
'-----* shows which dates are compared
'----------------------------
'1: *-1--|
'2: |-2-*
If Unt2 < Fro1 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: |-1--*
'2: *-2-|
If Unt1 < Fro2 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: *-1--|
'2: *------2-------*
If Fro1 >= Fro2 And Fro1 <= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: *-----1---------*
'2: *---2---|
If Fro2 >= Fro1 And Fro2 <= Unt1 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: *--1---|
'2: *---2-*
If Fro1 >= Fro2 And Fro1 <= Unt2 Then GoTo CoinciDe
data_error:
fsimdat = 3
xit:
Exit Function
CoinciDe:
fsimdat = 1
GoTo xit
UnCoinciDe:
fsimdat = 0
GoTo xit
err:
fsimdat = 2
Resume Next
End Function

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004


Nov 13 '05 #28

P: n/a
Dear Salad

Thank you for all your comments. I definitely will look into it more...

The reason why I said "to exit ASAP" is that everytime my procedure finds a
solution/problem, it immediately sends the program on to the next step,
rather than having to execute all the lines.

Thank you for all your comments... You seem to be very knowledgeable.

Nicolaas
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004
Nov 13 '05 #29

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in
news:Bw********************@news.xtra.co.nz:
this is my code that takes a while to run:
I'll point out some things that slow your code down, and are bad
programming practices to boot.

Thies are in addition to Salad's valid comments about the
optional clauses in the definition, You use optional for a
parameter only when you sometimes need to run a function withut
passing a parameter, for example:
ParseCDR(Item_no as variant, optional encoding as string = "g")
as string
That header tells me i need to pass a string (which may be null,
and may or may not pass a second argument which alters the
behaviour of my function, but it will still work.
Your function needs all four dates, although they may be null, so
the optional keyword should not be present.

Function fsimdat(VFRO1 as variant,VUNT1 as variant,VFRO2 as
variant, VUNT2 as variant) As Byte

Not that the default type for parameters is variant, but you
should as a matter of good codewriting, explicitly declare them.
I have put in the checks, because some fields may not contain
proper date fields or what have you.
Function fsimdat(Optional VFRO1, Optional VUNT1, Optional
VFRO2, Optional VUNT2) As Byte
'works out whether or not two date periods coincide
'0 = do not coincide
'1 = coincide
'2 = error
on error GoTo err
'--------------------------------------
Dim Fro1 As Date
Dim Unt1 As Date
Dim Fro2 As Date
Dim Unt2 As Date
'--------------------------------------
If IsNull(VFRO1) = True Or IsDate(VFRO1) = False Then GoTo
data_error
Clean up the line above
Any compiler works by reducing an expression to a true or false.
Isnull(VFR01) = true must be evaluated to if true = true, then
evaluated again to true, then processed by the if.

so the first level of cleanup is to
If isnull(VFR01) OR Not isdate(VFR01) then goto ...

Isdate returns false if VFR01 is null, so that cuts execution
time.

So we can reduce your code to"
IF not Isdate(VFR01) then goto data_error

and we've eliminated 5 of the 6 comparisons that the program must
make. there is a good speedup.
Else Fro1 = VFRO1
Why do that? Just use VFR01 in later calculations, and save the
time of a totally useless string copy routine.
If IsNull(VUNT1) = True Or IsDate(VUNT1) = False Then GoTo
data_error
Else Unt1 = VUNT1
If IsNull(VFRO2) = True Or IsDate(VFRO2) = False Then GoTo
data_error
Else Fro2 = VFRO2
If IsNull(VUNT2) = True Or IsDate(VUNT2) = False Then GoTo
data_error
Else Unt2 = VUNT2
the same comments apply to your other 3 data points.
If Fro1 > Unt1 Or Fro2 > Unt2 Then GoTo data_error
If Fro1 = Unt1 Then Unt1 = Unt1 + 1
If Fro2 = Unt2 Then Unt2 = Unt2 + 1
Why change dates? the logic below would work with fro1 = unt1.
It's just more wasted comparisons.
'----------------------------'----------------------------
fsimdat = 2 why bother setting fsimdat here?. All execution branches will set
it. more wasted time.

Graphics are good. they express clearly what you are trying to
accomplish
'----------------------------'----------------------------
'1: |----|
'2: |---|
If Unt2 < Fro1 Then GoTo UnCoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |---|
If Unt1 < Fro2 Then GoTo UnCoinciDe
'----------------------------'----------------------------
beyond this point, all roads go to Rome
Since the data has already been errorchecked, we will never fail
all four tests.
Goto CoinciDe saves a lot of useless steps.
'1: |----|
'2: |-------|
If Fro1 >= Fro2 And Unt1 <= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |---------|
'2: |-------|
If Fro2 >= Fro1 And Unt2 <= Unt1 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |------|
'2: |-----|
If Fro1 >= Fro2 And Unt1 >= Unt2 Then GoTo CoinciDe
'----------------------------'----------------------------
'1: |----|
'2: |-----|
If Fro1 <= Fro2 And Unt1 <= Unt2 Then GoTo CoinciDe

data_error:
fsimdat = 3
xit:
Exit Function
CoinciDe:
fsimdat = 1
GoTo xit
UnCoinciDe:
fsimdat = 0
GoTo xit
err:
fsimdat = 2
Resume Next
REsume next should be resume xit.
End Function


Bob Quintal
Nov 13 '05 #30

P: n/a
Dear Bob

Thank you - and all the others - for the feedback. My function now looks
like this:

Function fsimdat(FRO1 As Variant, UNT1 As Variant, FRO2 As Variant, UNT2 As
Variant) As Byte
'works out whether or not two date periods coincide
'the goto statement is used so that the procedure exits as soon as an answer
has been found
'0 = do not coincide
'1 = coincide
'2 = error
Const ProEro = 3: 'on error GoTo err
'------check validity of input--------------------------------
If Not IsDate(FRO1) Then GoTo data_err
If Not IsDate(UNT1) Then GoTo data_err
If Not IsDate(FRO2) Then GoTo data_err
If Not IsDate(UNT2) Then GoTo data_err
If FRO1 > UNT1 Or FRO2 > UNT2 Then GoTo data_err 'end dates should be
after start datea
If FRO1 = UNT1 Then UNT1 = UNT1 + 1 ' in case the start = end date then
the user probably meant during that one day
If FRO2 = UNT2 Then UNT2 = UNT2 + 1 ' in case the start = end date then
the user probably meant during that one day
'-----go through all options;
'-----comments show situation of the two dates (1 = first date, 2 = second
date)
'-----* shows which dates are compared
'----------------------------
'1: *-1--|
'2: |-2-*
If UNT2 < FRO1 Then GoTo notMatch
'----------------------------'----------------------------
'1: |-1--*
'2: *-2-|
If UNT1 < FRO2 Then GoTo notMatch
'----------------------------'----------------------------
'1: *-1--|
'2: *------2-------*
If FRO1 >= FRO2 And FRO1 <= UNT2 Then GoTo Match
'----------------------------'----------------------------
'1: *--------1---------*
'2: *---2---|
If FRO2 >= FRO1 And FRO2 <= UNT1 Then GoTo Match
'----------------------------'----------------------------
'1: *--1---|
'2: *---2----*
If FRO1 >= FRO2 And FRO1 <= UNT2 Then GoTo Match
'----------------------------'----------------------------
data_err:
fsimdat = 2
xit:
Exit Function
Match:
fsimdat = 1
GoTo xit
notMatch:
fsimdat = 0
GoTo xit
err:
fsimdat = 2
Call FerrorLog(err.Number, 0, ProEro + ModEro): Resume xit
End Function

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
Nov 13 '05 #31

P: n/a
"WindAndWaves" <ac****@ngaru.com> wrote in
news:Vp********************@news.xtra.co.nz:
Dear Bob

Thank you - and all the others - for the feedback. My
function now looks like this:

You are welcome. Much better, but still more tests than you need.
A few comments below.

If FRO1 = UNT1 Then UNT1 = UNT1 + 1 ' in case the start =
end date then
the user probably meant during that one day
So why are you changing it to two days?
If FRO2 = UNT2 Then UNT2 = UNT2 + 1 ' in case the start =
end date then
the user probably meant during that one day
same as above.
'-----go through all options;
why not just go through the options that point to one
possibility, and once they are all done, all remaining
possibilities can be combined into one. absolute jump.
'-----comments show situation of the two dates (1 = first
date, 2 = second date)
'-----* shows which dates are compared
'----------------------------
'1: *-1--|
'2: |-2-*
If UNT2 < FRO1 Then GoTo notMatch
'----------------------------'----------------------------
'1: |-1--*
'2: *-2-|
If UNT1 < FRO2 Then GoTo notMatch
'----------------------------'----------------------------
Everything below here takes you to Match, so just goto match and
skip all the useless tests.

GOTO Match
data_err:
fsimdat = 2
xit:
Exit Function
Match:
fsimdat = 1
GoTo xit
notMatch:
fsimdat = 0
GoTo xit
err:
fsimdat = 2
Call FerrorLog(err.Number, 0, ProEro + ModEro): Resume xit
End Function


Bob Quintal
Nov 13 '05 #32

P: n/a
> > '--------------------------------------
If IsNull(VFRO1) = True Or IsDate(VFRO1) = False Then GoTo
data_error
Clean up the line above
Any compiler works by reducing an expression to a true or false.
Isnull(VFR01) = true must be evaluated to if true = true, then
evaluated again to true, then processed by the if.


Any compiler will do that, so there is no need for you to do that.
If IsNull(VFRO1) = True


is a valid coding style. It is particularly appropriate as a
coding style for Access, because of the problems in some versions
with code like this:

If myChkBox Then

which should always be written in the long form:

If myChkBox = True Then
or
If myChkBox.Value Then

which force a local evaluation of the the Value property of
the checkbox, rather than passing the control object to the
VB virtual machine for evaluation.
(david)
Nov 13 '05 #33

P: n/a
Thank you for that David
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
Nov 13 '05 #34

P: n/a
Hi Everyone

Thank you all so much for your comments on the class module.

Further to all your comments, I have put together my first class module (i
have copied it onto the end of this post)


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
Nov 13 '05 #35

P: n/a
rkc

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:fS********************@news.xtra.co.nz...
Hi Everyone

Thank you all so much for your comments on the class module.

Further to all your comments, I have put together my first class module (i
have copied it onto the end of this post)

Must have been that self-destructing code PC Datasheet was looking for.
Nov 13 '05 #36

P: n/a
Thank you all very much for your help with the class module question.

Further to your comments, I have put together my first class module (see end
of post). My question now is how you make it compulsory to provide an
argument when initializing an instance of the ZOS class (as I call the class
module).

For those keen to read more about my developments, please read below:

It is called ZOS and it used in my database for printing reports.

Basically you pass the rptid (from a table with contains all the reports) or
name(rptn) to the class module and in return it can provide you with a raft
of variables about the report (e.g. whether it has date fields, if you can
filter for these date fields, a bunch of boolean parameters (e.g. print on
letterheader), a default filter (defaultW), etc...).

The class also loads the filters for the report (either from the ZOS form or
from the P-ZOU table) and prints the report.

Thank you once more for your comments

Greetings from the roaring forties

Nicolaas

-----------------------------------------------------------------

Option Compare Database
Option Explicit
'allows you to manage reports
'must provide a RptID or a RptN
Dim MyRptN As String
Dim RST As Recordset
Dim MyRptId As Long
Property Let RptN(S As String)
'On Error GoTo err
'-----------------------------------
Set RST = CurrentDb.OpenRecordset("SELECT [T-ZOU].* FROM [T-ZOU] WHERE
([T-ZOU]![Dc]='" & S & "');")
MyRptN = S
MyRptId = RST.Fields("ID")
xit:
Exit Property
err:
Resume xit
End Property
Property Let RptID(REC As Long)
'On Error GoTo err
'-----------------------------------
Set RST = CurrentDb.OpenRecordset("SELECT [T-ZOU].* FROM [T-ZOU] WHERE
([T-ZOU]![Dc]='" & REC & "');")
MyRptId = REC
MyRptN = RST.Fields("Dc")
xit:
Exit Property
err:
Resume xit
End Property
Property Get HasDateFields() As Boolean
'on error goto err
'-----------------------------------
If Nz(RST.Fields("FFD"), "") <> "" Or Nz(RST.Fields("UFD"), "") <> ""
Then HasDateFields = True
xit:
Exit Property
err:
Resume xit
End Property
Property Get ParameterS(i As Byte) As String
'on error goto err
'-----------------------------------
If i < 1 Or i > 5 Then ParameterS = ""
ParameterS = RST.Fields("PR" & i)
xit:
Exit Property
err:
Resume xit
End Property
Property Get ParameterDefaultV(i As Byte) As Boolean
'on error goto err
'-----------------------------------
If i < 1 Or i > 5 Then ParameterDefaultV = ""
ParameterDefaultV = DLookup("[PA" & i & "]", "[P-ZOU]",
"[P-ZOU]![T-ZOU-ID]=" & MyRptId)
xit:
Exit Property
err:
Resume xit
End Property
Property Get RptD() As String
'on error goto err
'-----------------------------------
RptD = Trim(DLookup("[D]", "[T-ZOU]]", "[T-ZOU]![ID]=" & RptID))
xit:
Exit Property
err:
Resume xit
End Property
Property Get RptN() As String
'on error goto err
'-----------------------------------
RptN = MyRptN
xit:
Exit Property
err:
Resume xit
End Property
Property Get RptID() As Long
'on error goto err
'-----------------------------------
RptID = RST.Fields("ID")
xit:
Exit Property
err:
Resume xit
End Property
Property Get FromField() As String
'on error goto err
'-----------------------------------
FromField = RST.Fields("FFD")
xit:
Exit Property
err:
Resume xit
End Property
Property Get UntilField() As String
'on error goto err
'-----------------------------------
UntilField = RST.Fields("UFD")
xit:
Exit Property
err:
Resume xit
End Property
Property Get DefaultFromDays() As Integer
'on error goto err
'-----------------------------------
DefaultFromDays = DLookup("[FROM]", "[P-ZOU]", "[P-ZOU]![T-ZOU-ID]=" &
RptID)
xit:
Exit Property
err:
Resume xit
End Property
Property Get DefaultUntilDays() As Integer
'on error goto err
'-----------------------------------
DefaultUntilDays = DLookup("[UNTIL]", "[P-ZOU]", "[P-ZOU]![T-ZOU-ID]=" &
RptID)
xit:
Exit Property
err:
Resume xit
End Property
Property Get DataTablesCount() As Integer
'on error goto err
'-----------------------------------
DataTablesCount = DCount("[ID]", "[T-ZTC]", "[T-ZTC]![T-ZOU-ID]=" &
MyRptId & " and mkd(tabidtodc([T-ZTC]![T-TAB-ID1]))=true")
xit:
Exit Property
err:
Resume xit
End Property
Property Get ParameterTablesCount() As Integer
'on error goto err
'-----------------------------------
ParameterTablesCount = DCount("[ID]", "[T-ZTC]", "[T-ZTC]![T-ZOU-ID]=" &
MyRptId & " and ljd(tabdctoid([T-ZTC]![T-TAB-ID1]))=true")
xit:
Exit Property
err:
Resume xit
End Property
Property Get DefaultFromUntilFilter() As Boolean
'on error goto err
'-----------------------------------
If DefaultFromDays <> 0 Or DefaultUntilDays <> 0 Then
DefaultFromUntilFilter = True
End If
xit:
Exit Property
err:
Resume xit
End Property
Private Function DefaultW() As String
'on error goto err
'-----------------------------------
Dim W As String
'-----------------------------------
If DefaultFromUntilFilter = True Then
If Nz(UntilField, "") <> "" Then
W = "fsimdat(fromm(), untilm(), [" & FromField & "], [" &
UntilField & "])=true"
Else
If Nz(FromField, "") = "" Then
W = "[DAT] >= fromm() and [dat]<= untilm()"
Else
W = "[" & FromField & "] >= fromm() and [" & FromField &
"]<= untilm()"
End If
End If
End If
xit:
Exit Function
err:
Resume xit
End Function
Private Function ZosW() As String
On Error GoTo err
'-----------------------------------
Dim Wall As String
Dim TblN1 As String
Dim TblN2 As String
Dim W(10) As String
Dim Itm As Variant
Dim Itm1 As Variant
Dim Itm2 As Variant
Dim Ctl1 As Control
Dim Ctl2 As Control
Dim Ctl As Control
Dim Frm As Form
'-----------------------------------
Set Frm = Forms("A-ZOS")
If Nz(Frm.T_TAB_ID1, "") <> "" Then TblN1 = TabIDtoDc(Frm.T_TAB_ID1)
If Nz(Frm.T_TAB_ID2, "") <> "" Then TblN1 = TabIDtoDc(Frm.T_TAB_ID2)
Set Ctl = Frm.Controls("FILTER")
For Each Itm In Ctl.ItemsSelected
Select Case Ctl.ItemData(Itm)
Case 0
If Nz(Frm.UFD, "") <> "" Then
W(0) = "fsimdat(fromm(), untilm(), [" & Frm.FFD & "], ["
& Frm.UFD & "])=true"
Else
If Nz(Frm.FFD, "") = "" Then
W(0) = "[DAT] >= fromm() and [dat]<= untilm()"
Else
W(0) = "[" & Frm.FFD & "] >= fromm() and [" &
Frm.FFD & "]<= untilm()"
End If
End If
Case 10
If Nz(TblN1, "") <> "" Then
Set Ctl1 = Frm.Controls("TAB1")
For Each Itm1 In Ctl1.ItemsSelected
W(1) = W(1) & "[" & TblN1 & "-ID]=" &
Ctl1.ItemData(Itm1) & " OR "
Next Itm1
W(1) = Left(W(1), Len(W(1)) - 4)
End If
Case 20
If Nz(TblN2, "") <> "" Then
Set Ctl2 = Frm.Controls("TAB2")
For Each Itm2 In Ctl1.ItemsSelected
W(2) = W(2) & "[" & TblN2 & "-ID]=" &
Ctl1.ItemData(Itm2) & " OR "
Next Itm2
W(2) = Left(W(2), Len(W(2)) - 4)
End If
End Select
Next Itm
Wall = "(" & W(0) & ") AND (" & W(1) & ") AND (" & W(2) & ")"
Wall = FuTOD(Wall, "() AND (")
Wall = FuTOD(Wall, ") AND ()")
ZosW = Wall
xit:
Exit Function
err:
Resume xit
End Function
Private Function DefaultParameterVs() As String
'produces a string, e.g. 00100 where parameters 1, 2, 4 and 5 are false AND
parameter 3 = true
'this string can be passed to reports as the opening argument
'on error goto err
'-----------------------------------
Dim W As String
Dim i As Byte
'-----------------------------------
DefaultParameterVs = ""
For i = 1 To 5
If ParameterDefaultV(i) = True Then W = W & 1 Else W = W & 0
Next i
DefaultParameterVs = W
xit:
Exit Function
err:
Resume xit
End Function

Private Function ZosParameterVs() As String
'produces a string, e.g. 00100 where parameters 1, 2, 4 and 5 are false AND
parameter 3 = true
'this string can be passed to reports as the opening argument
'on error goto err
'-----------------------------------
Dim W As String
Dim i As Byte
Dim Frm As Form
Dim Ctl As Control
'-----------------------------------
ZosParameterVs = ""
i = 0
Set Frm = Forms("A-ZOS")
Set Ctl = Frm.Controls("FILTER")
For i = 1 To 5
If Ctl.ItemData(i).Selected = True Then
W = W & "1"
Else
W = W & "0"
End If
Next i
ZosParameterVs = W
xit:
Exit Function
err:
Resume xit
End Function

Sub OpenReport(FromZos As Boolean, ToPrinter As Boolean)
'on error goto err
'-----------------------------------
Dim W As String
Dim OpenArg As String
'-----------------------------------
If FromZos = True And IsLoaded("A-ZOS") = True Then
W = ZosW
OpenArg = ZosParameterVs
Else
If IsLoaded("A-TOC") = False Then
FMB (204)
GoTo xit
Else
W = DefaultW
OpenArg = DefaultParameterVs
Forms("A-TOC").from = DATE + DefaultFromDays
Forms("A-TOC").until = DATE + DefaultUntilDays
End If
End If
If ToPrinter = True Then
DoCmd.OpenReport RptN, acViewNormal, , W, acWindowNormal, OpenArg
Else
DoCmd.OpenReport RptN, acViewPreview, , W, acWindowNormal, OpenArg
End If
xit:
Exit Sub
err:
Resume xit
End Sub


---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
Nov 13 '05 #37

P: n/a
rkc

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:3X********************@news.xtra.co.nz...
Thank you all very much for your help with the class module question.

Further to your comments, I have put together my first class module (see end of post). My question now is how you make it compulsory to provide an
argument when initializing an instance of the ZOS class (as I call the class module).


Unfortunately there is no way at all to provide an argument when creating
an object from a vba class. There is an Initialize event that will run if it
has
been defined, but it takes no arguments. You have to call at least one
method of the class after initialization to provide any arguments.

I notice you use the label err in all your error handler code.
Just so you are aware, Err is a VBA object that holds runtime error
information. You might want to use a different name for your error
labels.

Nov 13 '05 #38

P: n/a
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:40**********************@news.syd.swiftdsl.co m.au:
If IsNull(VFRO1) = True
is a valid coding style. It is particularly appropriate as a
coding style for Access, because of the problems in some versions
with code like this:

If myChkBox Then

which should always be written in the long form:

If myChkBox = True Then
or
If myChkBox.Value Then


It should also be written with the parent object explicitly
identified:

If (Me!myChkBox) Then

And the parens force evaluation.
which force a local evaluation of the the Value property of
the checkbox, rather than passing the control object to the
VB virtual machine for evaluation.


I prefer the parens to .Value or the silly = True.

However, let me point out that:

If IsNull(VFR01) Then

does not have this problem, because it is the result of IsNull()
that is evaluated, not a control on a form, so there is no need to
compare it to True -- IsNull() returns a value of type Boolean.

It's important to distinguish these two issues. The problem with
Boolean evaluation is when you do this:

If Me!CheckBox Then

But other Boolean expressions do *not* have the problem -- it is
only a problem with evaluating *controls* that contain Boolean
values. Having a control referred within a Boolean expression does
not cause the problem at all.

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

P: n/a
On Mon, 31 May 2004 15:35:02 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:40**********************@news.syd.swiftdsl.c om.au:
If IsNull(VFRO1) = True


is a valid coding style. It is particularly appropriate as a
coding style for Access, because of the problems in some versions
with code like this:

If myChkBox Then

which should always be written in the long form:

If myChkBox = True Then
or
If myChkBox.Value Then


It should also be written with the parent object explicitly
identified:

If (Me!myChkBox) Then

And the parens force evaluation.


In addition, one should never say If ... = True, but If ... <> False. This is
because, for the purposes of an If statement, any non-zero number is evaluated
as True, but the True constant is only equal to -1.
Nov 13 '05 #40

P: n/a
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:40**********************@news.syd.swiftdsl.co m.au:
> '--------------------------------------
> If IsNull(VFRO1) = True Or IsDate(VFRO1) = False Then
> GoTo data_error
Clean up the line above
Any compiler works by reducing an expression to a true or
false. Isnull(VFR01) = true must be evaluated to if true =
true, then evaluated again to true, then processed by the if.


Any compiler will do that, so there is no need for you to do
that.

Well, then Access has a broken compiler. ;) 10000 loops of 100
calls in each version shows a 25% speed improvement. by leaving off
the = true.

If IsNull(VFRO1) = True


is a valid coding style. It is particularly appropriate as a
coding style for Access, because of the problems in some
versions with code like this:

If myChkBox Then

which should always be written in the long form:

If myChkBox = True Then
or
If myChkBox.Value Then

which force a local evaluation of the the Value property of
the checkbox, rather than passing the control object to the
VB virtual machine for evaluation.
(david)


Explicit declaration of the .value of a control makes sense. I
don't know if it's faster to evaluate locally than letting VB do
it, but that wasnt WindNWaves' problem. He was saying that his code
was slow. It was slow because of redundant evaluations.

Bob Quintal


Nov 13 '05 #41

P: n/a
Dear RKC

Yes, I noticed the err thing. So far, it has never caused a problem so I
may just leave it like that (it will be a huge project to change all of
them). I used the same label for err all the way through my database and in
all my databases, because that keeps things trictly simple.

Thank you, btw, for your answer, much appreciated.
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:ST*******************@twister.nyroc.rr.com...

"WindAndWaves" <ac****@ngaru.com> wrote in message
news:3X********************@news.xtra.co.nz...
Thank you all very much for your help with the class module question.

Further to your comments, I have put together my first class module (see end
of post). My question now is how you make it compulsory to provide an
argument when initializing an instance of the ZOS class (as I call the

class
module).


Unfortunately there is no way at all to provide an argument when creating
an object from a vba class. There is an Initialize event that will run if

it has
been defined, but it takes no arguments. You have to call at least one
method of the class after initialization to provide any arguments.

I notice you use the label err in all your error handler code.
Just so you are aware, Err is a VBA object that holds runtime error
information. You might want to use a different name for your error
labels.

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
Nov 13 '05 #42

P: n/a
You can use the "find and replace" under the edit menu to replace your
label. Select "Current Project" and tick "Find Whole Word Only". I wouldn't
use "Replace All" but use "Find Next" & "Replace" to control what is being
changed. Err is an object in VBA, would you use "On Error GoTo Form"?

Stewart
"WindAndWaves" <ac****@ngaru.com> wrote in message
news:m7********************@news.xtra.co.nz...
Dear RKC

Yes, I noticed the err thing. So far, it has never caused a problem so I
may just leave it like that (it will be a huge project to change all of
them). I used the same label for err all the way through my database and in all my databases, because that keeps things trictly simple.

Thank you, btw, for your answer, much appreciated.

Nov 13 '05 #43

This discussion thread is closed

Replies have been disabled for this discussion.