No RecordSource Reports

[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

 

[Duane Hookom]

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.

 

[Marshall Barton]

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.

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

 

[Bill Stanton]

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

 

[Duane Hookom]

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;

 

[Bill Stanton]

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

 

[Bill Stanton]

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

 

[Duane Hookom]

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

 

[Bill Stanton]

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

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 beginning
and

 

[Duane Hookom]

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

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

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

 

[Bill Stanton]

Got it and many thanks,
Bill

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

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

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

 

[Marshall Barton]

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?)

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]

 

[Bill Stanton]

Marsh,
Your point is well taken.

Thanks,
Bill

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?)

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]