Setting the numbers of copies to print in VB

Try this ...

Basically what I am trying to achieve is to look up the qty of labels to print in the Table Bar, which is the field zoo using the forms control "zoo_choose" in the "tester form" as the criteria for the record I want labels for.

The DLookup in the code will return the value in foo where zoo matches zoo_chooe not the value in zoo. Is that correct?

Mary

Just one other thing. If [zoo] is a text field you will need to change the DLookup as follows:

Try this ...

Expand|Select|Wrap|Line Numbers

1.  
2. Dim Qty As Integer
3. Qty = DLookup("[foo]", "Bar", "[zoo] =" & Forms!Tester!zoo_choose)
4. DoCmd.OpenReport "rpt_zoo", acViewNormal
5. DoCmd.PrintOut acPrintAll, , , acMedium, Qty, No
6.  
7.  
8.  

The DLookup in the code will return the value in foo where zoo matches zoo_chooe not the value in zoo. Is that correct?

Mary

Yes sorry Mary I got that wrong, by changing the names of the objects, from the original.
Foo is the qty field, which determines how many labels I want to print.
It has just occured to me, that logic would suggest you could enter the whole line in the PrintOut statement, in the appropriate location. I will try this and keep you posted.

Yes sorry Mary I got that wrong, by changing the names of the objects, from the original.
Foo is the qty field, which determines how many labels I want to print.
It has just occured to me, that logic would suggest you could enter the whole line in the PrintOut statement, in the appropriate location. I will try this and keep you posted.

You could but it's probably neater not to.

You could but it's probably neater not to.

Hmmm got me thinking there ...
Below is the code at the moment.
Now this produces only one label out of the thermal and Three out of the Laser A4, which suggests that it is getting the number three there somwhere. :-)

Obviously the printout statement is working on the Me Form that the button is on, I need to delve back a few years, as it is ringing a few bells.
Meanwhile no doubt, your laughing your frock off, cause it is so obvious.

:-)

Cheers all ..

Sorry I'll clean that up a bit.
Hope this is easier to follow.

Is PakTrakID a number or text datatype field?

Sorry I'll clean that up a bit.

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub testprint_Click()
3. On Error GoTo Err_testprint_Click
4.  
5. DoCmd.OpenReport "rpt_lab_freshco", acViewNormal
6. DoCmd.PrintOut , , , acHigh, DLookup("[Count]", "PakTrak", "PakTrakID =" & Forms!Tester![line_choose]), NO
7.  
8. Exit_testprint_Click:
9. Exit Sub
10.  
11. Err_testprint_Click:
12. MsgBox Err.Description
13. Resume Exit_testprint_Click
14.  
15. End Sub
16.  
17.  
18.  

Hope this is easier to follow.

Is PakTrakID a number or text datatype field?

Sorry forgot we already covered this.

Mary

Do you have a design for the table PakTrak?
You are getting the count from a single record (PakTrak.Count) rather than counting the number of records in PakTrak that match your Line_Choose parameter.
If your code works ok for one printer but not for another, I suspect that the problem lies elsewhere than in your code.

Keep the DLookup outside the statement for now until we get if working at least.

In the vb editor click CTRL+G to open the immediate window. Then add the Debug.Print line as in the following code. This will tell you what is being returned to Qty. Run the code and then go back and look in the immediate window to see the result.

Do you have a design for the table PakTrak?
You are getting the count from a single record (PakTrak.Count) rather than counting the number of records in PakTrak that match your Line_Choose parameter.
If your code works ok for one printer but not for another, I suspect that the problem lies elsewhere than in your code.

Yes that is correct. PakTrakID is a unique identity for a line of fruit, maybe 156 boxs, what ever, In PakTrak we link to various tables, but the information, kept in PakTrak is used to tell us what Variety, Size, Packaging, Brand, Xport label, etc etc that line of fruit is, each line is unique, but it has a variable number of boxes in it. (We thought of individual numbers, however a logistical nightmare with casual staff :_0)) The reference to three pertains to a test line with three boxes in it. Now line_choose is a listbox of all those lines, what I wish to achieve is a "select the line, click the button solution" rather than the Pop up form I currently use.

Keep the DLookup outside the statement for now until we get if working at least.

