No RecordSource Reports

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
B

Bill Stanton

I have an application wherein I need to
generate report labels that do not have
a RecordSource. Rather, via a prompt,
the user defines a range of sequence
numbers and a general module generates
the sequence with a simple function.

Example: CA-0100 to CA-0250 results
in the generation of 151 labels,
CA-0100; CA-0101;...... CA-0250

I can easily create a textbox in the report
that contains the function invocation to fill
the textbox, but I don't know how to
"signal" what would be the equivalent
of a "RecordSource EOF".

What's the appropriate technique here?
Does the report's RecordSource have to
be some sort of function that mimics
table or query records.

Thanks,
Bill
 
You are much better off having some sort of record source, possibly a table
with as many records as you might need to print. For instance a table of
numbers from 1 to 500. You can then print up to 500 values beginning and
ending at a number/parameter entered by the user.
 
Bill said:
I have an application wherein I need to
generate report labels that do not have
a RecordSource. Rather, via a prompt,
the user defines a range of sequence
numbers and a general module generates
the sequence with a simple function.

Example: CA-0100 to CA-0250 results
in the generation of 151 labels,
CA-0100; CA-0101;...... CA-0250

I can easily create a textbox in the report
that contains the function invocation to fill
the textbox, but I don't know how to
"signal" what would be the equivalent
of a "RecordSource EOF".

What's the appropriate technique here?
Does the report's RecordSource have to
be some sort of function that mimics
table or query records.
Click to expand...


I strongly recommend Duane's approach, but to answer your
specific question, the logic would be along these lines:

