Use code over and over

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

Guest

Hi,

I have some code that I would like to use over and over again.
The code is used in the WHERE part of a query. I may also apply this
principle to other code as well. It is quite long and used multiple times in
multiple forms and queries. I would therefore like to create a constant and
refer to that constant in my code.

My Question is: How do I create a constant like below, so that it is global
and can be used anywhere. That is, in any form or any module.

Example of how I would like to use it is below:

const0102 = "(((COMPANY_INFO.lic_grant)<#7/1/2002#) AND
((COMPANY_INFO.lic_sur) Is Null)) OR (((COMPANY_INFO.lic_grant)<#7/1/2002#)
AND ((COMPANY_INFO.lic_sur)>=#7/1/2001#))"

and then in the where clause of queries on various forms and reports use it
such as

SELECT * FROM COMPANY_INFO
WHERE const0102

Your assistance is appreciated.
 
You're not limited to use tables for your queries: other queries can be used
as source, too; so you can create a query and use it as if it were a *table*
for other queries. It may also be usefull to replace things like #7/1/2002#
with parameters.

Queries can also be used a Filter source, I think, but I don't remember the
exact syntax.
 
Thanks for your response, you have interpretted what I will be using my query
for, and unfortunately your interpretation is incorrect. The use of my query
is also irrelevant to my question.

My question still remains. Any assistance with regards to creating a global
constant, that is, a constant that can be used over various forms and in
various modules would be appreciated.


--
Sylvain Lafontaine said:
You're not limited to use tables for your queries: other queries can be used
as source, too; so you can create a query and use it as if it were a *table*
for other queries. It may also be usefull to replace things like #7/1/2002#
with parameters.

Queries can also be used a Filter source, I think, but I don't remember the
exact syntax.
Click to expand...
 
Dylan said:
Thanks for your response, you have interpretted what I will be using my query
for, and unfortunately your interpretation is incorrect. The use of my query
is also irrelevant to my question.

My question still remains. Any assistance with regards to creating a global
constant, that is, a constant that can be used over various forms and in
various modules would be appreciated.
Click to expand...

Create a standard module, if you don't already have one, and right below
Option Explicit

add this:

Public Const const0102 = "(((COMPANY_INFO.lic_grant)<#7/1/2002#) AND " _
& " ((COMPANY_INFO.lic_sur) Is Null)) OR
(((COMPANY_INFO.lic_grant)<#7/1/2002#)" _
& " AND ((COMPANY_INFO.lic_sur)>=#7/1/2001#))"

*NOTE: this creates a constant. To change the dates, you will have to switch to
design view*

Create your form/report with the record source but without the where clause ie
"SELECT * FROM COMPANY_INFO" . Once you have all of the fields where you want
them, save the form/report.

In the Form_Open/Report_Open event (On Open), add:

Me.RecordSource = "SELECT * FROM COMPANY_INFO WHERE " & const0102 & ";"

Close the VB editor. Now open the form/report in design view. Open the
properties dialog box. Select the Data tab and delete the record source. Save
the form/report.

Basically, you create the record source "on-the-fly".

Good luck.
 
Back
Top