In the vb editor click CTRL+G to open the immediate window. Then add the Debug.Print line as in the following code. This will tell you what is being returned to Qty. Run the code and then go back and look in the immediate window to see the result.

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub testprint_Click()
3. On Error GoTo Err_testprint_Click
4. Dim Qty As Integer
5.  
6. Qty = DLookup("[Count]", "PakTrak", "[PakTrakID] =" & Forms!Tester!zoo_choose) 
7. Debug.Print Qty
8. DoCmd.OpenReport "rpt_lab_freshco", acViewNormal
9. DoCmd.PrintOut , , , acHigh, Qty, NO
10.  
11. Exit_testprint_Click:
12. Exit Sub
13.  
14. Err_testprint_Click:
15. MsgBox Err.Description
16. Resume Exit_testprint_Click
17.  
18. End Sub
19.  
20.  
21.  

I did try this early on, Not the debug, but Dim qty. I had an AhHa moment about 4a.m., re the Printout ringing a bell. I used to print palletcards using Macros, and the printout gave me problems. I found if I opened the report in acPreview, then issued the PrintOut all worked just fine.

I have just tried it, and the Thermal printer spat out three beautiful little labels, and the possibilities are now endless "until the next hurdle" :-).
BUT WAIT THERES MORE (I loathe those commercials)
Because looking at your code I like the "neatness" of it. It defines the copies count first. Also at or about that point I propose we should take a peek at the [line_choose] listbox, and make sure a meaningful selection has been made, otherwise the PrintOut statement will just print the Form the button was clicked from. (Leastways it did on my old Pop-up form, until we tweaked it, It enrages the hell out of Admin office staff when their new colour laser spits out 300 pages of a lovely couloured Access Form :-)))) Very important, ensure your survival I say.)
AND it would be real cool, to decide which xport brand label to print as well.
AND wouldn't it be cool to have a "some labels selection box", I.e., the cool store staff lose 6 labels from a roll of 156, so you want to bang of 6 to complete a pallet. I would achieve this with a nest of buttons, like

So the first sniff for the list_choose finds and error, then set the qty to the [some-qty] field etc etc.
Does that make sense. A bit long winded but I was trying to show you how I thought your code was so much better than my "crash bang" way of doing it.
Anyway onwards and upwards.

Many thanks ... so good to be a part of such a supportive group. My own endeavours, are a second occupation for me, I am a contractor in the viticultural industry during the day, but Databases have been an interest for me ever since I inherited a v1.0 install of access in about 1988 or so...and that interest lead to my involvement with Packing Shed logistics, dispatch etc. A very interesting, challenging, fun environment. And the people I work with, speak the same language, and are always supportive and grateful.

Keep the DLookup outside the statement for now until we get if working at least.

In the vb editor click CTRL+G to open the immediate window. Then add the Debug.Print line as in the following code. This will tell you what is being returned to Qty. Run the code and then go back and look in the immediate window to see the result.

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub testprint_Click()
3. On Error GoTo Err_testprint_Click
4. Dim Qty As Integer
5.  
6. Qty = DLookup("[Count]", "PakTrak", "[PakTrakID] =" & Forms!Tester!zoo_choose) 
7. Debug.Print Qty
8. DoCmd.OpenReport "rpt_lab_freshco", acViewPreview
9. DoCmd.PrintOut , , , acHigh, Qty, NO
10.  
11. Exit_testprint_Click:
12. Exit Sub
13.  
14. Err_testprint_Click:
15. MsgBox Err.Description
16. Resume Exit_testprint_Click
17.  
18. End Sub
19.  
20.  
21.  

So I have modified your code thus

Obviously I now need to tidy up, by closing the previewed report. I will try with acViewHidden as well.
Many thanks. I hope others get something from this, because I am having De Je Vue moments, as I recall other times I have got tangled in stuff like this, and just discarded the idea, and did it the long way.

John S

Try adding to the code as follows:

Alpnz,

It's so heartening to have a response like yours.
'I've learned from what you've posted and taken it on and progressed it myself'.
Also, your absolutely right about others learning from your thread. I understand that happens a lot. Many members post hardly anything at all, just search through and find a similar previous thread.
Lastly, for future ref, Déja Vue is French for Already Seen.

