Sources for query parameters

[Petr Danes]

A query can access values on a form, when the form is open. Is it possible
for a query to access the values in:

1. A global variable?
2. A document variable?
3. A custom document property?

If so, what is the syntax?

I have sets of queries that I run from code. Some have parameters, some do
not and of the ones that do, not all have the same parameters. Using
standard parameters queries, I have to feed the values manually - my query
execution routine must know the structure of each query, i.e., how many
parameters and what they're called, or the order in which the parameters are
requested. This is not ideal - I would prefer to set up some values in the
section of code that knows something about the current situation, then call
my generic ExecQueries routine with only the information about which queries
it is to execute.

It is possible to create a custom form which would I would open and load
with 'parameter' values as needed, but that seems a little clunky. The
internal variables would seem a cleaner solution, but I have so far been
unable to access such values.

Petr

 

[Jeff Boyce]

One approach might be to create one/more functions that 'get' the value of
the global. Then use that function in your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Petr Danes]

Hello Jeff,

yes, that would probably work, but would it not call my VBA function for
every returned record? That could get quite slow in large queries.

Petr

 

[KARL DEWEY]

You could have a table with a field for parameter and one or more check
boxes. The check box to indicate if the parameter was relavant. This table
could be viewed on a continous form for updating and an option group to
select which check box to use for the query criteria.
The query would use the parameter as criteria. Then another field of the
query would contain the option group and parameter table check boxes.

WhichParm: [Forms]![YourForm]![Frame1]
Criteria Row: IIF(ParamTable.CheckField1 = -1, 1, 0)
IIF(ParamTable.CheckField2 = -1, 2, 0)
IIF(ParamTable.CheckField3 = -1, 3, 0)

Selecting option group 2 will match check box 2.

 

[Jeff Boyce]

Yes.

"Performance" is in the eye of the beholder. How long do your large queries
take now? How long using a function to return a selection criterion?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Petr Danes]

That varies. Some have no perceptible run time, others take up to fifteen or
twenty seconds, usually the ones that already run some VBA code. But those
are usually ones that are for my use in some data conversion process, not
tied to a user's form, so the run time is not all that important and your
suggestion is certainly a useable technique for many of my needs.

But I'd still be interested if there is a way to access any of the three
types of data store I mentioned in my original post, that is, global
variables, document variables and custom document properties.

Thanks,

Petr

 

[Petr Danes]

Hello Karl,

thank you for the idea, that is a bit more complicated than I had
envisioned for a form feeding my query. My thought was a simple form with a
few textboxes, not a continuous form with option groups. I'll have to try
yours out to make sure I understand it all.

But I'd still be interested if there is a way to access any of the three
types of data store I mentioned in my original post, that is, global
variables, document variables and custom document properties.

Thanks,

Petr

You could have a table with a field for parameter and one or more check
boxes. The check box to indicate if the parameter was relavant. This
table
could be viewed on a continous form for updating and an option group to
select which check box to use for the query criteria.
The query would use the parameter as criteria. Then another field of the
query would contain the option group and parameter table check boxes.

WhichParm: [Forms]![YourForm]![Frame1]
Criteria Row: IIF(ParamTable.CheckField1 = -1, 1, 0)
IIF(ParamTable.CheckField2 = -1, 2, 0)
IIF(ParamTable.CheckField3 = -1, 3, 0)

Selecting option group 2 will match check box 2.

A query can access values on a form, when the form is open. Is it
possible
for a query to access the values in:

1. A global variable?
2. A document variable?
3. A custom document property?

If so, what is the syntax?

I have sets of queries that I run from code. Some have parameters, some
do
not and of the ones that do, not all have the same parameters. Using
standard parameters queries, I have to feed the values manually - my
query
execution routine must know the structure of each query, i.e., how many
parameters and what they're called, or the order in which the parameters
are
requested. This is not ideal - I would prefer to set up some values in
the
section of code that knows something about the current situation, then
call
my generic ExecQueries routine with only the information about which
queries
it is to execute.

It is possible to create a custom form which would I would open and load
with 'parameter' values as needed, but that seems a little clunky. The
internal variables would seem a cleaner solution, but I have so far been
unable to access such values.

Petr


