Which query is running

[Phil Stanton]

I have a number of queries which use code for the output of 1 or more
fields. For example
Address:GetAddress(AddressID, True, 60)
Address ID Points to an Address in a table - Address Line1, Line 2, Line 3,
Town, County, Post Code, Country.
The True is whether to include the country and the 60 is the maximum line
length (after which a line feed is inserted). All this is to output to a
formatted RTF file for a handbook.

In addition, I have a table that can also hold parameters so for example
if I am running Query 1, I want Address:GetAddress(AddressID, True, 60)
if I am running Query 2, I want Address:GetAddress(AddressID, False, 40)

The essentials of this table are QueryName ( The query or subquery that is
calling the function) i.e. Query1 or Query2
Field Name (The field that
calls the function) i.e. Address
NewParameters
i.e. -1,60 or 0,40

The Function GetAddress first calls a routine to get up to 4 new
parameters.

Function GetParameters(QueryName As String, FieldName As String, Optional
P1, Optional P2, Optional P3, Optional P4)

Dim MyDb As Database
Dim ParamSet As Recordset
Dim QDF As QueryDef
Dim i As Integer, j As Integer

On Error GoTo GetParameters_Err

Set MyDb = CurrentDb
Set ParamSet = MyDb.OpenRecordset("QQueryParameters")
With ParamSet
Do Until .EOF
If !QueryName = QueryName And !FieldName = FieldName Then ' Right
record
GoTo ExtractParams
End If
.MoveNext
Loop

GoTo GetParameters_Exit

ExtractParams:
If !FieldParams = "" Then ' No parameter
GoTo GetParameters_Exit
End If
i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P1 = !FieldParams
GoTo GetParameters_Exit
End If
P1 = Left(!FieldParams, i)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P2 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P2 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P3 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P3 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P4 = Right(!FieldParams, Len(!FieldParams) - j)
GoTo GetParameters_Exit
End If
P4 = Mid(!FieldParams, i, i - j - 1)

GetParameters_Exit:
.Close
End With
Set ParamSet = Nothing
Set MyDb = Nothing
Exit Function

GetParameters_Err:
P1 = 0
P2 = 0
P3 = 0
P4 = 0
MsgBox Err.Description
Resume GetParameters_Exit

End Function

All this works fine except.... How can I find which query is running so that
I can pass the correct queryname to the GetParameters Function

Any help gratefully recieved

Thanks

Phil

[storrboy]

I would pass the name of the query as an argument. I don't think you
can reference the calling proc.

[Tom van Stiphout]

On Tue, 13 Mar 2007 22:54:58 -0000, "Phil Stanton"
<ph**@stantonfamily.co.ukwrote:

Hi Phil,
I'm trying to follow your message. "Address:GetAddress" is the name of
a query or is it the name of a function?

I don't understand the looping in function GetParameters. Why can't
the query be a parameter query taking the QueryName as an argument,
and thus find the one and only record in the table?

Looking for commas? Use the Split function to tokenize your string.

I'm confused how you would not know what query to run.

-Tom.

>I have a number of queries which use code for the output of 1 or more
fields. For example
Address:GetAddress(AddressID, True, 60)
Address ID Points to an Address in a table - Address Line1, Line 2, Line 3,
Town, County, Post Code, Country.
The True is whether to include the country and the 60 is the maximum line
length (after which a line feed is inserted). All this is to output to a
formatted RTF file for a handbook.

In addition, I have a table that can also hold parameters so for example
if I am running Query 1, I want Address:GetAddress(AddressID, True, 60)
if I am running Query 2, I want Address:GetAddress(AddressID, False, 40)

The essentials of this table are QueryName ( The query or subquery that is
calling the function) i.e. Query1 or Query2
Field Name (The field that
calls the function) i.e. Address
NewParameters
i.e. -1,60 or 0,40

The Function GetAddress first calls a routine to get up to 4 new
parameters.

Function GetParameters(QueryName As String, FieldName As String, Optional
P1, Optional P2, Optional P3, Optional P4)

Dim MyDb As Database
Dim ParamSet As Recordset
Dim QDF As QueryDef
Dim i As Integer, j As Integer

On Error GoTo GetParameters_Err

Set MyDb = CurrentDb
Set ParamSet = MyDb.OpenRecordset("QQueryParameters")
With ParamSet
Do Until .EOF
If !QueryName = QueryName And !FieldName = FieldName Then ' Right
record
GoTo ExtractParams
End If
.MoveNext
Loop

GoTo GetParameters_Exit

ExtractParams:
If !FieldParams = "" Then ' No parameter
GoTo GetParameters_Exit
End If
i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P1 = !FieldParams
GoTo GetParameters_Exit
End If
P1 = Left(!FieldParams, i)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P2 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P2 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P3 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P3 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P4 = Right(!FieldParams, Len(!FieldParams) - j)
GoTo GetParameters_Exit
End If
P4 = Mid(!FieldParams, i, i - j - 1)

GetParameters_Exit:
.Close
End With
Set ParamSet = Nothing
Set MyDb = Nothing
Exit Function