Try adding to the code as follows:

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub Testprint3_Click()
3. On Error GoTo Err_Testprint3_Click
4. Dim Qty As Integer
5. Dim rslt As Integer
6.  
7.   Qty = DLookup("[Count]", "PakTrak", "[PakTrakID] =" & Forms!Tester!line_choose)
8.   DoCmd.OpenReport "rpt_lab_freshco", acViewPreview
9.   rslt = Msgbox("You are about to print " & Qty & " copies of the Report. " & _
10. vbcrlf & "Do you wish to continue?", vbYesNo)
11.   If rslt = vbYes Then
12.     DoCmd.PrintOut , , , acHigh, Qty, NO
13.   End If
14.   DoCmd.Close acReport, "rpt_lab_freshco"
15.  
16. Exit_Testprint3_Click:
17. Exit Sub
18.  
19. Err_Testprint3_Click:
20. MsgBox Err.Description
21. Resume Exit_Testprint3_Click
22.  
23. End Sub
24.  
25.  

:-) Do you ever sleep?
I suspect you are nearer than I think ...
As for the idea ... well yes just like that, it catches the error.
I am going to have a Field beside the Listbox and under the Print Labels button, which would refresh as you selected the rows in the listbox, and then have the nest of * 2 /2, +5 +10 etc buttons around that.

OR

perhaps just a print 5 button, print 2 button. It leads to perhaps wasting 1 or 2 labels for a Pallet, but Mary I suspect you must understand the pressure the Dispatch people work under, as the code you supplied cuts through the nonsense, and gets to the point much better.
I should confess that I have a registered install of Visual Basic.net standard, here which I tried to get to grips with, but gave up, the environment, did not seem familiar some how, but is probably the environment I should have developed this in.

Many thanks
John S

Alpnz,

It's so heartening to have a response like yours.
'I've learned from what you've posted and taken it on and progressed it myself'.
Also, your absolutely right about others learning from your thread. I understand that happens a lot. Many members post hardly anything at all, just search through and find a similar previous thread.
Lastly, for future ref, Déja Vue is French for Already Seen.

NeoPa,

Well at the end of the day, how is it possible to know everything, without reading the book. Invariably someone else has grappled with the idea before, and so in passing we might trade of ideas and techniques. E.g. Mary has very succinctly pointed out, there is VB to use and speed things up with.

I also commend you on correcting my spelling, which is probably because I learnt to spell from a Crosbie,Stills, Nash, and Young album :-)

Alpnz,

lmao.
It's a pleasure dealing with you.
And I commend your taste in music ;).

:-) Do you ever sleep?

Don't start, you'll only encourage NeoPa to start giving out to me again.

I suspect you are nearer than I think ...

Ireland, but I don't sleep much anyway.

As for the idea ... well yes just like that, it catches the error.
I am going to have a Field beside the Listbox and under the Print Labels button, which would refresh as you selected the rows in the listbox, and then have the nest of * 2 /2, +5 +10 etc buttons around that.

OR

perhaps just a print 5 button, print 2 button. It leads to perhaps wasting 1 or 2 labels for a Pallet, but Mary I suspect you must understand the pressure the Dispatch people work under, as the code you supplied cuts through the nonsense, and gets to the point much better.
I should confess that I have a registered install of Visual Basic.net standard, here which I tried to get to grips with, but gave up, the environment, did not seem familiar some how, but is probably the environment I should have developed this in.

Many thanks
John S

Not necessarily John, Although vb.net can have more functionality I firmly believe you can develop more user friendly environments using access and it sounds like that is your priority.

In your place, in the interests of user friendliness, I would use code to get a number from the user regarding the qty and allow the user to freely enter the number they wish to print.

Something like:

Don't start, you'll only encourage NeoPa to start giving out to me again.



Ireland, but I don't sleep much anyway.



Not necessarily John, Although vb.net can have more functionality I firmly believe you can develop more user friendly environments using access and it sounds like that is your priority.

In your place, in the interests of user friendliness, I would use code to get a number from the user regarding the qty and allow the user to freely enter the number they wish to print.

