Stop a query

[Phil Stanton]

I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address. The
same query is used as the RecordSource of lots of similar reports, but all
with different sized Address text boxes. For the function to work, the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about 1000 times). How can I neatly abort running the query if either no
Reports are open, or a Report is open, but the particular Text Box is not on
the report (i.e. the wrong Report is open)

TIA

Phil

[Len Robichaud]

Try this...Starting with the report Closed, From your Procedure, Call
ControlFound ("YourReportName", "YourControlName")
the Function will find the report, open it in design mode, check for the
control and if the control is found, returns ControlFound = True
Then you can act on the information, Don't forget to Close the Report and
move on to the next report.

Good luck
Len Robichaud

------------------------------------------------------------
Option Compare Database
Option Explicit

'Returns True if the specified report is open in Design View and The Control
is found.
'Any Error returns a False

Function ControlFound(ByVal strReportName As String, ByVal strControlName As
String) As Boolean
On Error GoTo ControlFound_Error

Const conObjStateClosed = 0
Dim strControl As Variant
ControlFound = False

'If the Report Exists and it is currently closed, Open it in Design mode
If SysCmd(acSysCmdGetObjectState, acReport, strReportName) =
conObjStateClosed Then
DoCmd.OpenReport strReportName, acViewDesign
'Now check to see if the control exists on the report
strControl = Reports(strReportName).Controls(strControlName).Na me
If Len(strControl) 0 Then ControlFound = True
End If

ControlFound_Exit:
Exit Function

ControlFound_Error:
ControlFound = False
Resume ControlFound_Exit

End Function
---------------------------------------------------------------

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...

>I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address.
The same query is used as the RecordSource of lots of similar reports, but
all with different sized Address text boxes. For the function to work, the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about 1000 times). How can I neatly abort running the query if either no
Reports are open, or a Report is open, but the particular Text Box is not
on the report (i.e. the wrong Report is open)

TIA

Phil

[Phil Stanton]

Thanks for that, Len. That works fine, but how do I terminate the query from
continuing to run. It is the running of the query that initiates checking
the report is open and that the control exists. If the ControlFound is
False, I want to abort running the query that called it

Thanks

Phil
"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46***********************@news.corp.moreusene t.net...

Try this...Starting with the report Closed, From your Procedure, Call
ControlFound ("YourReportName", "YourControlName")
the Function will find the report, open it in design mode, check for the
control and if the control is found, returns ControlFound = True
Then you can act on the information, Don't forget to Close the Report and
move on to the next report.

Good luck
Len Robichaud

------------------------------------------------------------
Option Compare Database
Option Explicit

'Returns True if the specified report is open in Design View and The
Control is found.
'Any Error returns a False

Function ControlFound(ByVal strReportName As String, ByVal strControlName
As String) As Boolean
On Error GoTo ControlFound_Error

Const conObjStateClosed = 0
Dim strControl As Variant
ControlFound = False

'If the Report Exists and it is currently closed, Open it in Design mode
If SysCmd(acSysCmdGetObjectState, acReport, strReportName) =
conObjStateClosed Then
DoCmd.OpenReport strReportName, acViewDesign
'Now check to see if the control exists on the report
strControl = Reports(strReportName).Controls(strControlName).Na me
If Len(strControl) 0 Then ControlFound = True
End If

ControlFound_Exit:
Exit Function

ControlFound_Error:
ControlFound = False
Resume ControlFound_Exit

End Function
---------------------------------------------------------------

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...

>>I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address.
The same query is used as the RecordSource of lots of similar reports, but
all with different sized Address text boxes. For the function to work, the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about 1000 times). How can I neatly abort running the query if either no
Reports are open, or a Report is open, but the particular Text Box is not
on the report (i.e. the wrong Report is open)

TIA

Phil

[Len Robichaud]

Explain in more detail what you are trying to accomplish and the steps in
your process. You may need to incorporate another procedure. Talk me
through your process.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...
Thanks for that, Len. That works fine, but how do I terminate the query from
continuing to run. It is the running of the query that initiates checking
the report is open and that the control exists. If the ControlFound is
False, I want to abort running the query that called it

Thanks

Phil
"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46***********************@news.corp.moreusene t.net...