--
This e-mail address is fake, to keep spammers and their address
harvesters
out of my hair. If you need to get in touch personally, I am 'pdanes' and
I
use yahoo mail. But please use the newsgroups whenever possible, so that
all
may benefit from the exchange of ideas.

 

[Dale_Fye via AccessMonster.com]

1. global variable: Not directly
2. document variable? don't think I've ever heard that term
3. custom document property. Can you give an example?

I generally use a function similar to Jeff's comment, but I leave out the
global variable and use a static variable within the function instead. The
thing I like about this technique is that I can set this value in the
immediate window while testing code

Public Function fnAdminID(Optional SomeValue as Variant = NULL) as integer

Static myAdminID as long

if isnull(SomeValue) = false then myAdminID = SomeValue
fnAdminID = myAdminID

End Function

If I want to set the value of fnAdminID, I pass it a value, otherwise, I just
call it without a parameter.

In my tests, Access only calls the function once, unless you are passing it a
value.

HTH
Dale

 

[John Spencer MVP]

If all you are doing is using the function in the where clause it may get
called only once.

Select ... From SomeTable ... Where Field1 = fGetSomeValue()

The query "interpreter" is smart enough to only execute fGetSomeValue() once
since there are no changing arguments to the function.


Select ... From SomeTable ... Where Field1 = fGetSomeValue([SomeField])

In the above the function will get called for every record in the database
since there is a parameter that varies with each record.


Select ... From SomeTable ... Where Field1 = fGetSomeValue(1)

In the above, I'm not positive but I believe that the interpreter is again
smart enough to know that the result will not vary and therefore calls the
function only once.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Petr Danes]

Hi John,

I think you're right about the calling, I seem to remember those results
from tracking a misbehaving function a while back. I'll test it again to
make sure, but that would be a way of achieving my goal - have a module full
of nothing but one-line functions, which do nothing but return the value of
a particular global variable.

It's not quite as clean as accessing the variable directly from SQL, but
certainly an effective solution.

Thanks,

Petr

If all you are doing is using the function in the where clause it may get
called only once.

Select ... From SomeTable ... Where Field1 = fGetSomeValue()

The query "interpreter" is smart enough to only execute fGetSomeValue()
once since there are no changing arguments to the function.


Select ... From SomeTable ... Where Field1 = fGetSomeValue([SomeField])

In the above the function will get called for every record in the database
since there is a parameter that varies with each record.


Select ... From SomeTable ... Where Field1 = fGetSomeValue(1)

In the above, I'm not positive but I believe that the interpreter is again
smart enough to know that the result will not vary and therefore calls the
function only once.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello Jeff,

yes, that would probably work, but would it not call my VBA function for
every returned record? That could get quite slow in large queries.

Petr

 

[Petr Danes]

Hi Dale,

1. global variable: Not directly

Rats. Oh well.

2. document variable? don't think I've ever heard that term

Sorry, that was a bit of schizophrenia on my part. They're used in Word,
rather than Access, where you can put field definitions into your document
that retrieve values from these variables. I develop in several Office
applications and occasionally get fuddled about which object model contains
what.

The syntax in Word is:
ActiveDocument.Variables.Add "MyVar", "ABC"
Once executed, the variable becomes part of the Word document and its value
remains set when the document is saved, unlike globals or locals. Values may
be read or changed through code, and read in the document via fields, or
deleted through code. They are not directly visible anywhere in the UI.

The Access object model also includes a document object, but it's not the
same thing as the Word document and it does not have a Variables collection
among its properties.

3. custom document property. Can you give an example?

You bet. Here're a couple of functions that set and get one such property.
The comments are in Czech, the code refers to a few global variables and
includes a bit of error handling to deal with the property not yet created,
but you should get the drift. If not, post back and I'll step you through
them. They function similarly to the Word document variables, where you can
create, set, read and delete them from code, and their values remain set
when the Db is closed. Also, they are visible through the UI in the main
database window, under File / Properties, Custom tab. I don't know if they
can be accessed directly by the SQL in a query, that was actually my
original question. None of my experiments in that direction have yielded any
positive results.

Petr



