By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,797 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Setting the numbers of copies to print in VB

alpnz
100+
P: 113
Hi,
I am trying to set the numbers of copies to print, when a button is clicked on a form. It is printing Labels
In the Click Event I have
Expand|Select|Wrap|Line Numbers
  1.  
  2. DIM Qty As ???, 
  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.  
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.
What I want to do is set the copies in the Printout command using the number in the field.
Mary can you help ... this is a problem I have had a go at in the past, and I think my logic is all wrong.
Nov 18 '06 #1
Share this Question
Share on Google+
70 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.  
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
Nov 18 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Just one other thing. If [zoo] is a text field you will need to change the DLookup as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Qty = DLookup("[foo]", "Bar", "[zoo] ='" & Forms!Tester!zoo_choose & "'")
  3.  
  4.  
Nov 18 '06 #3

alpnz
100+
P: 113
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.
Nov 18 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 18 '06 #5

alpnz
100+
P: 113
You could but it's probably neater not to.
Hmmm got me thinking there ...
Below is the code at the moment.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub testprint_Click()
  3. On Error GoTo Err_testprint_Click
  4. DoCmd.OpenReport "rpt_lab_freshco", acViewNormal
  5. DoCmd.Printout acPrintAll,,,acHigh,DLookup("[Count]", "PakTrak", "PakTrakID =" & Forms!tester![line_choose]), No
    Exit_testprint_Click:
  6.     Exit Sub
  7. Err_testprint_Click:
  8.      MsgBox Err.Description
  9. Resume Exit_testprint_ClickEnd Sub
  10.  
  11.  
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 ..
Nov 18 '06 #6

alpnz
100+
P: 113
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.
Nov 18 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 18 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Is PakTrakID a number or text datatype field?
Sorry forgot we already covered this.

Mary
Nov 18 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
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.
Nov 18 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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.  
Nov 18 '06 #11

alpnz
100+
P: 113
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.
Nov 18 '06 #12

alpnz
100+
P: 113
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub testprint_Click()
  2. On Error GoTo Err_testprint_Click
  3.  
  4.         DoCmd.OpenReport "rpt_lab_freshco", acViewPreview
  5.         DoCmd.PrintOut , , , acHigh, DLookup("[Count]", "PakTrak", "PakTrakID =" & Forms!Tester![line_choose]), NO
  6.  
  7. Exit_testprint_Click:
  8.     Exit Sub
  9.  
  10. Err_testprint_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_testprint_Click
  13.  
  14. End Sub
  15.  
  16.  
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command81_Click()
  3. PrintCount.Value = (PrintCount - 10)
  4. End Sub
  5.  
  6. Private Sub Command82_Click()
  7. PrintCount.Value = (PrintCount / 2)
  8. End Sub
  9.  
  10. Private Sub Command83_Click()
  11. PrintCount.Value = (PrintCount * 2)
  12. End Sub
  13.  
  14.  
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.
Nov 18 '06 #13

alpnz
100+
P: 113
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Testprint3_Click()
  3. On Error GoTo Err_Testprint3_Click
  4. Dim Qty As Integer
  5. Qty = DLookup("[Count]", "PakTrak", "[PakTrakID] =" & Forms!Tester!line_choose)
  6. Debug.Print Qty
  7. DoCmd.OpenReport "rpt_lab_freshco", acViewPreview
  8. DoCmd.PrintOut , , , acHigh, Qty, NO
  9.  
  10. Exit_Testprint3_Click:
  11.     Exit Sub
  12.  
  13. Err_Testprint3_Click:
  14.     MsgBox Err.Description
  15.     Resume Exit_Testprint3_Click
  16.  
  17. End Sub
  18.  
  19.  
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
Nov 18 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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.  
Nov 18 '06 #15

NeoPa
Expert Mod 15k+
P: 31,494
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.
Nov 18 '06 #16

alpnz
100+
P: 113
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
Nov 18 '06 #17

alpnz
100+
P: 113
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 :-)
Nov 18 '06 #18

NeoPa
Expert Mod 15k+
P: 31,494
Alpnz,

lmao.
It's a pleasure dealing with you.
And I commend your taste in music ;).
Nov 18 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
:-) 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:

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.  
Nov 18 '06 #20

alpnz
100+
P: 113
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.

:-)
Nov 18 '06 #21

NeoPa
Expert Mod 15k+
P: 31,494
:-) 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.
Nov 18 '06 #22