GetParameters_Err:
P1 = 0
P2 = 0
P3 = 0
P4 = 0
MsgBox Err.Description
Resume GetParameters_Exit

End Function

All this works fine except.... How can I find which query is running so that
I can pass the correct queryname to the GetParameters Function

Any help gratefully recieved

Thanks

Phil

[Phil Stanton]

Thanks for coming back, Tom

GetAddress is the name of a function which combines and formats the address
lines, Town, County, Postcode etc and returns the field Address

ie Function GetAddress(AddressID as Long, IncludeCountry as Boolean,
LineLength as String) as String

Dim MyStg as string

MyStg = AddressLine 1 & Town & Blah Blah
If Len(MyStg) LineLength
do all sorts of things
end if
GetAddress = MyStg
Exit function

The reason for adopting this approach is that I don't want users to modify
the parameters in the query. So I give them a form that allows then to enter
new parameters with checks that the right number of parameters are enterd
and will get round to checking the values are sensible. This should allow
them to format the output for whatever size of booklet we need to produce.

The QQueryParameters look like this

ClubID QueryID FieldName FieldParams ParamDetail QueryName
6 5 Phone 70 Line length LstHomesFull
6 2 FormatAdd 70 Line length LstHomesWFYC
6 2 NameContact 70,2 Line length, 0 = Separate Line: 1 = each number
on line: 2 = Numbers combined LstHomesWFYC
6 3 Boats -1,-1,2,90 Main Boat, Show Boat Class, 1 = Feet 2 = Meters,
Line Length WhoseMainBoat
Each query could contain more than 1 output field that need parameters to be
modified.

This is part of a Club database. Actually there are about 5 clubs involved,
same Front end, different back ends for each club.
Each Club wants their handbooks in different formats, and it is getting a
pain having a different set of queries for each Club so that each get the
information they want in the format they want. Hence this QQueryParameters
which holds the parameter information for each Club.

Thanks for the tip about Split - never used it - will give it a go

Thanks

Phil
...
"Tom van Stiphout" <no*************@cox.netwrote in message
news:7q********************************@4ax.com...

On Tue, 13 Mar 2007 22:54:58 -0000, "Phil Stanton"
<ph**@stantonfamily.co.ukwrote:

Hi Phil,
I'm trying to follow your message. "Address:GetAddress" is the name of
a query or is it the name of a function?

I don't understand the looping in function GetParameters. Why can't
the query be a parameter query taking the QueryName as an argument,
and thus find the one and only record in the table?

Looking for commas? Use the Split function to tokenize your string.

I'm confused how you would not know what query to run.

-Tom.

>>I have a number of queries which use code for the output of 1 or more
fields. For example
Address:GetAddress(AddressID, True, 60)
Address ID Points to an Address in a table - Address Line1, Line 2, Line
3,
Town, County, Post Code, Country.
The True is whether to include the country and the 60 is the maximum line
length (after which a line feed is inserted). All this is to output to a
formatted RTF file for a handbook.

In addition, I have a table that can also hold parameters so for example
if I am running Query 1, I want Address:GetAddress(AddressID, True, 60)
if I am running Query 2, I want Address:GetAddress(AddressID, False, 40)

The essentials of this table are QueryName ( The query or subquery that is
calling the function) i.e. Query1 or Query2
Field Name (The field that
calls the function) i.e. Address
NewParameters
i.e. -1,60 or 0,40

The Function GetAddress first calls a routine to get up to 4 new
parameters.

Function GetParameters(QueryName As String, FieldName As String, Optional
P1, Optional P2, Optional P3, Optional P4)

Dim MyDb As Database
Dim ParamSet As Recordset
Dim QDF As QueryDef
Dim i As Integer, j As Integer

On Error GoTo GetParameters_Err

Set MyDb = CurrentDb
Set ParamSet = MyDb.OpenRecordset("QQueryParameters")
With ParamSet
Do Until .EOF
If !QueryName = QueryName And !FieldName = FieldName Then ' Right
record
GoTo ExtractParams
End If
.MoveNext
Loop

GoTo GetParameters_Exit

ExtractParams:
If !FieldParams = "" Then ' No parameter
GoTo GetParameters_Exit
End If
i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P1 = !FieldParams
GoTo GetParameters_Exit
End If
P1 = Left(!FieldParams, i)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P2 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P2 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P3 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P3 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P4 = Right(!FieldParams, Len(!FieldParams) - j)
GoTo GetParameters_Exit
End If
P4 = Mid(!FieldParams, i, i - j - 1)

GetParameters_Exit:
.Close
End With
Set ParamSet = Nothing
Set MyDb = Nothing
Exit Function

GetParameters_Err:
P1 = 0
P2 = 0
P3 = 0
P4 = 0
MsgBox Err.Description
Resume GetParameters_Exit

End Function

All this works fine except.... How can I find which query is running so
that
I can pass the correct queryname to the GetParameters Function

Any help gratefully recieved

Thanks

Phil