Something like:

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub Testprint3_Click()
3. On Error GoTo Err_Testprint3_Click
4. Dim Qty As Integer
5. Dim rslt As Integer
6.  
7.   Qty = Int(InputBox("How many copies of this report do you wish to print?", "No of Copies"))
8.   DoCmd.OpenReport "rpt_lab_freshco", acViewPreview
9.   rslt = Msgbox("You are about to print " & Qty & " copies of the Report. " & _
10. vbcrlf & "Do you wish to continue?", vbYesNo)
11.   If rslt = vbYes Then
12.     DoCmd.PrintOut , , , acHigh, Qty, NO
13.   End If
14.   DoCmd.Close acReport, "rpt_lab_freshco"
15.  
16. Exit_Testprint3_Click:
17. Exit Sub
18.  
19. Err_Testprint3_Click:
20. MsgBox Err.Description
21. Resume Exit_Testprint3_Click
22.  
23. End Sub
24.  
25.  

Yes I have achieved it like this before. However the Dispatch operator, then commented how irritated she got, having to go from Mouse to keyboard, and I developed the click click click solution with the ability to just edit the number in the box that was being changed using the "math nest" of buttons. I wonder in your idea above, is it possible to have an "All" button on the "InputBox",

Oh and my ancestors (Some of them) left Cavan to come to NZ.

:-)

:-) Do you ever sleep?

Forgot to post this earlier.

MMcCarthy is actually a very clever 'Bot' designed by the forum Admin to keep the forums going. An excellent job ;)
If the trial period proves successful - he will write one for all the forums.

He's considering changing the signature to :-
MaryBot - as designed by KUB365.

Forgot to post this earlier.

MMcCarthy is actually a very clever 'Bot' designed by the forum Admin to keep the forums going. An excellent job ;)
If the trial period proves successful - he will write one for all the forums.

He's considering changing the signature to :-
MaryBot - as designed by KUB365.

Are you saying I could just dump the whole database between the CODE signs, and it would come back "Real Cool".
I will observe that this community is more helpful than EX change, if you know whom I mean, the possibilities of BOTS had not occured to me ~:)

JDS

Forgot to post this earlier.

MMcCarthy is actually a very clever 'Bot' designed by the forum Admin to keep the forums going. An excellent job ;)
If the trial period proves successful - he will write one for all the forums.

He's considering changing the signature to :-
MaryBot - as designed by KUB365.

I'll get you back for that one.

Don't think I haven't noticed you've been disobeying my orders, BTW

Mary

Yes I have achieved it like this before. However the Dispatch operator, then commented how irritated she got, having to go from Mouse to keyboard, and I developed the click click click solution with the ability to just edit the number in the box that was being changed using the "math nest" of buttons. I wonder in your idea above, is it possible to have an "All" button on the "InputBox",

Oh and my ancestors (Some of them) left Cavan to come to NZ.

:-)

It could work.

If you consider the Inputbox returns a string. You will notice I converted it to an integer value.

You could check for this as the value entered by the user before converting.

I'm a little confused though. I thought we were talking about setting a number to decide the number of copies to be printed. Where does "All" come into this.

Mary

Are you saying I could just dump the whole database between the CODE signs, and it would come back "Real Cool".
I will observe that this community is more helpful than EX change, if you know whom I mean, the possibilities of BOTS had not occured to me ~:)

JDS

It's amazing the developments in AI nowdays.

I'll get you back for that one.

Don't think I haven't noticed you've been disobeying my orders, BTW

Mary

What?!? - I rested loads. :scared witless: (Rhymes with that anyway.)
HELP!!
I'm a good boy - honest.

;) -Adrian.

What?!? - I rested loads. :scared witless: (Rhymes with that anyway.)
HELP!!
I'm a good boy - honest.

;) -Adrian.

If I catch you on here at 3.00 am, there'll be trouble.

Mary

I'm gone.

G'Night.

-Adrian.

G'Night Adrian

It could work.

If you consider the Inputbox returns a string. You will notice I converted it to an integer value.

You could check for this as the value entered by the user before converting.

I'm a little confused though. I thought we were talking about setting a number to decide the number of copies to be printed. Where does "All" come into this.

Mary