alpnz
100+
P: 113
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
Nov 18 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 18 '06 #24

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 18 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Nov 18 '06 #26

NeoPa
Expert Mod 15k+
P: 31,494
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.
Nov 18 '06 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 18 '06 #28

NeoPa
Expert Mod 15k+
P: 31,494
I'm gone.

G'Night.

-Adrian.
Nov 18 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
G'Night Adrian
Nov 18 '06 #30

alpnz
100+
P: 113
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.
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. Dim lab As Integer
  7.  
  8.   lab = DLookup("[lab_id]", "PakTrak", "PakTrakID =" & Forms!tester![line_choose])
  9.   Qty = Int(InputBox("How many labels do you wish to print?", "No of Labels"))
  10.  
  11.     If lab = "1" Then
  12.             DoCmd.OpenReport "rpt_lab_freshco", acViewPreview
  13.     Else
  14.         If lab = 2 Then
  15.             DoCmd.OpenReport "rpt_lab_csi", acViewPreview
  16.     End If
  17.  
  18.     rslt = MsgBox("You are about to print " & Qty & " Labels. " & _
  19. vbCrLf & "Do you wish to continue?", vbYesNo)
  20.   If rslt = vbYes Then
  21.     DoCmd.PrintOut , , , acHigh, Qty, NO
  22.   End If
  23.   DoCmd.Close acReport, "rpt_lab_freshco"
  24. Exit_Testprint3_Click:
  25.     Exit Sub
  26.  
  27. Err_Testprint3_Click:
  28.     MsgBox Err.Description
  29.     Resume Exit_Testprint3_Click
  30.  
  31. End Sub
  32.  
  33.  
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 ....:-)
(-:
Nov 18 '06 #31

alpnz
100+
P: 113
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.
Nov 18 '06 #32

alpnz
100+
P: 113
[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
Nov 19 '06 #33

NeoPa
Expert Mod 15k+
P: 31,494
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 ;).
Nov 19 '06 #34

alpnz
100+
P: 113
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
Nov 20 '06 #35

alpnz
100+
P: 113
By the way would something like this work?
Expand|Select|Wrap|Line Numbers
  1. Dim Qty As Integer
  2. Qty = Int(InputBox("How many labels do you wish to print?", "No of Labels"))
  3. IF Qty = NULL Then
  4.     Qty = DLookup("[Count]", "PakTrak", "[PakTrakID =", & Forms![Pallet].[PakTrak]![PakTrakID])
  5.  
  6. Etc Etc
  7.  
  8.  
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
Nov 20 '06 #36

Expert 5K+
P: 8,434
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?
Nov 20 '06 #37

NeoPa
Expert Mod 15k+
P: 31,494
Also, you can't check for Null by saying If X = Null.
It must be If IsNull(X) or (sometimes) If X Is Null.
Nov 20 '06 #38

alpnz
100+
P: 113
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.
Nov 20 '06 #39

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 20 '06 #40

alpnz
100+
P: 113
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
Nov 21 '06 #41

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 21 '06 #42

Expert 5K+
P: 8,434
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.
Nov 21 '06 #43

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 21 '06 #44

alpnz
100+
P: 113
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim Lab As Integer
  3.  
  4. Choose("Lab",(OpenReport "lab_1",acViewPreview),(OpenReport "Lab_2,acViewPreview), etc etc
  5.  
  6. Printout ,  etc etc
  7.  
I think this is too simplistic view of the code.

John S
Nov 21 '06 #45

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey John S,

Something more like....

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim Lab As Integer
  3. Dim rptName As String
  4.  
  5.   Lab = Me.[Control on form with lab number] 
  6.   ' e.g. Lab=1 refering to lab_1 report
  7.  
  8.   rptName = Choose(Lab,"lab_1","Lab_2","Lab_3", etc etc)
  9.    DoCmd.OpenReport rptName
  10.    DoCmd.Printout , etc etc
  11.  
Nov 21 '06 #46

NeoPa
Expert Mod 15k+
P: 31,494
Choose is not an appropriate function for very many situations.
It looks like you may have found one though :).
Nov 21 '06 #47

Expert 5K+
P: 8,434
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).
Nov 21 '06 #48

alpnz
100+
P: 113
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
Nov 22 '06 #49

alpnz
100+
P: 113
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. :-)))
Nov 22 '06 #50

70 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.