Try this...Starting with the report Closed, From your Procedure, Call
ControlFound ("YourReportName", "YourControlName")
the Function will find the report, open it in design mode, check for the
control and if the control is found, returns ControlFound = True
Then you can act on the information, Don't forget to Close the Report and
move on to the next report.

Good luck
Len Robichaud

------------------------------------------------------------
Option Compare Database
Option Explicit

'Returns True if the specified report is open in Design View and The
Control is found.
'Any Error returns a False

Function ControlFound(ByVal strReportName As String, ByVal strControlName
As String) As Boolean
On Error GoTo ControlFound_Error

Const conObjStateClosed = 0
Dim strControl As Variant
ControlFound = False

'If the Report Exists and it is currently closed, Open it in Design mode
If SysCmd(acSysCmdGetObjectState, acReport, strReportName) =
conObjStateClosed Then
DoCmd.OpenReport strReportName, acViewDesign
'Now check to see if the control exists on the report
strControl = Reports(strReportName).Controls(strControlName).Na me
If Len(strControl) 0 Then ControlFound = True
End If

ControlFound_Exit:
Exit Function

ControlFound_Error:
ControlFound = False
Resume ControlFound_Exit

End Function
---------------------------------------------------------------

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...

>>I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address.
The same query is used as the RecordSource of lots of similar reports, but
all with different sized Address text boxes. For the function to work, the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about 1000 times). How can I neatly abort running the query if either no
Reports are open, or a Report is open, but the particular Text Box is not
on the report (i.e. the wrong Report is open)

TIA

Phil

[Phil Stanton]

Thanks for coming back to me, Len

I am trying to create a number of similar Club directories - Names,
addresses, phone, email etc. I use the same front end DB for all the clubs
and a different BE for each club. Each Club wants their directory in a
different format so for example, one Club wants their directory in a 1/2 A5
booklet form. One Club in particular wants the Surname and address on 1 line
and First name, mobile and emails of each of the family members on
subsequent lines indented in from the Surname.To make the booklet neat, for
example if the address is longer than it's text box, I want to ensure that
the second line starts at a space, and doesn't split a word. With the names
I want to line up all the first names and phone numbers, so have to work out
the length of the Surname so that all the First names are suitably indented
and then work out the length of the longest first name so that the phone
numbers all line up.
I Am using Stephen Lebans modTextHeightWidth to calculate spaces required
and using all sorts of manipulations to combine say 5 address fields into 1
concatenated address.

The query that is the record source for each of these Club Directory reports
calls a number of functions to combine the information in different ways
depending on parameters it gets passed from a hidden form. It also needs the
information from the intended report to get the field.Lengths so that the
correct formatting can be applied. What I am trying to do is stop the query
running if it can't find the necessary information from the report as it
process the first record. Otherwise I get the error message repeated 1000
times for each record. Gets rather boring

Probably a much simpler way of doing it, with 1 query per report - but
messy. 6 Clubs, 2 or 3 similar reports each could be 18 queries and 18
reports. There must be a more elegant way.

Thanks

Phil

"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46*********************@news.corp.moreusenet. net...

Explain in more detail what you are trying to accomplish and the steps in
your process. You may need to incorporate another procedure. Talk me
through your process.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...
Thanks for that, Len. That works fine, but how do I terminate the query
from
continuing to run. It is the running of the query that initiates checking
the report is open and that the control exists. If the ControlFound is
False, I want to abort running the query that called it

Thanks

Phil
"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46***********************@news.corp.moreusene t.net...

>Try this...Starting with the report Closed, From your Procedure, Call
ControlFound ("YourReportName", "YourControlName")
the Function will find the report, open it in design mode, check for the
control and if the control is found, returns ControlFound = True
Then you can act on the information, Don't forget to Close the Report and
move on to the next report.

Good luck
Len Robichaud

------------------------------------------------------------
Option Compare Database
Option Explicit

'Returns True if the specified report is open in Design View and The
Control is found.
'Any Error returns a False

Function ControlFound(ByVal strReportName As String, ByVal strControlName
As String) As Boolean
On Error GoTo ControlFound_Error

Const conObjStateClosed = 0
Dim strControl As Variant
ControlFound = False