OK I tried something like this, but I get a compile error, must be the order I am doing things in.
The all would print the number in the PakTrak.[Count] field. In other words a Dlookup of the integer value. Below is the non compiling version.
So what I am suggesting is that we peek at the Label designation, which then decides which Report to open and Print. The above would remove about 3 steps for me, from the current version, of Pop up form, with line selection, copies to print, and Label to print selections on it. Again a wee bell is ringing on the If Then Else statements, I've been here before I am sure of it ....:-)
(-:

One implication I have not mentioned is that all of this was intended last season to remove the need for a Subform for PakTrak on the Pallet form. Much simpler to have a list box. Select the line Edit it, Select the line print labels, Select the line consign etc etc.
I see Possible problems with the listbox [line_choose]

1. Using a mixed Table query to set values in Columns
2. Totalling Columns in the list. I.e. [Count], [Kgs] etc.
3. Possible speed reduction, ??? not sure on this one.

Anyway the adventure continues :-)

Mary and Adrian, you are the best. Many many thanks, I will refer to the improvements as the McCarthy and Co effect ... for ever more.
Have mentioned it opens up a wider vista of much improved functionality.

[An Update
I ducked over to a site nearby to my homebase, and tried this on a live system, It is an Access MSSQL setup. In a listbox with multiple tables in the Row Source, it bombs out, I.e. lab returns no value. On a bare listbox with the one table in the Row Source, it works like a charm, admittedly I had only one argument in the If lab = statement. I would like to nest a list of If lab= Then Openreport statements. Obviously I do not have the familiarity to identify quickly how to quickly code this. Given some time and reading I will.

Hope you all had a good ights rest on the other side of the world. Weve had not a bad day, Clearing Southerly winds, after a night of rain. Spring might come yet :_0

Regards to all
John S

John,

Rather than nested IFs, think about using other constructs:
Choose() will work in a limited set of situations.
Select Case is generally recommended for more complicated situation checking scenarios.

Bear in mind, although Select Case has the format of comparing a single variable against multiple possible values, if you want something a little more flexible, use Select Case TRUE - and each Case statement can then be a boolean expression.

Mary and Adrian, you are the best. Many many thanks, I will refer to the improvements as the McCarthy and Co effect ... for ever more.
Have mentioned it opens up a wider vista of much improved functionality.

BTW I think you're a little generous with your ' & Co effect' I think my contribution to this thread can be summed up as 'He spelt Déja Vue'. Although I enjoyed participating ;).

John,

Rather than nested IFs, think about using other constructs:
Choose() will work in a limited set of situations.
Select Case is generally recommended for more complicated situation checking scenarios.

Bear in mind, although Select Case has the format of comparing a single variable against multiple possible values, if you want something a little more flexible, use Select Case TRUE - and each Case statement can then be a boolean expression.



BTW I think you're a little generous with your ' & Co effect' I think my contribution to this thread can be summed up as 'He spelt Déja Vue'. Although I enjoyed participating ;).

I have not used Choose before, might sound a bit naff, however I only dabble, databases are not my main line of occupation.
An interesting concept. I have it working against the Sub form in the Pallet management form, on the new site, and used just a series of If statements. No lack of speed, however I like the neatness of less code that you are proposing. Besides, if they add brands to the system, I need a way to pickup on the change and relate them to a Label. Fairly involved. One possibility is to actually have a table related to the labels reports. All my Label reports are now named lab_1, lab_2 etc, which relate to the br_id in the brand table. I seem to recall you can relate reports and forms in tables. Least ways I know you can match Word docs and Excel using OLE objects, so there should be no reason not to be able to relate a report in a table. Will have to have a read up of the Bible I use.

If I could acheive this it would be possible to cross match and hatch for them.

Regards
John S

By the way would something like this work?
By doing this, I am giving them the opportunity to set the number to print, otherwise use the PakTrak Count as the number to print. Thingk it would just bomb out to the Count on reflection.

John S

Expand|Select|Wrap|Line Numbers

1. Dim Qty As Integer
2. Qty = Int(InputBox(blah blah))
3. IF Qty = NULL Then ...

I'm almost certain the Int function can't return a Null, and I doubt that an Integer field can hold one. Shouldn't you be checking for zero?

Also, you can't check for Null by saying If X = Null.
It must be If IsNull(X) or (sometimes) If X Is Null.

I'm almost certain the Int function can't return a Null, and I doubt that an Integer field can hold one. Shouldn't you be checking for zero?

Quite right, DJV thankyou,
Put it down to a Senior Moment.

Quite right, DJV thankyou,
Put it down to a Senior Moment.

Sorry guys

This was my error initially.

I accept full responsibility.

What's going on today. Maybe you're right Adrian, my brain has finally fried.

Mary

Hi guys,
Well roll out today for that site. Everything running smooth as a bug.
I resolved to offer two Label printing Buttons. One that just does a Blanket PakTrak Label print, the whole lot, and One offering "Print Some" with the InputBox. I have also used the vbYes/No idea in one or two forms, which has added to functionality for the operator. All in All a much faster process to get labels printed anyway.

One thing I will say is that I would like to tweak it using the Choose() idea from Adrian. However I have not spent much time doing any research.
At the moment it is just a series of If statements.
I start another site tomorrow, and no dubt they will want theirs to be unique in some way also.
I long for the day of having a LAMP based application, which enable me to just work on the one version. Until then, make hay while the sun shines eh :-)