Public Function GetRootFolder$()
' Podíváme se jestli existuje uživatelská vlastnost nazvaná Zdroj. Jestli
ano, pokusíme se vylovit obsah.
' Když tato vlastnost neexistuje, vytvoøíme ji.
' Používá se jako koøenová složka zdrojových dat. Když nic neexistuje,
nabídneme složku ve které žije databáze.
On Error Resume Next
GetRootFolder =
CurrentDb.Containers(1).Documents(Uzivatelske).Properties(RootFolderPropName)
If Err = 3270 Then
GetRootFolder = CurrentDb.Name
GetRootFolder = Left$(GetRootFolder, InStrRev(GetRootFolder,
vbBackSlash))
CurrentDb.Containers(1).Documents(Uzivatelske).Properties.Append
CurrentDb.Containers(1).Documents(Uzivatelske).CreateProperty(RootFolderPropName,
dbText, GetRootFolder)
End If
End Function



Public Sub SetRootFolder(a$)
' Uloží koøenovou složku dat. Pøiplácne zpìtné lomítko na konec, jestli tam
už není.
On Error Resume Next
CurrentDb.Containers(1).Documents(Uzivatelske).Properties(RootFolderPropName)
= a & IIf(Right$(a, 1) = vbBackSlash, vbNullString, vbBackSlash)
If Err = 3270 Then
CurrentDb.Containers(1).Documents(Uzivatelske).Properties.Append
CurrentDb.Containers(1).Documents(Uzivatelske).CreateProperty(RootFolderPropName,
dbText, a & IIf(Right$(a, 1) = vbBackSlash, vbNullString, vbBackSlash))
End Sub

 

[Dale_Fye via AccessMonster.com]

Petr,

You can create new properties for the database, and store values in them.
Database properties are retained in the application, and can be addressed
directly, as you indicate in your example.

Public Sub SetProperty(PropertyTitle As String, PropertyValue As Variant)

Dim prp As Property

On Error Resume Next
Select Case VarType(PropertyValue)
Case vbString
Set prp = CurrentDb.CreateProperty(PropertyTitle, dbText,
PropertyValue)
Case vbInteger, vbLong
Set prp = CurrentDb.CreateProperty(PropertyTitle, dbLong,
PropertyValue)
Case Else
MsgBox "SetProperty only execepts strings and integer values"
End Select

CurrentDb.Properties.Append prp
If Err.Number = 3367 Then
CurrentDb.Properties(PropertyTitle).Value = PropertyValue
End If

End Sub

Public Function GetProperty(PropertyTitle As String) As Variant

On Error Resume Next

GetProperty = CurrentDb.Properties(PropertyTitle).Value
If Err.Number <> 0 Then
MsgBox Err.Number, Err.Description
GetProperty = Null
end if

End Function

HTH
Dale

Hi Dale,

1. global variable: Not directly

Rats. Oh well.

2. document variable? don't think I've ever heard that term

Sorry, that was a bit of schizophrenia on my part. They're used in Word,
rather than Access, where you can put field definitions into your document
that retrieve values from these variables. I develop in several Office
applications and occasionally get fuddled about which object model contains
what.

The syntax in Word is:
ActiveDocument.Variables.Add "MyVar", "ABC"
Once executed, the variable becomes part of the Word document and its value
remains set when the document is saved, unlike globals or locals. Values may
be read or changed through code, and read in the document via fields, or
deleted through code. They are not directly visible anywhere in the UI.

The Access object model also includes a document object, but it's not the
same thing as the Word document and it does not have a Variables collection
among its properties.

3. custom document property. Can you give an example?

You bet. Here're a couple of functions that set and get one such property.
The comments are in Czech, the code refers to a few global variables and
includes a bit of error handling to deal with the property not yet created,
but you should get the drift. If not, post back and I'll step you through
them. They function similarly to the Word document variables, where you can
create, set, read and delete them from code, and their values remain set
when the Db is closed. Also, they are visible through the UI in the main
database window, under File / Properties, Custom tab. I don't know if they
can be accessed directly by the SQL in a query, that was actually my
original question. None of my experiments in that direction have yielded any
positive results.

Petr