'If the Report Exists and it is currently closed, Open it in Design mode
If SysCmd(acSysCmdGetObjectState, acReport, strReportName) =
conObjStateClosed Then
DoCmd.OpenReport strReportName, acViewDesign
'Now check to see if the control exists on the report
strControl = Reports(strReportName).Controls(strControlName).Na me
If Len(strControl) 0 Then ControlFound = True
End If

ControlFound_Exit:
Exit Function

ControlFound_Error:
ControlFound = False
Resume ControlFound_Exit

End Function
---------------------------------------------------------------

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...

>>>I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address.
The same query is used as the RecordSource of lots of similar reports,
but
all with different sized Address text boxes. For the function to work,
the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about 1000 times). How can I neatly abort running the query if either no
Reports are open, or a Report is open, but the particular Text Box is not
on the report (i.e. the wrong Report is open)

TIA

Phil

[Len Robichaud]

Here is my suggestion.
In the Backend database create a new table of report parameters
Let that table have one record with as many fields as is necessary to store
all of the data parameters that your report queries need.
Link that to the front end and use DLookup(s) to retrieve the data for your
queries instead of opening the reports.
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...
Thanks for coming back to me, Len

I am trying to create a number of similar Club directories - Names,
addresses, phone, email etc. I use the same front end DB for all the clubs
and a different BE for each club. Each Club wants their directory in a
different format so for example, one Club wants their directory in a 1/2 A5
booklet form. One Club in particular wants the Surname and address on 1 line
and First name, mobile and emails of each of the family members on
subsequent lines indented in from the Surname.To make the booklet neat, for
example if the address is longer than it's text box, I want to ensure that
the second line starts at a space, and doesn't split a word. With the names
I want to line up all the first names and phone numbers, so have to work out
the length of the Surname so that all the First names are suitably indented
and then work out the length of the longest first name so that the phone
numbers all line up.
I Am using Stephen Lebans modTextHeightWidth to calculate spaces required
and using all sorts of manipulations to combine say 5 address fields into 1
concatenated address.

The query that is the record source for each of these Club Directory reports
calls a number of functions to combine the information in different ways
depending on parameters it gets passed from a hidden form. It also needs the
information from the intended report to get the field.Lengths so that the
correct formatting can be applied. What I am trying to do is stop the query
running if it can't find the necessary information from the report as it
process the first record. Otherwise I get the error message repeated 1000
times for each record. Gets rather boring

Probably a much simpler way of doing it, with 1 query per report - but
messy. 6 Clubs, 2 or 3 similar reports each could be 18 queries and 18
reports. There must be a more elegant way.

Thanks

Phil

"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46*********************@news.corp.moreusenet. net...

Explain in more detail what you are trying to accomplish and the steps in
your process. You may need to incorporate another procedure. Talk me
through your process.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...
Thanks for that, Len. That works fine, but how do I terminate the query
from
continuing to run. It is the running of the query that initiates checking
the report is open and that the control exists. If the ControlFound is
False, I want to abort running the query that called it

Thanks

Phil
"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46***********************@news.corp.moreusene t.net...

>Try this...Starting with the report Closed, From your Procedure, Call
ControlFound ("YourReportName", "YourControlName")
the Function will find the report, open it in design mode, check for the
control and if the control is found, returns ControlFound = True
Then you can act on the information, Don't forget to Close the Report and
move on to the next report.

Good luck
Len Robichaud

------------------------------------------------------------
Option Compare Database
Option Explicit

'Returns True if the specified report is open in Design View and The
Control is found.
'Any Error returns a False

Function ControlFound(ByVal strReportName As String, ByVal strControlName
As String) As Boolean
On Error GoTo ControlFound_Error

Const conObjStateClosed = 0
Dim strControl As Variant
ControlFound = False

'If the Report Exists and it is currently closed, Open it in Design mode
If SysCmd(acSysCmdGetObjectState, acReport, strReportName) =
conObjStateClosed Then
DoCmd.OpenReport strReportName, acViewDesign
'Now check to see if the control exists on the report
strControl = Reports(strReportName).Controls(strControlName).Na me
If Len(strControl) 0 Then ControlFound = True
End If

ControlFound_Exit:
Exit Function

ControlFound_Error:
ControlFound = False
Resume ControlFound_Exit

End Function
---------------------------------------------------------------

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...

