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
6  6810 
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
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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Suzi Carr |
last post by:
Hello,
We create querydefs in VB programs (i.e. CreateQueryDef). But as illustrated
below, Access regenerates the SQL code we specify -- particularly the WHERE
clause. While the new code is...
|
by: strangedub |
last post by:
I am working on a C# Windows program that needs to kill a known
process. I have written code that works in development mode but hits
an "Access Denied" exception when running the compiled program:...
|
by: prakashsha |
last post by:
HI,
I've an asp.net page which has a button. On click of the button the program
queries the database and gets the result and displays the same.Now assume
that my program takes 2 minutes to get the...
|
by: James Radke |
last post by:
Hello,
I am attempting to use the proper Try/Catch technique when accessing my
Microsoft SQL server database and have a question...
If I use something similar to the following:
Try
set up...
|
by: kevcar40 |
last post by:
hi
is it possible to stop a form opening if the result of the query is
null?
eg
select a company name from a combobox if the name is not in the table
(ie the query returns null) can i stop...
|
by: diego |
last post by:
Greetings everyone!
Is there a way to stop query analyzer from processing remaining query
statements? Let's say i have the following query in query analyzer:
query statements
if condition...
|
by: bharadwaj |
last post by:
Hi All,
I am executing one query in sql analyzer. It was taking too much of time.I am not able to stop the query using stop button,it was not responding.
Many users are working with that database...
|
by: kcdoell |
last post by:
Hello:
What is the best way to stop a report from running if the query is empty? Currently, I have a form that has a command button on it. The user has to make selections from 3 combo boxes on...
|
by: Guillermo Antonio Amaral Bastidas |
last post by:
Hi everybody,
I have a quick and probably dumb question, keep in mind I just
dumped my old love FastCGI + Perl for it's younger hotter friend PHP5.
If the user calls a time consuming script...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |
