Iterating through a hidden textbox field

[Jacob Frankham]

Hi everyone

The scenario is this:

A team of inspectors will inspect colleges/universities for quality of
provision etc, and the results of these inspections will be recorded in a
Lotus Notes database.

An inspector will be unlikely to check ALL of the courses that a college
runs, so the specific courses that (s)he has checked will also be recorded
in the database.

I am importing this data into access - (thats the easy bit !)

Lotus Notes automatically gives each record a unique ID number (long mix of
numbers and text)

I am trying to create a report which shows for each college/university all
of the programmes inspected within that inspection. There will be many
instances of an inspection at each college/university, and are all likely to
have different programmes checked within them.

So, I have a query which lists (amongst other data)
docID (identifies each inspection)
name (of the college / university)
programme (lists the different programmes checked during that particular
inspection)
date etc

eg

a1ssq2 Sheffield College ..... ..... BSc 11.08.99
a1ssq2 Sheffield College ..... ..... LLB 11.08.99
a1ssq2 Sheffield College ..... ..... BA
11.08.99
.......... ............................ ..... .....
....... .............
d3lgt4 Edinburgh University ..... ..... BEng 04.05.02
d3lgt4 Edinburgh University ..... ..... MSc 04.05.02
d3lgt4 Edinburgh University ..... ..... BSc 04.05.02
d3lgt4 Edinburgh University ..... ..... LLB 04.05.02

OK so here is my question !!! -

For each college / university, I wish to display all of the programmes which
were inspected on that date.
I need the programmes to be listed ON ONE LINE, (separated by commas).

I currently have a textbox field and am using some VBA to iterate through a
recordset to display the programmes but this is taking too much time when
the report opens - I was thinking of using a hidden textbox field and
iterating through that instead, I guess that would be quicker at runtime?

A copy of the VBA I am currently using is below

Public Function FillControl()

txt_Provision = ""
Dim db As Database

Dim rstTemp As Recordset
Set db = CurrentDb
Set rstTemp = db.OpenRecordset("sqry_AuditedProvisions", dbOpenDynaset)
Dim F1 As Field
Dim F2 As Field
Dim F3 As Field
Set F1 = rstTemp("provision")
Set F2 = rstTemp("docID")

Dim FillString As String
With rstTemp
Do While Not rstTemp.EOF
If F2 = [txt_docID] Then
FillString = FillString & F1 & ", "
End If
.MoveNext
Loop
End With
FillControl = Left(FillString, IIf(Len(FillString) > 0, Len(FillString) - 3,
FillString))
rstTemp.Close
Set db = Nothing

End Function




But, as I said it is taking far too long at runtime using this method, so I
need an alternative

Hope this is somewhat clear?

Cheers

Jake

 

[Marshall Barton]

Jacob Frankham wrote:
[snip]

For each college / university, I wish to display all of the programmes which
were inspected on that date.
I need the programmes to be listed ON ONE LINE, (separated by commas).

I currently have a textbox field and am using some VBA to iterate through a
recordset to display the programmes but this is taking too much time when
the report opens - I was thinking of using a hidden textbox field and
iterating through that instead, I guess that would be quicker at runtime?

A copy of the VBA I am currently using is below

Public Function FillControl()

txt_Provision = ""
Dim db As Database

Dim rstTemp As Recordset
Set db = CurrentDb
Set rstTemp = db.OpenRecordset("sqry_AuditedProvisions", dbOpenDynaset)
Dim F1 As Field
Dim F2 As Field
Dim F3 As Field
Set F1 = rstTemp("provision")
Set F2 = rstTemp("docID")

Dim FillString As String
With rstTemp
Do While Not rstTemp.EOF
If F2 = [txt_docID] Then
FillString = FillString & F1 & ", "
End If
.MoveNext
Loop
End With
FillControl = Left(FillString, IIf(Len(FillString) > 0, Len(FillString) - 3,
FillString))
rstTemp.Close
Set db = Nothing

End Function

But, as I said it is taking far too long at runtime using this method, so I
need an alternative

You should not open the recordset on the entire dataset.
Instead, use a where clause to restrinct the data to only
the records that apply to the current record:

Dim strSQL As String
strSQL = "SELECT * FROM sqry_AuditedProvisions " _
& "WHERE docID = " & Me.txtdocID
Set rstTemp = db.OpenRecordset(strSQL, dbOpenDynaset)
Dim FillString As String
Do While Not rstTemp.EOF
FillString = ", " & FillString & rstTemp("provision")
.MoveNext
Loop
End With
FillControl = Mid(FillString, 3)
rstTemp.Close

I think at least part of the slowness lies in the
sqry_AuditedProvisions query, but since I don't know what
that does, I can't comment on it.

 

[Jacob Frankham]

Hi Marshall

Thanks for your help

O have tried your code but I keep getting a 3075 error message

"Sysntax error (missing operator) in query expression [docID] =
233DE4H9JJ5GY32etcetc"

Do you have any suggestions?

Cheers

Jake

Jacob Frankham wrote:
[snip]

For each college / university, I wish to display all of the programmes which
were inspected on that date.
I need the programmes to be listed ON ONE LINE, (separated by commas).

I currently have a textbox field and am using some VBA to iterate through a
recordset to display the programmes but this is taking too much time when
the report opens - I was thinking of using a hidden textbox field and
iterating through that instead, I guess that would be quicker at runtime?

A copy of the VBA I am currently using is below

Public Function FillControl()

txt_Provision = ""
Dim db As Database

Dim rstTemp As Recordset
Set db = CurrentDb
Set rstTemp = db.OpenRecordset("sqry_AuditedProvisions", dbOpenDynaset)
Dim F1 As Field
Dim F2 As Field
Dim F3 As Field
Set F1 = rstTemp("provision")
Set F2 = rstTemp("docID")

Dim FillString As String
With rstTemp
Do While Not rstTemp.EOF
If F2 = [txt_docID] Then
FillString = FillString & F1 & ", "
End If
.MoveNext
Loop
End With
FillControl = Left(FillString, IIf(Len(FillString) > 0, Len(FillString) - 3,
FillString))
rstTemp.Close
Set db = Nothing

End Function

But, as I said it is taking far too long at runtime using this method, so I
need an alternative

You should not open the recordset on the entire dataset.
Instead, use a where clause to restrinct the data to only
the records that apply to the current record:

Dim strSQL As String
strSQL = "SELECT * FROM sqry_AuditedProvisions " _
& "WHERE docID = " & Me.txtdocID
Set rstTemp = db.OpenRecordset(strSQL, dbOpenDynaset)
Dim FillString As String
Do While Not rstTemp.EOF
FillString = ", " & FillString & rstTemp("provision")
.MoveNext
Loop
End With
FillControl = Mid(FillString, 3)
rstTemp.Close

I think at least part of the slowness lies in the
sqry_AuditedProvisions query, but since I don't know what
that does, I can't comment on it.

 

[Jacob Frankham]

My apologies,

the error message I now get is 30613

"Too few parameters, expected 1"

and when I click on 'Debug' it highlights the following line:

Set rstTemp = db.OpenRecordset(strSQL, dbOpenDynaset)

My strSQL statement is as follows:

strSQL = "SELECT * FROM sqry_AuditedProvisions WHERE [docID] = " &
Me.txt_docID


Help !!

Jake

Hi Marshall

Thanks for your help

O have tried your code but I keep getting a 3075 error message

"Sysntax error (missing operator) in query expression [docID] =
233DE4H9JJ5GY32etcetc"

Do you have any suggestions?

Cheers

Jake

Jacob Frankham wrote:
[snip]

For each college / university, I wish to display all of the programmes which
were inspected on that date.
I need the programmes to be listed ON ONE LINE, (separated by commas).

I currently have a textbox field and am using some VBA to iterate

through

a

recordset to display the programmes but this is taking too much time when
the report opens - I was thinking of using a hidden textbox field and
iterating through that instead, I guess that would be quicker at runtime?

A copy of the VBA I am currently using is below

Public Function FillControl()

txt_Provision = ""
Dim db As Database

Dim rstTemp As Recordset
Set db = CurrentDb
Set rstTemp = db.OpenRecordset("sqry_AuditedProvisions", dbOpenDynaset)
Dim F1 As Field
Dim F2 As Field
Dim F3 As Field
Set F1 = rstTemp("provision")
Set F2 = rstTemp("docID")

Dim FillString As String
With rstTemp
Do While Not rstTemp.EOF
If F2 = [txt_docID] Then
FillString = FillString & F1 & ", "
End If
.MoveNext
Loop
End With
FillControl = Left(FillString, IIf(Len(FillString) > 0,

Len(FillString) -
3, so

 

[Marshall Barton]

My apologies,

the error message I now get is 30613

"Too few parameters, expected 1"

and when I click on 'Debug' it highlights the following line:

Set rstTemp = db.OpenRecordset(strSQL, dbOpenDynaset)

My strSQL statement is as follows:

strSQL = "SELECT * FROM sqry_AuditedProvisions WHERE [docID] = " &
Me.txt_docID

Sorry, my mistake. You did say the docID was a Text field,
but I gave you the syntax for a numeric type field. It
should be:

. . . WHERE [docID] = """ & Me.txt_docID & """"

as long as the docID field never includes a quote (")
character.

 

[Jacob Frankham]

Thanks Marshall

That solved the error

However !!!

It is still just as slow (my original problem?)

If I may continue to pull on your expertise that would be appreciated !!

Now, the query which is being supplied to the report runs instantly (despite
the number of records)
So too does sqry_AuditedProvisions (as soon as u click run it is virtually
on the screen in 1/2 a second)

sqry_AuditedProvisions simply shows [docID] and [provision] (this is the
recordset we opened in VB)

On my report I have a grouping header on [docID] with NOTHING in it (ie no
controls) - I don't really know why I have this here apart from the fact
that it doesn't work without it!

I then have a header below this called [collegeName], and it is within this
header that most of my controls are
eg [docID](which is hidden) [collegeName] etc etc
I also have my provision text box in this header (the one which is being
populated by my VB FillControl method)

Marshall, my database has no more than 100 records and so I must be doing
something wrong for it to take approx 14 seconds to open a report

Again, I appreciate your time.

Many thanks

Jake

My apologies,

the error message I now get is 30613

"Too few parameters, expected 1"

and when I click on 'Debug' it highlights the following line:

Set rstTemp = db.OpenRecordset(strSQL, dbOpenDynaset)

My strSQL statement is as follows:

strSQL = "SELECT * FROM sqry_AuditedProvisions WHERE [docID] = " &
Me.txt_docID

Sorry, my mistake. You did say the docID was a Text field,
but I gave you the syntax for a numeric type field. It
should be:

. . . WHERE [docID] = """ & Me.txt_docID & """"

as long as the docID field never includes a quote (")
character.

 

[Marshall Barton]

Thanks Marshall

That solved the error

However !!!

It is still just as slow (my original problem?)

If I may continue to pull on your expertise that would be appreciated !!

Now, the query which is being supplied to the report runs instantly (despite
the number of records)
So too does sqry_AuditedProvisions (as soon as u click run it is virtually
on the screen in 1/2 a second)

That's not always as clear as it may seem. Access will
display the query as soon as it has enough data to display
the first screen, the remainder of the query may take a
**lot** longer.

sqry_AuditedProvisions simply shows [docID] and [provision] (this is the
recordset we opened in VB)

On my report I have a grouping header on [docID] with NOTHING in it (ie no
controls) - I don't really know why I have this here apart from the fact
that it doesn't work without it!

To get all the records for a docID together??

I then have a header below this called [collegeName], and it is within this
header that most of my controls are
eg [docID](which is hidden) [collegeName] etc etc
I also have my provision text box in this header (the one which is being
populated by my VB FillControl method)

Without seeing the query, I can't be sure, but it sounds
like the query has a lot more data than it needs. What do
you have in the detail section?

Marshall, my database has no more than 100 records and so I must be doing
something wrong for it to take approx 14 seconds to open a report

100 records in the whole database??? Or in the report
record source query? Something smells funny here ...

I think you'll have to post back with the SQL of the queries
being used in this.

On another thing you might check for is another common cause
of report slowdown. Are you using a textbox with a Page of
Pages type of expression?
--
Marsh
MVP [MS Access]

My apologies,

the error message I now get is 30613

"Too few parameters, expected 1"

and when I click on 'Debug' it highlights the following line:

Set rstTemp = db.OpenRecordset(strSQL, dbOpenDynaset)

My strSQL statement is as follows:

strSQL = "SELECT * FROM sqry_AuditedProvisions WHERE [docID] = " &
Me.txt_docID

Sorry, my mistake. You did say the docID was a Text field,
but I gave you the syntax for a numeric type field. It
should be:

. . . WHERE [docID] = """ & Me.txt_docID & """"

as long as the docID field never includes a quote (")
character.

 

[Jacob Frankham]

Hi Marshall

*
That's not always as clear as it may seem. Access will
display the query as soon as it has enough data to display
the first screen, the remainder of the query may take a
**lot** longer.

Point taken, but it still seems to be quite quick

*
To get all the records for a docID together??

You can probably tell, I'm no expert ! But yes, if I remove the header, it
messes up all of the provisions which were applicable to each colleges
inspection, which is why I keep the header in the report (despite there
being no controls within it)

*
Without seeing the query, I can't be sure, but it sounds
like the query has a lot more data than it needs. What do
you have in the detail section?

Now thats an interesting question - I have NOTHING in my detail section -
most of my data is in the collegeName Header section (The reports query is
showing the latest inspection record for each college, so I put all of my
controls onto the same line as the collegeName so the data would not appear
'stepped') - could the absence of anything in the Detail section be slowing
it down?

*
100 records in the whole database??? Or in the report
record source query? Something smells funny here ...

Actually only 48 records in the reports record source query, about 580
records in entire database. Is the smell getting stronger ?!!??!

*
I think you'll have to post back with the SQL of the queries
being used in this.

The reports record source query is merely displaying all of the data which I
require for my report eg
[docID], [collegeName], [auditDate], [gradeAwarded], [fineImposed] etc.
Access has made a pigs ear of the SQL by adding all of the table names to
each and every field so it would probably scare you off from reading onwards
if I was to paste it here !
Suffice to say, that the query runs (in datasheet view) very fast and there
are only 48 records (not 100 as I mentioned earlier today).
The query which I am opening via VBA (sqry_AuditedProvisions) has just
[docID] and [provisions]. I am using the VBA method to link
[sqry_AuditedProvisions].[docID] to the txt_docID on the report (the control
source for txt_docID is the docID field of the reports record source query).

*
On another thing you might check for is another common cause
of report slowdown. Are you using a textbox with a Page of
Pages type of expression?

Funny you should mention that - yes I am - in the Page Footer section I have
a textbox whose control source is:
="Page " & [Page] & " of " & [Pages]

As always, your feedback is invaluable

Cheers

Jake

 

[Marshall Barton]

*
That's not always as clear as it may seem. Access will
display the query as soon as it has enough data to display
the first screen, the remainder of the query may take a
**lot** longer.

Point taken, but it still seems to be quite quick

*
To get all the records for a docID together??

You can probably tell, I'm no expert ! But yes, if I remove the header, it
messes up all of the provisions which were applicable to each colleges
inspection, which is why I keep the header in the report (despite there
being no controls within it)

If it's causing any grief, you can always set the header's
Visible property to No.

*
Without seeing the query, I can't be sure, but it sounds
like the query has a lot more data than it needs. What do
you have in the detail section?

Now thats an interesting question - I have NOTHING in my detail section -
most of my data is in the collegeName Header section (The reports query is
showing the latest inspection record for each college, so I put all of my
controls onto the same line as the collegeName so the data would not appear
'stepped') - could the absence of anything in the Detail section be slowing
it down?

That implies either that you don't need the collegeName
group and everything can be in the detail section or that
you should be using a Totals (or calculated) query that
would eliminate the need for the group. One way or another,
we want to get rid of this group.

Try dropping the group and let me know what happens.

*
100 records in the whole database??? Or in the report
record source query? Something smells funny here ...

Actually only 48 records in the reports record source query, about 580
records in entire database. Is the smell getting stronger ?!!??!

Sniff sniff ... yeah, I think it is ;-)

*
I think you'll have to post back with the SQL of the queries
being used in this.

The reports record source query is merely displaying all of the data which I
require for my report eg
[docID], [collegeName], [auditDate], [gradeAwarded], [fineImposed] etc.
Access has made a pigs ear of the SQL by adding all of the table names to
each and every field so it would probably scare you off from reading onwards
if I was to paste it here !

Pigs ear is being nice, many others refer to it as a dogs
breakfast ;-) Regardless, we're used to it so post it
anyway.

Better post sqry_AuditedProvisions too. The query being
used to constuct the list of provisions could also be the
cause of the slowness.

Suffice to say, that the query runs (in datasheet view) very fast and there
are only 48 records (not 100 as I mentioned earlier today).
The query which I am opening via VBA (sqry_AuditedProvisions) has just
[docID] and [provisions]. I am using the VBA method to link
[sqry_AuditedProvisions].[docID] to the txt_docID on the report (the control
source for txt_docID is the docID field of the reports record source query).

*
On another thing you might check for is another common cause
of report slowdown. Are you using a textbox with a Page of
Pages type of expression?

Funny you should mention that - yes I am - in the Page Footer section I have
a textbox whose control source is:
="Page " & [Page] & " of " & [Pages]

Does removing that significantly speed things up?

 

[Jacob Frankham]

Hi again Marshall

*
If it's causing any grief, you can always set the header's
Visible property to No.

Done (Didn't know u could do this) - incidentally, now that the header has
gone, how would I get it visible again if I wanted it?

*
Does removing that significantly speed things up?

Yes it does - thanks a lot for this - you've made my day

I will get back to you regarding eliminating the collegeName header and just
using the detail section

Thanks Again

Jake

*
That's not always as clear as it may seem. Access will
display the query as soon as it has enough data to display
the first screen, the remainder of the query may take a
**lot** longer.

Point taken, but it still seems to be quite quick

*
To get all the records for a docID together??

You can probably tell, I'm no expert ! But yes, if I remove the header, it
messes up all of the provisions which were applicable to each colleges
inspection, which is why I keep the header in the report (despite there
being no controls within it)

If it's causing any grief, you can always set the header's
Visible property to No.

*
Without seeing the query, I can't be sure, but it sounds
like the query has a lot more data than it needs. What do
you have in the detail section?

Now thats an interesting question - I have NOTHING in my detail section -
most of my data is in the collegeName Header section (The reports query is
showing the latest inspection record for each college, so I put all of my
controls onto the same line as the collegeName so the data would not appear
'stepped') - could the absence of anything in the Detail section be slowing
it down?

That implies either that you don't need the collegeName
group and everything can be in the detail section or that
you should be using a Totals (or calculated) query that
would eliminate the need for the group. One way or another,
we want to get rid of this group.

Try dropping the group and let me know what happens.

*
100 records in the whole database??? Or in the report
record source query? Something smells funny here ...

Actually only 48 records in the reports record source query, about 580
records in entire database. Is the smell getting stronger ?!!??!

Sniff sniff ... yeah, I think it is ;-)

*
I think you'll have to post back with the SQL of the queries
being used in this.

The reports record source query is merely displaying all of the data which I
require for my report eg
[docID], [collegeName], [auditDate], [gradeAwarded], [fineImposed] etc.
Access has made a pigs ear of the SQL by adding all of the table names to
each and every field so it would probably scare you off from reading onwards
if I was to paste it here !

Pigs ear is being nice, many others refer to it as a dogs
breakfast ;-) Regardless, we're used to it so post it
anyway.

Better post sqry_AuditedProvisions too. The query being
used to constuct the list of provisions could also be the
cause of the slowness.

Suffice to say, that the query runs (in datasheet view) very fast and there
are only 48 records (not 100 as I mentioned earlier today).
The query which I am opening via VBA (sqry_AuditedProvisions) has just
[docID] and [provisions]. I am using the VBA method to link
[sqry_AuditedProvisions].[docID] to the txt_docID on the report (the control
source for txt_docID is the docID field of the reports record source query).

*
On another thing you might check for is another common cause
of report slowdown. Are you using a textbox with a Page of
Pages type of expression?

Funny you should mention that - yes I am - in the Page Footer section I have
a textbox whose control source is:
="Page " & [Page] & " of " & [Pages]

Does removing that significantly speed things up?

 

[Jacob Frankham]

Hi Again

I got rid of the collegeName header but it did not speed up any

I think that the Page Of Pages textboxes was my main problem

However, if I run the query with ALL records of the db it takes about 30s to
run

I will post code shortly (is on laptop)

Cheers

Jake

*
That's not always as clear as it may seem. Access will
display the query as soon as it has enough data to display
the first screen, the remainder of the query may take a
**lot** longer.

Point taken, but it still seems to be quite quick

*
To get all the records for a docID together??

You can probably tell, I'm no expert ! But yes, if I remove the header, it
messes up all of the provisions which were applicable to each colleges
inspection, which is why I keep the header in the report (despite there
being no controls within it)

If it's causing any grief, you can always set the header's
Visible property to No.

*
Without seeing the query, I can't be sure, but it sounds
like the query has a lot more data than it needs. What do
you have in the detail section?

Now thats an interesting question - I have NOTHING in my detail section -
most of my data is in the collegeName Header section (The reports query is
showing the latest inspection record for each college, so I put all of my
controls onto the same line as the collegeName so the data would not appear
'stepped') - could the absence of anything in the Detail section be slowing
it down?

That implies either that you don't need the collegeName
group and everything can be in the detail section or that
you should be using a Totals (or calculated) query that
would eliminate the need for the group. One way or another,
we want to get rid of this group.

Try dropping the group and let me know what happens.

*
100 records in the whole database??? Or in the report
record source query? Something smells funny here ...

Actually only 48 records in the reports record source query, about 580
records in entire database. Is the smell getting stronger ?!!??!

Sniff sniff ... yeah, I think it is ;-)

*
I think you'll have to post back with the SQL of the queries
being used in this.

The reports record source query is merely displaying all of the data which I
require for my report eg
[docID], [collegeName], [auditDate], [gradeAwarded], [fineImposed] etc.
Access has made a pigs ear of the SQL by adding all of the table names to
each and every field so it would probably scare you off from reading onwards
if I was to paste it here !

Pigs ear is being nice, many others refer to it as a dogs
breakfast ;-) Regardless, we're used to it so post it
anyway.