>>>I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address.
The same query is used as the RecordSource of lots of similar reports,
but
all with different sized Address text boxes. For the function to work,
the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about 1000 times). How can I neatly abort running the query if either no
Reports are open, or a Report is open, but the particular Text Box is not
on the report (i.e. the wrong Report is open)

TIA

Phil

[Phil Stanton]

Ok, Len I'll give it a try

Thanks

Phil

"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46***********************@news.corp.moreusene t.net...

Here is my suggestion.
In the Backend database create a new table of report parameters
Let that table have one record with as many fields as is necessary to
store
all of the data parameters that your report queries need.
Link that to the front end and use DLookup(s) to retrieve the data for
your
queries instead of opening the reports.
"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...
Thanks for coming back to me, Len

I am trying to create a number of similar Club directories - Names,
addresses, phone, email etc. I use the same front end DB for all the clubs
and a different BE for each club. Each Club wants their directory in a
different format so for example, one Club wants their directory in a 1/2
A5
booklet form. One Club in particular wants the Surname and address on 1
line
and First name, mobile and emails of each of the family members on
subsequent lines indented in from the Surname.To make the booklet neat,
for
example if the address is longer than it's text box, I want to ensure
that
the second line starts at a space, and doesn't split a word. With the
names
I want to line up all the first names and phone numbers, so have to work
out
the length of the Surname so that all the First names are suitably
indented
and then work out the length of the longest first name so that the phone
numbers all line up.
I Am using Stephen Lebans modTextHeightWidth to calculate spaces required
and using all sorts of manipulations to combine say 5 address fields into
1
concatenated address.

The query that is the record source for each of these Club Directory
reports
calls a number of functions to combine the information in different ways
depending on parameters it gets passed from a hidden form. It also needs
the
information from the intended report to get the field.Lengths so that the
correct formatting can be applied. What I am trying to do is stop the
query
running if it can't find the necessary information from the report as it
process the first record. Otherwise I get the error message repeated 1000
times for each record. Gets rather boring

Probably a much simpler way of doing it, with 1 query per report - but
messy. 6 Clubs, 2 or 3 similar reports each could be 18 queries and 18
reports. There must be a more elegant way.

Thanks

Phil

"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46*********************@news.corp.moreusenet. net...

>Explain in more detail what you are trying to accomplish and the steps in
your process. You may need to incorporate another procedure. Talk me
through your process.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...
Thanks for that, Len. That works fine, but how do I terminate the query
from
continuing to run. It is the running of the query that initiates checking
the report is open and that the control exists. If the ControlFound is
False, I want to abort running the query that called it

Thanks

Phil
"Len Robichaud" <le***********@rqwproserv.comwrote in message
news:46***********************@news.corp.moreusen et.net...

>>Try this...Starting with the report Closed, From your Procedure, Call
ControlFound ("YourReportName", "YourControlName")
the Function will find the report, open it in design mode, check for the
control and if the control is found, returns ControlFound = True
Then you can act on the information, Don't forget to Close the Report
and
move on to the next report.

Good luck
Len Robichaud

------------------------------------------------------------
Option Compare Database
Option Explicit

'Returns True if the specified report is open in Design View and The
Control is found.
'Any Error returns a False

Function ControlFound(ByVal strReportName As String, ByVal
strControlName
As String) As Boolean
On Error GoTo ControlFound_Error

Const conObjStateClosed = 0
Dim strControl As Variant
ControlFound = False

'If the Report Exists and it is currently closed, Open it in Design mode
If SysCmd(acSysCmdGetObjectState, acReport, strReportName) =
conObjStateClosed Then
DoCmd.OpenReport strReportName, acViewDesign
'Now check to see if the control exists on the report
strControl = Reports(strReportName).Controls(strControlName).Na me
If Len(strControl) 0 Then ControlFound = True
End If

ControlFound_Exit:
Exit Function

ControlFound_Error:
ControlFound = False
Resume ControlFound_Exit

End Function
---------------------------------------------------------------

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:13*************@corp.supernews.com...
I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address.
The same query is used as the RecordSource of lots of similar reports,
but
all with different sized Address text boxes. For the function to work,
the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about 1000 times). How can I neatly abort running the query if either
no
Reports are open, or a Report is open, but the particular Text Box is
not
on the report (i.e. the wrong Report is open)

TIA

Phil