Printing Labels with Sequence #'s

[Bombay]

I am printing a series of Labels, and at the top right of each label will be
a Alpha Numeric Code printed. It is associated with the box it must be put
on.

So you are Prompted for "Number" to start at = X value
How Many Tickets = N Value
Enter Alpha Sequence: A,B,C,D,E,F Max of 6 Letters, can be less.

The Actual Printing does this.

Print Labels with Piece # = XA, X+1A, X+2A ... N times, then XB, X+1B, ... N
Times, until all A,B,C,D,E,F have been printed.

SO if someone enters in.
X=4
N=3
Sequence =A,C

Prints 4A, 5A, 6A and 4C, 5C, 6C.

Can anyone help with getting this to print from Access based on the info
above?

 

[Fred]

To emphasize a key point, Access is a database program, and it's report
function is to print out data from tables. So, you will need to get that
stuff that you want into tables.

 

[Bombay]

Yes, each of those feilds I will leave empty that will be prompted when
creating the label.

Still need to know how to get it to print labels, the amount of them with
the sequence printing out on each.

 

[John W. Vinson]

I am printing a series of Labels, and at the top right of each label will be
a Alpha Numeric Code printed. It is associated with the box it must be put
on.

So you are Prompted for "Number" to start at = X value
How Many Tickets = N Value
Enter Alpha Sequence: A,B,C,D,E,F Max of 6 Letters, can be less.

The Actual Printing does this.

Print Labels with Piece # = XA, X+1A, X+2A ... N times, then XB, X+1B, ... N
Times, until all A,B,C,D,E,F have been printed.

SO if someone enters in.
X=4
N=3
Sequence =A,C

Prints 4A, 5A, 6A and 4C, 5C, 6C.

Can anyone help with getting this to print from Access based on the info
above?