Better post sqry_AuditedProvisions too. The query being
used to constuct the list of provisions could also be the
cause of the slowness.

Suffice to say, that the query runs (in datasheet view) very fast and there
are only 48 records (not 100 as I mentioned earlier today).
The query which I am opening via VBA (sqry_AuditedProvisions) has just
[docID] and [provisions]. I am using the VBA method to link
[sqry_AuditedProvisions].[docID] to the txt_docID on the report (the control
source for txt_docID is the docID field of the reports record source query).

*
On another thing you might check for is another common cause
of report slowdown. Are you using a textbox with a Page of
Pages type of expression?

Funny you should mention that - yes I am - in the Page Footer section I have
a textbox whose control source is:
="Page " & [Page] & " of " & [Pages]

Does removing that significantly speed things up?

 

[Jacob Frankham]

Marshall

My reports appear to be running quite quickly now, I believe that the main
culprit was the 'Page of Pages' bit

Thanks ever so much for your help !!!!

Keep up the excellent support

Jake !

*
That's not always as clear as it may seem. Access will
display the query as soon as it has enough data to display
the first screen, the remainder of the query may take a
**lot** longer.

Point taken, but it still seems to be quite quick

*
To get all the records for a docID together??

You can probably tell, I'm no expert ! But yes, if I remove the header, it
messes up all of the provisions which were applicable to each colleges
inspection, which is why I keep the header in the report (despite there
being no controls within it)

If it's causing any grief, you can always set the header's
Visible property to No.

*
Without seeing the query, I can't be sure, but it sounds
like the query has a lot more data than it needs. What do
you have in the detail section?

Now thats an interesting question - I have NOTHING in my detail section -
most of my data is in the collegeName Header section (The reports query is
showing the latest inspection record for each college, so I put all of my
controls onto the same line as the collegeName so the data would not appear
'stepped') - could the absence of anything in the Detail section be slowing
it down?

That implies either that you don't need the collegeName
group and everything can be in the detail section or that
you should be using a Totals (or calculated) query that
would eliminate the need for the group. One way or another,
we want to get rid of this group.

Try dropping the group and let me know what happens.

*
100 records in the whole database??? Or in the report
record source query? Something smells funny here ...

Actually only 48 records in the reports record source query, about 580
records in entire database. Is the smell getting stronger ?!!??!

Sniff sniff ... yeah, I think it is ;-)

*
I think you'll have to post back with the SQL of the queries
being used in this.

The reports record source query is merely displaying all of the data which I
require for my report eg
[docID], [collegeName], [auditDate], [gradeAwarded], [fineImposed] etc.
Access has made a pigs ear of the SQL by adding all of the table names to
each and every field so it would probably scare you off from reading onwards
if I was to paste it here !