Public Function GetRootFolder$()
' Podíváme se jestli existuje uživatelská vlastnost nazvaná Zdroj. Jestli
ano, pokusíme se vylovit obsah.
' Když tato vlastnost neexistuje, vytvoøíme ji.
' Používá se jako koøenová složka zdrojových dat. Když nic neexistuje,
nabídneme složku ve které žije databáze.
On Error Resume Next
GetRootFolder =
CurrentDb.Containers(1).Documents(Uzivatelske).Properties(RootFolderPropName)
If Err = 3270 Then
GetRootFolder = CurrentDb.Name
GetRootFolder = Left$(GetRootFolder, InStrRev(GetRootFolder,
vbBackSlash))
CurrentDb.Containers(1).Documents(Uzivatelske).Properties.Append
CurrentDb.Containers(1).Documents(Uzivatelske).CreateProperty(RootFolderPropName,
dbText, GetRootFolder)
End If
End Function

Public Sub SetRootFolder(a$)
' Uloží koøenovou složku dat. Pøiplácne zpìtné lomítko na konec, jestli tam
už není.
On Error Resume Next
CurrentDb.Containers(1).Documents(Uzivatelske).Properties(RootFolderPropName)
= a & IIf(Right$(a, 1) = vbBackSlash, vbNullString, vbBackSlash)
If Err = 3270 Then
CurrentDb.Containers(1).Documents(Uzivatelske).Properties.Append
CurrentDb.Containers(1).Documents(Uzivatelske).CreateProperty(RootFolderPropName,
dbText, a & IIf(Right$(a, 1) = vbBackSlash, vbNullString, vbBackSlash))
End Sub

1. global variable: Not directly
2. document variable? don't think I've ever heard that term

[quoted text clipped - 54 lines]

 

[Dale_Fye via AccessMonster.com]

BTW,

You cannot call currentdb.Properties("PropertyName") from a query, so you
will still need a wrapper function to return the value in that "variable".

Petr,

You can create new properties for the database, and store values in them.
Database properties are retained in the application, and can be addressed
directly, as you indicate in your example.

Public Sub SetProperty(PropertyTitle As String, PropertyValue As Variant)

Dim prp As Property

On Error Resume Next
Select Case VarType(PropertyValue)
Case vbString
Set prp = CurrentDb.CreateProperty(PropertyTitle, dbText,
PropertyValue)
Case vbInteger, vbLong
Set prp = CurrentDb.CreateProperty(PropertyTitle, dbLong,
PropertyValue)
Case Else
MsgBox "SetProperty only execepts strings and integer values"
End Select

CurrentDb.Properties.Append prp
If Err.Number = 3367 Then
CurrentDb.Properties(PropertyTitle).Value = PropertyValue
End If

End Sub

Public Function GetProperty(PropertyTitle As String) As Variant

On Error Resume Next

GetProperty = CurrentDb.Properties(PropertyTitle).Value
If Err.Number <> 0 Then
MsgBox Err.Number, Err.Description
GetProperty = Null
end if

End Function

HTH
Dale

Hi Dale,

[quoted text clipped - 73 lines]

 

[Petr Danes]

Yeah, that's what I've come up with as well. I was hoping that I had
overlooked something, and that there -IS- a way to get them directly from a
query, but probably not. Thanks for the input.

Petr

BTW,

You cannot call currentdb.Properties("PropertyName") from a query, so you
will still need a wrapper function to return the value in that "variable".

Petr,

You can create new properties for the database, and store values in them.
Database properties are retained in the application, and can be addressed
directly, as you indicate in your example.

Public Sub SetProperty(PropertyTitle As String, PropertyValue As Variant)

Dim prp As Property

On Error Resume Next
Select Case VarType(PropertyValue)
Case vbString
Set prp = CurrentDb.CreateProperty(PropertyTitle, dbText,
PropertyValue)
Case vbInteger, vbLong
Set prp = CurrentDb.CreateProperty(PropertyTitle, dbLong,
PropertyValue)
Case Else
MsgBox "SetProperty only execepts strings and integer values"
End Select

CurrentDb.Properties.Append prp
If Err.Number = 3367 Then
CurrentDb.Properties(PropertyTitle).Value = PropertyValue
End If

End Sub

Public Function GetProperty(PropertyTitle As String) As Variant

On Error Resume Next

GetProperty = CurrentDb.Properties(PropertyTitle).Value
If Err.Number <> 0 Then
MsgBox Err.Number, Err.Description
GetProperty = Null
end if

End Function

HTH
Dale

Hi Dale,

[quoted text clipped - 73 lines]

Petr