Sub Detail_Format(
textbox = "CA-" & Format(intCurNum, "0000")
intCurNum = intCurNum + 1
If intCurNum < intLastNum Then
Me.NextRecord = False
End If
End Sub
 
Duane,

That's what I started to do when the
question arose as to how would one
accomplish the task without an under-
lying table or query. I will heed your
recommendation and retreat to what
my thought was to begin with... I'll
need to "run up the table to a few
thousand" even though only a hundred
or so would ever be specified at any
given time.

Thanks to you and Marsh.

Bill
 
Actually, you can create a table [T] with a single field [Num] and records
with values 0,1,2,3...9. Then, to get 100,000 records, create a query with
this SQL:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000+[T_4]![Num]*10000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3, T AS T_4;
 
Duane,
Good idea! 5,000 would have been enough.
(I'm not yet sufficiently proficient in the use
of SQL and user defined elements to have
come up with this one.)

I take it that the idea is to then filter the query
for Nums >= x1 AND Nums <= x2 where
x1 and x2 is the range specified by the user?

Bill

Duane Hookom said:
Actually, you can create a table [T] with a single field [Num] and records
with values 0,1,2,3...9. Then, to get 100,000 records, create a query with
this SQL:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000+[T_4]![Num]*10000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3, T AS T_4;


--
Duane Hookom
MS Access MVP


Bill Stanton said:
Duane,

That's what I started to do when the
question arose as to how would one
accomplish the task without an under-
lying table or query. I will heed your
recommendation and retreat to what
my thought was to begin with... I'll
need to "run up the table to a few
thousand" even though only a hundred
or so would ever be specified at any
given time.

Thanks to you and Marsh.

Bill
Click to expand...
Click to expand...
 
Marsh,
I ran your segment of code to familiarize myself
with the use of "Me.NextRecord = False". However,
I will follow yours and Duane's suggestion to create
an underlying query (see Duane's SQL help in that).

What is the reasoning behind the suggestion in
opposition to code such as what you offered as
an example? (Duane?)

Bill
 
Filter for the numbers as required. I would save the query as qselNums and
then use it in the report's record source.

--
Duane Hookom
MS Access MVP


Bill Stanton said:
Duane,
Good idea! 5,000 would have been enough.
(I'm not yet sufficiently proficient in the use
of SQL and user defined elements to have
come up with this one.)

I take it that the idea is to then filter the query
for Nums >= x1 AND Nums <= x2 where
x1 and x2 is the range specified by the user?

Bill

Duane Hookom said:
Actually, you can create a table [T] with a single field [Num] and records
with values 0,1,2,3...9. Then, to get 100,000 records, create a query with
this SQL:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000+[T_4]![Num]*10000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3, T AS T_4;


--
Duane Hookom
MS Access MVP


Bill Stanton said:
Duane,

That's what I started to do when the
question arose as to how would one
accomplish the task without an under-
lying table or query. I will heed your
recommendation and retreat to what
my thought was to begin with... I'll
need to "run up the table to a few
thousand" even though only a hundred
or so would ever be specified at any
given time.

Thanks to you and Marsh.

Bill
You are much better off having some sort of record source, possibly a
table
with as many records as you might need to print. For instance a
Click to expand...
Click to expand...
table
of
Click to expand...
Click to expand...
 
Will do, and thanks.

Last question: It's not clear how to reduce
the qselNums query to only produce 5 or
10 thousand records. I noticed that it takes
a few moments to generate 100K and there's
no sense in that for this application.

Bill



Duane Hookom said:
Filter for the numbers as required. I would save the query as qselNums and
then use it in the report's record source.

--
Duane Hookom
MS Access MVP


Bill Stanton said:
Duane,
Good idea! 5,000 would have been enough.
(I'm not yet sufficiently proficient in the use
of SQL and user defined elements to have
come up with this one.)

I take it that the idea is to then filter the query
for Nums >= x1 AND Nums <= x2 where
x1 and x2 is the range specified by the user?

Bill

Duane Hookom said:
Actually, you can create a table [T] with a single field [Num] and records
with values 0,1,2,3...9. Then, to get 100,000 records, create a query with
this SQL:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000+[T_4]![Num]*10000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3, T AS T_4;


--
Duane Hookom
MS Access MVP


Duane,

That's what I started to do when the
question arose as to how would one
accomplish the task without an under-
lying table or query. I will heed your
recommendation and retreat to what
my thought was to begin with... I'll
need to "run up the table to a few
thousand" even though only a hundred
or so would ever be specified at any
given time.

Thanks to you and Marsh.

Bill
You are much better off having some sort of record source,
Click to expand...
Click to expand...
possibly
a table beginning
and
Click to expand...
Click to expand...
 
Modify the sql removing reference to T_4:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3;

If you want, you can make a table from this SQL.

--
Duane Hookom
Microsoft Access MVP


Bill Stanton said:
Will do, and thanks.

Last question: It's not clear how to reduce
the qselNums query to only produce 5 or
10 thousand records. I noticed that it takes
a few moments to generate 100K and there's
no sense in that for this application.

Bill



Duane Hookom said:
Filter for the numbers as required. I would save the query as qselNums and
then use it in the report's record source.

--
Duane Hookom
MS Access MVP


Bill Stanton said:
Duane,
Good idea! 5,000 would have been enough.
(I'm not yet sufficiently proficient in the use
of SQL and user defined elements to have
come up with this one.)

I take it that the idea is to then filter the query
for Nums >= x1 AND Nums <= x2 where
x1 and x2 is the range specified by the user?

Bill

Actually, you can create a table [T] with a single field [Num] and records
with values 0,1,2,3...9. Then, to get 100,000 records, create a query
with
this SQL:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000+[T_4]![Num]*10000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3, T AS T_4;


--
Duane Hookom
MS Access MVP


Duane,

That's what I started to do when the
question arose as to how would one
accomplish the task without an under-
lying table or query. I will heed your
recommendation and retreat to what
my thought was to begin with... I'll
need to "run up the table to a few
thousand" even though only a hundred
or so would ever be specified at any
given time.

Thanks to you and Marsh.

Bill
You are much better off having some sort of record source,
Click to expand...
possibly
a
table
with as many records as you might need to print. For instance a table
of
numbers from 1 to 500. You can then print up to 500 values beginning
and
ending at a number/parameter entered by the user.

--
Duane Hookom
MS Access MVP


I have an application wherein I need to
generate report labels that do not have
a RecordSource. Rather, via a prompt,
the user defines a range of sequence
numbers and a general module generates
the sequence with a simple function.

Example: CA-0100 to CA-0250 results
in the generation of 151 labels,
CA-0100; CA-0101;...... CA-0250

I can easily create a textbox in the report
that contains the function invocation to fill
the textbox, but I don't know how to
"signal" what would be the equivalent
of a "RecordSource EOF".

What's the appropriate technique here?
Does the report's RecordSource have to
be some sort of function that mimics
table or query records.

Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Got it and many thanks,
Bill


Duane Hookom said:
Modify the sql removing reference to T_4:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3;

If you want, you can make a table from this SQL.

--
Duane Hookom
Microsoft Access MVP


Bill Stanton said:
Will do, and thanks.

Last question: It's not clear how to reduce
the qselNums query to only produce 5 or
10 thousand records. I noticed that it takes
a few moments to generate 100K and there's
no sense in that for this application.

Bill



Duane Hookom said:
Filter for the numbers as required. I would save the query as qselNums and
then use it in the report's record source.

--
Duane Hookom
MS Access MVP


Duane,
Good idea! 5,000 would have been enough.
(I'm not yet sufficiently proficient in the use
of SQL and user defined elements to have
come up with this one.)

I take it that the idea is to then filter the query
for Nums >= x1 AND Nums <= x2 where
x1 and x2 is the range specified by the user?

Bill

Actually, you can create a table [T] with a single field [Num] and
records
with values 0,1,2,3...9. Then, to get 100,000 records, create a query
with
this SQL:

SELECT [T]![Num]+[T_1]![Num]*10+[T_2]![Num]*100+
[T_3]![Num]*1000+[T_4]![Num]*10000 AS Nums
FROM T, T AS T_1, T AS T_2, T AS T_3, T AS T_4;


--
Duane Hookom
MS Access MVP


Duane,

That's what I started to do when the
question arose as to how would one
accomplish the task without an under-
lying table or query. I will heed your
recommendation and retreat to what
my thought was to begin with... I'll
need to "run up the table to a few
thousand" even though only a hundred
or so would ever be specified at any
given time.

Thanks to you and Marsh.

Bill
You are much better off having some sort of record source, possibly
a
table
with as many records as you might need to print. For instance a
table
of
numbers from 1 to 500. You can then print up to 500 values beginning
and
ending at a number/parameter entered by the user.

--
Duane Hookom
MS Access MVP


I have an application wherein I need to
generate report labels that do not have
a RecordSource. Rather, via a prompt,
the user defines a range of sequence
numbers and a general module generates
the sequence with a simple function.

Example: CA-0100 to CA-0250 results
in the generation of 151 labels,
CA-0100; CA-0101;...... CA-0250

I can easily create a textbox in the report
that contains the function invocation to fill
the textbox, but I don't know how to
"signal" what would be the equivalent
of a "RecordSource EOF".

What's the appropriate technique here?
Does the report's RecordSource have to
be some sort of function that mimics
table or query records.

Thanks,
Bill
Click to expand...
Click to expand...
Click to expand...
 
Bill said:
Marsh,
I ran your segment of code to familiarize myself
with the use of "Me.NextRecord = False". However,
I will follow yours and Duane's suggestion to create
an underlying query (see Duane's SQL help in that).

What is the reasoning behind the suggestion in
opposition to code such as what you offered as
an example? (Duane?)
Click to expand...

While that kind of code has always worked in the cases where
I thought it was really needed, I just don't trust an event
driven architecture, such as Access reports, to follow my
linear logic. A database system should be data driven, not
code driven.
--
Marsh
MVP [MS Access]


 
Marsh,
Your point is well taken.

Thanks,
Bill


Marshall Barton said:
Bill said:
Marsh,
I ran your segment of code to familiarize myself
with the use of "Me.NextRecord = False". However,
I will follow yours and Duane's suggestion to create
an underlying query (see Duane's SQL help in that).

What is the reasoning behind the suggestion in
opposition to code such as what you offered as
an example? (Duane?)
Click to expand...

While that kind of code has always worked in the cases where
I thought it was really needed, I just don't trust an event
driven architecture, such as Access reports, to follow my
linear logic. A database system should be data driven, not
code driven.
--
Marsh
MVP [MS Access]


Click to expand...
 
Back
Top