Automate Print Labels After Set Number of Table Entries

[Peader]

Hello,

I’d like to make an inquiry into how to best perform a task in access 2003. I have a form which enters data into a table. This in turn is linked to a label. After ten data entries into the table, I’d like a label to print. Is it best for me to use a counter on the ‘save’ button in my form or is there an easier way to do this? I’d also like to be able to print a partial label say 3, 5 or 7 enters through my process, I’m thinking maybe a second ‘instant pint’ button on the form would cover this however if I tie the ‘save’ button to a 10 count, will it over complicate my code. I’m a beginner so I’d really like to keep it simple. Any words from wiser people would be appreciated.

Thanks,
Peader

[nico5038]

Best probably to add a "Printed" YesNo field.
When a row has been entered a query counting the records with PrintedYN = False will give the newly entered rows. When greater or equal 10 run the labelprint and issue an update query to set the PrintedYN field to True.

For the counting you can use the DCount() function.

Getting the idea ?

Nic;o)

[Peader]

thanks for pointing me in right direction nico5038, it's taken me a while however i'm beginning to make progress

[nico5038]

Don't hesitate to post when stuck. I'm here to help.
I start always with "pointers" as I know how much satisfaction it gives when you do a job (almost) by yourself.

Nic;o)

[Peader]

Thanks Nico
I've prepared the table, macro and query's however I'm now having trouble running a IF statement to check the printed files, any idea's on where I'm going wrong?

    IF (dcount(printed, '10pkBoxLabels ',[printed = false] >10 then
'I've set up a macro to print the label
    stDocName = "Open10pkLabelMacro"
'then another query to update the printed fields
    stDocName = "UpdatePrintedQuery"
    End IF
 

I have it set up to run on a command button execution.

Thanks,
Peader

[nico5038]

Hmm, your Dcount is terribly mutilated, try:

IF (dcount('printed', '10pkBoxLabels','[printed] = false') >10) then
 

Please pay more attention to the syntax and how to use () and [].
The Access helpfile (F1) gives some pritty good descriptions.

Nic;o)

[Peader]

I corrected the Dcount statement to:

If (DCount("[printed]", "10pkBoxLabels", "[printed] = False") > 10) Then
stDocName = "Open10pkLabelMacro"
stDocName = "UpdatePrintedQuery"
End If

However it still doesn't work. Regardless of the number of printed true's/false's it does not run the macro or update the table, no warnings, appears nothing is happening . Could it be related to the fact that prior to running the If statement i already run an update query to modify the source table?

I'm guessing the previous query shouldn't matter. The IF statement looks pretty simple. I'm trying to 'kis' (helps me understand whats happen, no point getting carried away with myself on my first database :) )

[nico5038]

When your previous update does change the [printed], then it's obvious not triggering this IF statement.
Just place a breakpoint (click in left "ruler" to get a round dot) and start the code from the form by pressing the button.
The code will halt on the breakpoint and now press F8 for single line execution to see what happens.

Nic;o)

[Peader]

Nico thanks for the advise so far it's been very helpful.

No joy when i inserted the break point and used F8 to skip down the line in the code however it didn't seam to have an effect.

I've created the Dcount function in a query to check it's correct, it worked fine.

I then removed the IF statement and left the macro, my thinking was the macro should have run however it didn't.

Here's the preceding code:
'go to last record, this line is probably not needed however my code works with it, so i left it in

    DoCmd.GoToRecord , , acLast

'insert new record

    DoCmd.GoToRecord , , acNewRec

'I run an update query to pair up details from a external excel file with the item selected in the form, i turn off then on warnings so the user doesn't have to repeatedly check ok

    DoCmd.SetWarnings False
    stDocName = "10pcksStickerDescription"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.SetWarnings True

'i had ' out the IF statement to check if the macro ran here, however it didn't

    'If (DCount("printed", "10pkBoxLabels", "printed = False") > 10) Then
    stDocName = "Open10pkLabelMacro"
    'stDocName = "UpdatePrintedQuery"
    'End If

I then moved the IF portion to run after an update, still no joy.
I then removed the update query that matches the external data, however it still appears the IF statement does not run.

I'm really lost here as to why something that appears to be correct is not getting picked up when the code is ran?

Any advise or explanation of whats happening would be very much appreciated.

[nico5038]

When the code isn't executed, then you're using the wrong event.
What event did you use for your code ?

Nic;o)

[Peader]

Hello Nico

I use the On Click event to run. I also tried the after update event specifically for the IF statement alone however this didn't work!?!?

Thanks,
Peader

[nico5038]

When in the OnClick event of a button, the code should execute.
Make sure the breakpoint is set within the eventcode and try again.

Nic;o)

[Peader]

Oh yes, a thing of amazement, it's working. :)

Nic;o) thanks for the advise and direction. You've really gone out of your way to be more than helpful.

Here's the final IF Statement code for anybody reading this thread in the future.

    If (DCount("printed", "10pkBoxLabels", "printed = False") > 10) Then
    stDocName = "Open10pkLabelMacro"
    DoCmd.RunMacro stDocName
    DoCmd.SetWarnings False
    stDocName = "UpdatePrintedQuery"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.SetWarnings True
    End If

My problems were simply lack of experience related. While i might not have though it at times over the last couple of weeks, access really is a beautiful thing :P

[nico5038]

Glad it's working for you now :-)

Success with your application !

Nic;o)