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

Print reports after specified number of command button clicks

P: 3
I have created a Access production database that records good parts and bad parts to a table thru querys using macros and command buttons on a form. A report (part label) is printed each time a command button is clicked for good or bad parts. When the container is filled with good parts, the operator manualy clicks a command button to print a report (container label). I have been asked to automate the container label report so it prints when the correct number of good part command button clicks have been performed. I think this can be done using a module to keep track of the number of operations of the good part command button, print the report and reset the count, but I do not have any knowledge of VB. The correct number of parts value will be selected from a table 'tbl_partkbqty' that contains the part number - 'PartNo' - column and quantity - 'Mulqty_01' - column. Can anyone give me instruction on how this can be accomplished? Is it is possible to perform this operation using standard Access macros or will VB be necessary?
Oct 1 '07 #1
Share this Question
Share on Google+
9 Replies


Scott Price
Expert 100+
P: 1,384
You have posted your question in the Access Articles section rather than the Access Forum section.
I have moved it across for you.

MODERATOR.
Oct 1 '07 #2

ADezii
Expert 5K+
P: 8,692
I have created a Access production database that records good parts and bad parts to a table thru querys using macros and command buttons on a form. A report (part label) is printed each time a command button is clicked for good or bad parts. When the container is filled with good parts, the operator manualy clicks a command button to print a report (container label). I have been asked to automate the container label report so it prints when the correct number of good part command button clicks have been performed. I think this can be done using a module to keep track of the number of operations of the good part command button, print the report and reset the count, but I do not have any knowledge of VB. The correct number of parts value will be selected from a table 'tbl_partkbqty' that contains the part number - 'PartNo' - column and quantity - 'Mulqty_01' - column. Can anyone give me instruction on how this can be accomplished? Is it is possible to perform this operation using standard Access macros or will VB be necessary?
  1. Declare a Public Variable in a Standard Code Module to hold the number of Clicks.
    Expand|Select|Wrap|Line Numbers
    1. Public intTimesClicked As Integer
  2. In the Click() Event of a Command Button, place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Dim intRequiredClicks As Integer
    2.  
    3. intTimesClicked = intTimesClicked + 1
    4.  
    5. 'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is Numeric
    6. intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] =" & Me![txtPartNo])
    7.  
    8. 'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is a String
    9. intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] ='" & Me![txtPartNo] & "'")
    10.  
    11. If intRequiredClicks = intTimesClicked Then
    12.   DoCmd.OpenReport "rptWhatever", acViewNormal
    13.     intTimesClicked = 0     'Reset the old clicker
    14. End If
  3. Since the Variable is Public, you'll need an Option to Reset it in some Event (Current(), Click(), etc.). Idf you don't Reset it, it will maintain its value from Record to Record.
    Expand|Select|Wrap|Line Numbers
    1. intTimesClicked = 0
Oct 2 '07 #3

P: 3
Thanks a bunch, this really helps. One problem however, the Click() event of the command button that is to be counted already runs a macro called Good++, which, among other things prints the report "part label". Can I create a module with the vb code you supplied in lines 1,3, 9, 11, 12, 13 and 14; name the module, add the RunCode action to the macro and call the named module as the Function argument?
Also, it appears the comand to print the report will only display the report. I need it to print directly to the printer without viewing, or am I making an assumption because of my lack of vb knowledge?
Thanks Again,
Larryimic
Oct 2 '07 #4

P: 21
Thanks a bunch, this really helps. One problem however, the Click() event of the command button that is to be counted already runs a macro called Good++, which, among other things prints the report "part label". Can I create a module with the vb code you supplied in lines 1,3, 9, 11, 12, 13 and 14; name the module, add the RunCode action to the macro and call the named module as the Function argument?
Also, it appears the comand to print the report will only display the report. I need it to print directly to the printer without viewing, or am I making an assumption because of my lack of vb knowledge?
Thanks Again,
Larryimic
Another option that I have used in the past when I don't feel like playing with public variables is that you can insert a textbox into your form and make it's visible setting set to false. Then, every time you click the button just increase the value of the textbox by one.

If you want to separate your macros, you could set the print macro to run off of the textbox_AfterUpdate event, that way you could keep your current button macro (just add the +1 to the textbox step).
Oct 2 '07 #5

ADezii
Expert 5K+
P: 8,692
Thanks a bunch, this really helps. One problem however, the Click() event of the command button that is to be counted already runs a macro called Good++, which, among other things prints the report "part label". Can I create a module with the vb code you supplied in lines 1,3, 9, 11, 12, 13 and 14; name the module, add the RunCode action to the macro and call the named module as the Function argument?
Also, it appears the comand to print the report will only display the report. I need it to print directly to the printer without viewing, or am I making an assumption because of my lack of vb knowledge?
Thanks Again,
Larryimic
Why not execute the code directly that prints the Report, then run the Good++ Macro eliminating the reference to OpenReport? The acViewNormal Parameter should Print the report directly.
Oct 2 '07 #6

P: 3
Sorry for taking so long to get back. This was a great idea, thank you so much. I Took the code you provided, added the code from the macro and in the Click() event of the command button choose code builder, typed in the two codes as one event and it works like a charm. This site is great, I just wish I had known about it years ago. Again, thank you for all your help.
Oct 9 '07 #7

ADezii
Expert 5K+
P: 8,692
Sorry for taking so long to get back. This was a great idea, thank you so much. I Took the code you provided, added the code from the macro and in the Click() event of the command button choose code builder, typed in the two codes as one event and it works like a charm. This site is great, I just wish I had known about it years ago. Again, thank you for all your help.
We are always glad to help.
Oct 9 '07 #8

P: 19
Hello ADezii

I've a very similar problem to Larryimic's. I'm trying to print a report after a specific number (10) of command button clicks and also I'm very new to this. I understand the principle behind the program you describe however can you help me understand the specific function (and difference) of the lines 6 & 9.

Thanks for any advise you could provide,
Peader

  1. Declare a Public Variable in a Standard Code Module to hold the number of Clicks.
    Expand|Select|Wrap|Line Numbers
    1. Public intTimesClicked As Integer
  2. In the Click() Event of a Command Button, place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Dim intRequiredClicks As Integer
    2.  
    3. intTimesClicked = intTimesClicked + 1
    4.  
    5. 'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is Numeric
    6. intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] =" & Me![txtPartNo])
    7.  
    8. 'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is a String
    9. intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] ='" & Me![txtPartNo] & "'")
    10.  
    11. If intRequiredClicks = intTimesClicked Then
    12.   DoCmd.OpenReport "rptWhatever", acViewNormal
    13.     intTimesClicked = 0     'Reset the old clicker
    14. End If
  3. Since the Variable is Public, you'll need an Option to Reset it in some Event (Current(), Click(), etc.). Idf you don't Reset it, it will maintain its value from Record to Record.
    Expand|Select|Wrap|Line Numbers
    1. intTimesClicked = 0
Oct 15 '07 #9

Scott Price
Expert 100+
P: 1,384
Peader,

You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from his needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions).
Please post your questions in their own threads in future (See POSTING GUIDELINES ).

Especially pay attention to this section about How to ask a question , as you will need to reword your question to indicate exactly what you have tried, and what is not working for you.

MODERATOR.
Oct 15 '07 #10

Post your reply

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