Pigs ear is being nice, many others refer to it as a dogs
breakfast ;-) Regardless, we're used to it so post it
anyway.

Better post sqry_AuditedProvisions too. The query being
used to constuct the list of provisions could also be the
cause of the slowness.

Suffice to say, that the query runs (in datasheet view) very fast and there
are only 48 records (not 100 as I mentioned earlier today).
The query which I am opening via VBA (sqry_AuditedProvisions) has just
[docID] and [provisions]. I am using the VBA method to link
[sqry_AuditedProvisions].[docID] to the txt_docID on the report (the control
source for txt_docID is the docID field of the reports record source query).

*
On another thing you might check for is another common cause
of report slowdown. Are you using a textbox with a Page of
Pages type of expression?

Funny you should mention that - yes I am - in the Page Footer section I have
a textbox whose control source is:
="Page " & [Page] & " of " & [Pages]

Does removing that significantly speed things up?

 

[Marshall Barton]

Marshall

My reports appear to be running quite quickly now, I believe that the main
culprit was the 'Page of Pages' bit

I don't think it's the main problem, but it did make the
report do twice the work.

Thanks ever so much for your help !!!!

You're welcome.

Keep up the excellent support

Doing my best ;-)
--
Marsh
MVP [MS Access]

*
That's not always as clear as it may seem. Access will
display the query as soon as it has enough data to display
the first screen, the remainder of the query may take a
**lot** longer.

Point taken, but it still seems to be quite quick

*
To get all the records for a docID together??

You can probably tell, I'm no expert ! But yes, if I remove the header, it
messes up all of the provisions which were applicable to each colleges
inspection, which is why I keep the header in the report (despite there
being no controls within it)

If it's causing any grief, you can always set the header's
Visible property to No.

*
Without seeing the query, I can't be sure, but it sounds
like the query has a lot more data than it needs. What do
you have in the detail section?

Now thats an interesting question - I have NOTHING in my detail section -
most of my data is in the collegeName Header section (The reports query is
showing the latest inspection record for each college, so I put all of my
controls onto the same line as the collegeName so the data would not appear
'stepped') - could the absence of anything in the Detail section be slowing
it down?

That implies either that you don't need the collegeName
group and everything can be in the detail section or that
you should be using a Totals (or calculated) query that
would eliminate the need for the group. One way or another,
we want to get rid of this group.

Try dropping the group and let me know what happens.

*
100 records in the whole database??? Or in the report
record source query? Something smells funny here ...

Actually only 48 records in the reports record source query, about 580
records in entire database. Is the smell getting stronger ?!!??!

Sniff sniff ... yeah, I think it is ;-)

*
I think you'll have to post back with the SQL of the queries
being used in this.

The reports record source query is merely displaying all of the data which I
require for my report eg
[docID], [collegeName], [auditDate], [gradeAwarded], [fineImposed] etc.
Access has made a pigs ear of the SQL by adding all of the table names to
each and every field so it would probably scare you off from reading onwards
if I was to paste it here !

Pigs ear is being nice, many others refer to it as a dogs
breakfast ;-) Regardless, we're used to it so post it
anyway.

Better post sqry_AuditedProvisions too. The query being
used to constuct the list of provisions could also be the
cause of the slowness.

Suffice to say, that the query runs (in datasheet view) very fast and there
are only 48 records (not 100 as I mentioned earlier today).
The query which I am opening via VBA (sqry_AuditedProvisions) has just
[docID] and [provisions]. I am using the VBA method to link
[sqry_AuditedProvisions].[docID] to the txt_docID on the report (the control
source for txt_docID is the docID field of the reports record source query).

*
On another thing you might check for is another common cause
of report slowdown. Are you using a textbox with a Page of
Pages type of expression?

Funny you should mention that - yes I am - in the Page Footer section I have
a textbox whose control source is:
="Page " & [Page] & " of " & [Pages]

Does removing that significantly speed things up?