John S

This site will give you the basics of the Choose function.

http://www.techonthenet.com/access/f...ced/choose.php

Glad to hear everything rolled out OK. It's always something of a miracle.

Congratulations!

Mary

This site will give you the basics of the Choose function.
http://www.techonthenet.com/access/f...ced/choose.php

I just had a look there, and I believe there's an error in their examples. Specifically, they seem to think that the nearest whole number to 3.75 is 3. If you agree with them, then disregard this post.

I just had a look there, and I believe there's an error in their examples. Specifically, they seem to think that the nearest whole number to 3.75 is 3. If you agree with them, then disregard this post.

No I caught that as well. Still a small test should clarify the point.

Mary

This site will give you the basics of the Choose function.

http://www.techonthenet.com/access/f...ced/choose.php

Glad to hear everything rolled out OK. It's always something of a miracle.

Congratulations!

Mary

From reading That I get the idea that the code would look something like.

I think this is too simplistic view of the code.

John S

Hey John S,

Something more like....

Choose is not an appropriate function for very many situations.
It looks like you may have found one though :).

Choose is not an appropriate function for very many situations.
It looks like you may have found one though :).

Although, if the situation actually resembles the sample that was posted, a simple concatenation would probably work as well, and be more flexible than Choose (in terms of the number of entries, I mean).

Thats a cool use of choose. One odd thing I did strike was that if I create a Listbox from the PakTrak Table, related to the Pallet table by PalletID, using just the PakTrak table I can refer to the bound column, with a DLookup, however if I add tables to the listbox query, I then strike problems getting the value of say, br_id from the PakTrak table using the listbox bound column as a criteria in a DLookup of br_id. The lab_1 lab_2 is determined by a value in a field [br_id] in the PakTrak table. I could add the br_id to the current subform on the Pallet Form, to be able to refer to it using Choose. However the whole thread started on the subject of using listboxs, and VB to set numbers to print. We have worked our way through a number of great ways to achieve this, and I hope anyone searching will find the help I rcvd here as useful as I have. Ideally I would like to have a listbox, rather than a Subform. It looks neater, and more concise. And only one form. What I should do for you all is post some screenshots on a website somewhere, and let you get an idea of what I am trying to achieve.
By the way Mary you have unleashed a monster with your small piece of code on the VB yes/no rslt code some replies ago. I have used it quite a bit, to place some final decisions onto the operator, to confirm an action. Really adds functionality, and empowers the operator with a sense of control over whats happening. One other aspect of my Label printing buttons, is that because I do not know what report is going to open, I cannot issue a single DoCmd.Close acReport, " " after the printout Command. I had thought a simple line like that would work, it does not, it requires the object name. However I do not know the name, OR do I issue another set of If statements, to close the report. Hmmmm

Anyway, great to read about choose, and to see it here as well. Many thanks.

John S

simple line like that would work, it does not, it requires the object name. However I do not know the name, OR do I issue another set of If statements, to close the report. Hmmmm

Anyway, great to read about choose, and to see it here as well. Many thanks.

John S[/quote]

Stupid B^&*(), All I ahve to do is say DoCmd.Close "rpt" or similar. :-)))