One way you could do this is to use two auxiliary tables, Num (with one field
N, containing numbers from 0 to 10000 or so) and Alpha (with one text field
AlphaSequence, with values A, B, C, D, E, F. Use a Form with three textboxes:
txtStartNum with your X value, TxtHowMany with your N, and txtLastLet with
value (say) E if you want to print A, B, C, D and E.

Base your report on a Query. Create the query by adding the Num and Alpha
tables to the query grid with no join line. As a criterion on N put

<= Forms!yourformname!txtStart

and as a criterion on AlphaSequence put

<= Forms!yourformname!txtLastLet

Use a calculated field in the query

[Num].[N] + [Forms]![yourformname]![txtStart] & [Aplha].[AlphaSequence]

 

[Bombay]

Not quite sure if that will work.

As there is basically a Do Loop. Do until N = Number.

My biggest problem is.

How do I get the sequence into a temp table?

 

[John W. Vinson]

Not quite sure if that will work.

It will. Try it.

As there is basically a Do Loop. Do until N = Number.

No looping is necessary. Use a "Cartesian Join" between the tables and a
criterion on N.

My biggest problem is.

How do I get the sequence into a temp table?

No temp table is necessary. You clearly did not try my suggestion, but it
*will* work.

 

[Bombay]

no join line...

I assume you mean there is no relation setup between the two tables when you
say No Join Line?

 

[Bombay]

Use a Form with three textboxes:
txtStartNum with your X value, TxtHowMany with your N, and txtLastLet with
value (say) E if you want to print A, B, C, D and E.
.....

When I try to create a form using both N and Alpha table based on what you
wrote above, it wants some kind of relationship for the tables?

 

[John W. Vinson]

Use a Form with three textboxes:
txtStartNum with your X value, TxtHowMany with your N, and txtLastLet with
value (say) E if you want to print A, B, C, D and E.
....

When I try to create a form using both N and Alpha table based on what you
wrote above, it wants some kind of relationship for the tables?

Well, don't give it one. It may pout and whine but it will obey.

Create your table Num. Don't relate it to anything.
Create your table Alpha. Don't relate it to anything.
Create a new Query; add N and Alpha to the query grid. Don't join anything to
anything.

Open the query.

You will see every possible combination of every record in N with every record
in Alpha (without using any do loops or any code).

Now create a new form. Don't base it on ANY table at all - it's an unbound
form, with no recordsource. This form should have the textboxes txtStartNum,
txtHowMany and txtLastLet. Add the form references to the query as suggested
in my previous post.

Base your Label report on this query, and you'll have what you requested.

 

[Bombay]

I was able to create the query with the table alpha and table num.

Working on the rest right now.

Form named "GetSequence" has values
-txtStartNum
-txtHowMany
-txtLastLet

using what you wrote

<= Forms!GetSequence!txtStartNum

and as a criterion on AlphaSequence put

<= Forms!GetSequence!txtLastLet

Use a calculated field in the query

[Num].[Num] + [Forms]![GetSequence]![txtStartNum] & [Aplha].[Alpha]

Ihave changed values N=Num and AlphaSequence=Alpha.

Is that correct?

Tad bit confused on where the <= Forms!GetSequence!txtLastLet, and how the
calculated field in the query are going to work.

 

[Bombay]

Okay here is where I am at.

Query
Field Alpha, table Alpha (Criterion <=[Forms]![GetSequence]![txtLastLet])
Field Num, table Num (Criterion <=[Forms]![GetSequence]![txtStartNum])

and finally
Expr1: [Num].[Num]+[Forms]![GetSequence]![txtStartNum] & [Alpha].[Alpha]

When I run the Query it askes me for
1. Forms!GetSequence!txtStartNum and I enter 1
2. Forms!GetSequence!txtLastLet and I enter F

I should have a How many option? I appear to be missing that.

It shows me 2A, 2B, 2C, 2D, 2E, 2F.

It should have done 1A, 1B, 1C, 1D, 1E, 1F. What is wrong here?

 

[John W. Vinson]

Okay here is where I am at.

Query
Field Alpha, table Alpha (Criterion <=[Forms]![GetSequence]![txtLastLet])
Field Num, table Num (Criterion <=[Forms]![GetSequence]![txtStartNum])

and finally
Expr1: [Num].[Num]+[Forms]![GetSequence]![txtStartNum] & [Alpha].[Alpha]

When I run the Query it askes me for
1. Forms!GetSequence!txtStartNum and I enter 1
2. Forms!GetSequence!txtLastLet and I enter F

That suggests that either you don't actually have a form named GetSequence
with controls of those names, or that the form is not open.

I should have a How many option? I appear to be missing that.

You need a third textbox on the form GetSequence, and add it to the criteria:

on field N use a criterion

=[Forms]![GetSequence]![txtStartNum] AND < [Forms]![GetSequence]![txtStartNum]+[Forms]![GetSequence]![txtHowMany]

It shows me 2A, 2B, 2C, 2D, 2E, 2F.

It should have done 1A, 1B, 1C, 1D, 1E, 1F. What is wrong here?

You probably don't have a row in Num with a value 0 for N.

 

[Bombay]

=[Forms]![GetSequence]![txtStartNum] AND <

[Forms]![GetSequence]![txtStartNum]+[Forms]![GetSequence]![txtHowMany]

Almost There.

Something is still wrong with this expression, and I cant figure it out.

If I enter the following Values
1= StartNum
F= LastLet
4=How Many

I should get 1A,1B,1C,1D,1E,1F... 4A,4B,4C,4D,4E,4F
instead I get

2A,2B,2C,2D,2E,2F ... 10A,10B,10C,10E,10D,10F(Happen to be the last values
in my spreedsheet.)

Any idea whats wrong with that formula?

 

[John W. Vinson]

=[Forms]![GetSequence]![txtStartNum] AND <

[Forms]![GetSequence]![txtStartNum]+[Forms]![GetSequence]![txtHowMany]

Almost There.

Something is still wrong with this expression, and I cant figure it out.

If I enter the following Values
1= StartNum
F= LastLet
4=How Many

I should get 1A,1B,1C,1D,1E,1F... 4A,4B,4C,4D,4E,4F
instead I get

2A,2B,2C,2D,2E,2F ... 10A,10B,10C,10E,10D,10F(Happen to be the last values
in my spreedsheet.)

Any idea whats wrong with that formula?

Please post the entire SQL of your query. I'm not seeing it from this.

 

[Bombay]

SELECT Alpha.Alpha, Num.Num, [Num].[Num] & [Alpha].[Alpha] AS Expr1
FROM Alpha, Num
WHERE (((Alpha.Alpha)<=[Forms]![GetSequence]![txtLastLet]) AND
((Num.Num)>=[Forms]![GetSequence]![txtStartNum] And
(Num.Num)<=([Forms]![GetSequence]![txtHowMany]+[Forms]![GetSequence]![txtStartNum])));


on another note, ive realized something.

They can enter the Alpha Sequence, but it needs to be entered in these like
combinations.

It is not A through F.

It can be ABCF
or ADEF
or BF

Thanks for your help so far, its been great.

 

[John W. Vinson]

SELECT Alpha.Alpha, Num.Num, [Num].[Num] & [Alpha].[Alpha] AS Expr1
FROM Alpha, Num
WHERE (((Alpha.Alpha)<=[Forms]![GetSequence]![txtLastLet]) AND
((Num.Num)>=[Forms]![GetSequence]![txtStartNum] And
(Num.Num)<=([Forms]![GetSequence]![txtHowMany]+[Forms]![GetSequence]![txtStartNum])));

And table Num has values 0 through 10 (or what)? Not sure why it's not
working, unless the + is being treated as a concatenation. Try

on another note, ive realized something.

They can enter the Alpha Sequence, but it needs to be entered in these like
combinations.

It is not A through F.

It can be ABCF
or ADEF
or BF

Thanks for your help so far, its been great.

Try using a textbox named txtUseLetters (with "ADEF" as contents for instance)
and a query

Parameters [Forms]![GetSequence]![txtUseLetters] Text,
[Forms]![GetSequence]![txtStartNum] Integer,
[Forms]![GetSequence]![txtHowMany] Integer;
SELECT Alpha.Alpha, Num.Num, [Num].[Num] & [Alpha].[Alpha] AS Expr1
FROM Alpha, Num
WHERE [Forms]![GetSequence]![txtUseLetters] LIKE "*" & Alpha.Alpha & "*"
AND
Num.Num>=[Forms]![GetSequence]![txtStartNum]
AND
Num.Num<=[Forms]![GetSequence]![txtStartNum]+[Forms]![GetSequence]![txtHowMany])));

 

