Print qty from a form

[George Sydney]

I am creating a label program where i can select and product from a table
and store the selected item to another table so i can print the selected item.
I was wondering if I could enter a value and press the print button so the
program will automatically print the qty entered in the qty field.

Thanks

 

[Al Campagna]

George,
Please describe a bit more...
Are you printing one label, a certain number of times?
It sounds as though you want to print unique labels, but with a common
Qty?
Please show us some sample data, and what you would like to see on your
labels.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Ken Sheridan]

George:

You shouldn't need to insert the record into another table. Why not simply
base the label report on the original table or query and open the report
filtered to the selected record? Lets assume the key field which identifies
the record uniquely is called ItemID and this is a number. I'm guessing you
are selecting the item from the combo box you refer to on your other recent
thread, so to print the label report for just the selected record the code
for the Click event procedure of a button on the form would be:

Dim strCriteria As String, strReport As String

strCriteria = "[ItemID] = " & me.CboItems
strReport = "YourReportNameGoesHere"

DoCmd.OpenReport strReport, WhereCondition:=strCriteria

To preview it use:

DoCmd.OpenReport strReport, View:=acViewPreview, WhereCondition:=strCriteria

To insert the quantity in the report add an unbound text box, txtQuantity
say, to the form and in the report add an unbound text box whose
ControlSource property references the control on the form, e.g.

=Forms![YourFormName]![txtQuantity]

The form must be open when the report prints so if you want it to close
automatically put the code to close it in the report's Close event procedure:

DoCmd.Close acForm,"YourFormName"

The above assumes of course that you are not saving the quantity in a field
in a record (as in the Order Details table in the sample Northwind database),
but simply want to insert it manually each time into the label.

Ken Sheridan
Stafford, England

 

[George Sydney]

Dear Ken

Thank you for your assistance in other matters.

BUT

I am still having problems with this program.
I have removed the second table that was storing the item code.
I have entered the information in the print button and the preview button
sub routines.
It is not working. The program come up with the following error.
Syntax error (missing operator) in query expression '([ItemCode] = Caramel
Banana)'
I dont know what i am doing wrong.

My printing qty issue was not well explained. I need to be able to enter an
amount on the form and when i press the print button the program will print
the request number of the selected label.
Eg Selected Item = Caramel Banana, Print Qty = 20, press the print button
and the program prints 20 caramel banana labels.

Look forward to your assistance.




--
George Sydney
Frustrated programmer

George:

You shouldn't need to insert the record into another table. Why not simply
base the label report on the original table or query and open the report
filtered to the selected record? Lets assume the key field which identifies
the record uniquely is called ItemID and this is a number. I'm guessing you
are selecting the item from the combo box you refer to on your other recent
thread, so to print the label report for just the selected record the code
for the Click event procedure of a button on the form would be:

Dim strCriteria As String, strReport As String

strCriteria = "[ItemID] = " & me.CboItems
strReport = "YourReportNameGoesHere"

DoCmd.OpenReport strReport, WhereCondition:=strCriteria

To preview it use:

DoCmd.OpenReport strReport, View:=acViewPreview, WhereCondition:=strCriteria

To insert the quantity in the report add an unbound text box, txtQuantity
say, to the form and in the report add an unbound text box whose
ControlSource property references the control on the form, e.g.

=Forms![YourFormName]![txtQuantity]

The form must be open when the report prints so if you want it to close
automatically put the code to close it in the report's Close event procedure:

DoCmd.Close acForm,"YourFormName"

The above assumes of course that you are not saving the quantity in a field
in a record (as in the Order Details table in the sample Northwind database),
but simply want to insert it manually each time into the label.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

George:

By the look of it the first problem is due to the data type of ItemCode
being text rather than a number, so the value needs to be wrapped in quotes
characters:

strCriteria = "[ItemID] = """ & me.CboItems & """"

To print a selected number of labels first create a table PrintQuantities
with a single column of number data type PrintQuantity. Make this the
primary key. Then enter sequential numbers into the table from 1 to whatever
maximum number of labels you might ever want to print (you can always add
more later if necessary). Tip: for very long sequences you can serially
fill down a column in Excel and then import the worksheet into Access as a
table to save time manually entering the sequence.

In the label report's underlying query add the PrintQuantities table but
don't join it to any other table. Add the PrintQuntity field to a new column
in the design grid in query design view, uncheck the 'show' check box, and in
the 'criteria' row put:

<= Forms![YourFormName]![txtQuantity]

The way this works is that when a table is included in a query without being
joined the query returns what is known as the Cartesian product of the
tables, which means that every row of one table is joined to every row of the
other. Consequently, if you've entered 20 in the form the table(s) with your
label data will be joined to the first 20 rows of the PrintQuantities table
and 20 identical rows will be returned, printing 20 labels.

Ken Sheridan
Stafford, England

Dear Ken

Thank you for your assistance in other matters.

BUT

I am still having problems with this program.
I have removed the second table that was storing the item code.
I have entered the information in the print button and the preview button
sub routines.
It is not working. The program come up with the following error.
Syntax error (missing operator) in query expression '([ItemCode] = Caramel
Banana)'
I dont know what i am doing wrong.

My printing qty issue was not well explained. I need to be able to enter an
amount on the form and when i press the print button the program will print
the request number of the selected label.
Eg Selected Item = Caramel Banana, Print Qty = 20, press the print button
and the program prints 20 caramel banana labels.

Look forward to your assistance.




--
George Sydney
Frustrated programmer

George:

You shouldn't need to insert the record into another table. Why not simply
base the label report on the original table or query and open the report
filtered to the selected record? Lets assume the key field which identifies
the record uniquely is called ItemID and this is a number. I'm guessing you
are selecting the item from the combo box you refer to on your other recent
thread, so to print the label report for just the selected record the code
for the Click event procedure of a button on the form would be:

Dim strCriteria As String, strReport As String

strCriteria = "[ItemID] = " & me.CboItems
strReport = "YourReportNameGoesHere"

DoCmd.OpenReport strReport, WhereCondition:=strCriteria

To preview it use:

DoCmd.OpenReport strReport, View:=acViewPreview, WhereCondition:=strCriteria

To insert the quantity in the report add an unbound text box, txtQuantity
say, to the form and in the report add an unbound text box whose
ControlSource property references the control on the form, e.g.

=Forms![YourFormName]![txtQuantity]

The form must be open when the report prints so if you want it to close
automatically put the code to close it in the report's Close event procedure:

DoCmd.Close acForm,"YourFormName"

The above assumes of course that you are not saving the quantity in a field
in a record (as in the Order Details table in the sample Northwind database),
but simply want to insert it manually each time into the label.

Ken Sheridan
Stafford, England

I am creating a label program where i can select and product from a table
and store the selected item to another table so i can print the selected item.
I was wondering if I could enter a value and press the print button so the
program will automatically print the qty entered in the qty field.

Thanks