Print reports after specified number of command button clicks

[larryimic]

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?

[Scott Price]

You have posted your question in the Access Articles section rather than the Access Forum section.
I have moved it across for you.

MODERATOR.

[ADezii]

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.
   

   Public intTimesClicked As Integer

2. In the Click() Event of a Command Button, place the following code:
   

   Dim intRequiredClicks As Integer
    
   intTimesClicked = intTimesClicked + 1
    
   'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is Numeric
   intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] =" & Me![txtPartNo])
    
   'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is a String
   intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] ='" & Me![txtPartNo] & "'")
    
   If intRequiredClicks = intTimesClicked Then
     DoCmd.OpenReport "rptWhatever", acViewNormal
       intTimesClicked = 0     'Reset the old clicker
   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.
   

   intTimesClicked = 0

[larryimic]

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

[Luke Bailey]

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).

[ADezii]

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.

[larryimic]

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.

[ADezii]

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.

[Peader]

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.
   

   Public intTimesClicked As Integer

2. In the Click() Event of a Command Button, place the following code:
   

   Dim intRequiredClicks As Integer
    
   intTimesClicked = intTimesClicked + 1
    
   'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is Numeric
   intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] =" & Me![txtPartNo])
    
   'Assuming a Field on your Form contains the PartNo and is called txtPartNo, and it is a String
   intRequiredClicks = DLookup("[Mulqty_01]", "tbl_partkbqty", "[PartNo] ='" & Me![txtPartNo] & "'")
    
   If intRequiredClicks = intTimesClicked Then
     DoCmd.OpenReport "rptWhatever", acViewNormal
       intTimesClicked = 0     'Reset the old clicker
   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.
   

   intTimesClicked = 0

[Scott Price]

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.