[Bombay]

I have passed all those Hurdles, I am at one problem now.

Viewing form "GetSequence".

I cannot enter in values into my fields, I get. Control Can't Be Edited;
it's bound to an uknown field 'txtstartnum' etc...

How to I make this Form "Get Sequence" editable? I obviously do not want to
store the values into a table, but If I am able to enter in the values and
then have a button that runs the label using the query right on the form
while it is open, I am golden.

 

[Bombay]

Also...

Now that I have the Sequence number that needs to print on the Labels.

How would I go about adding that sequence number to the label I already have
created. That Label uses a seperate Query to populate the data needed.

Other values that it is grabbing is OrderID, PurchaseOrderNumber, Finish
Style from the query "Invoice".

 

[John W. Vinson]

I have passed all those Hurdles, I am at one problem now.

Viewing form "GetSequence".

I cannot enter in values into my fields, I get. Control Can't Be Edited;
it's bound to an uknown field 'txtstartnum' etc...

GetSequence should have nothing in its Recordsource property, and these three
textboxes should have nothing in their Control Source properties - that is, an
unbound form with unbound controls.

 

[John W. Vinson]

Also...

Now that I have the Sequence number that needs to print on the Labels.

How would I go about adding that sequence number to the label I already have
created. That Label uses a seperate Query to populate the data needed.

Other values that it is grabbing is OrderID, PurchaseOrderNumber, Finish
Style from the query "Invoice".

Just add the Num and AlphaSequence tables to your query, with no JOIN line.