Marking records as printed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in my table called SSRep which contains a text character N for
each record (which means that the ssrep report has not been printed for the
record). After the ssrep report has been printed I want the N to be replaced
with a P. I am new to vba and not sure what code to use or where the code
should go.
Any help would be much appreciated.
Susan
 
Sue said:
I have a field in my table called SSRep which contains a text character N
for
each record (which means that the ssrep report has not been printed for
the
record). After the ssrep report has been printed I want the N to be
replaced
with a P. I am new to vba and not sure what code to use or where the code
should go.
Click to expand...

Without knowing when you want the update performed, we couldn't tell you
where you would want to put any VBA. The following Query does what you want,
where the Field is named "HasBeenPrinted".

UPDATE SSRep SET SSRep.HasBeenPrinted = "P"
WHERE [SSRep.HasBeenPrinted] = "N";

Printers, particularly in busy workplaces, can easily jam or experience
other failures, and a report executed and printed may not be complete... it
may be in a stack of crumpled paper inside the printer or have brought the
printer to a halt only partway through. For that reason, I put the "set has
been printed" kind of code (it may well have more complex criteria than the
simple one shown) in code behind a Command Button, and instruct the user not
to press that button until they have reviewed the report output.

DAO code to execute that Query, if you name it qupdHasPrinted, would be:

Private Sub cmdHasPrinted_Click()
On Error GoTo Err_cmdHasPrinted_Click

Dim stDocName As String

stDocName = "qupdHasPrinted"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdHasPrinted_Click:
Exit Sub

Err_cmdHasPrinted_Click:
MsgBox Err.Description
Resume Exit_cmdHasPrinted_Click

End Sub

This code was generated by the Command Button Wizard. All I had to do was
respond to prompts and point and click. Choose Miscellaneous in the First
Dialog, and select Run Query.

If P and N are the only values, you have used a text field to represent a
Yes/No. There is a Yes/No type of field which you could use instead of text,
but the text field works just fine.

Larry Linson
Microsoft Access MVP
 
Larry
Many thanks for your reply.

I had already created a button that when clicked will print my report. The
code on click is as follows:-

Private Sub PrintSSRep_Click()
On Error GoTo Err_PrintSSRep_Click

Dim stDocName As String

stDocName = "Soil Samples"
DoCmd.OpenReport stDocName, acNormal

Exit_PrintSSRep_Click:
Exit Sub

Err_PrintSSRep_Click:
MsgBox Err.Description
Resume Exit_PrintSSRep_Click

End Sub

You were right, I do want a message box that asks the user whether the
report has printed correctly or not. If yes it has, then I assume I can call
the query you set out, but if the user selects "no" the records need to be
still available for printing again. I could use a Yes/No type field in my
table. Can I include if statements within the code already created from my
print report button??
Thanks
Sue

Larry Linson said:
Sue said:
I have a field in my table called SSRep which contains a text character N
for
each record (which means that the ssrep report has not been printed for
the
record). After the ssrep report has been printed I want the N to be
replaced
with a P. I am new to vba and not sure what code to use or where the code
should go.
Click to expand...

Without knowing when you want the update performed, we couldn't tell you
where you would want to put any VBA. The following Query does what you want,
where the Field is named "HasBeenPrinted".

UPDATE SSRep SET SSRep.HasBeenPrinted = "P"
WHERE [SSRep.HasBeenPrinted] = "N";

Printers, particularly in busy workplaces, can easily jam or experience
other failures, and a report executed and printed may not be complete... it
may be in a stack of crumpled paper inside the printer or have brought the
printer to a halt only partway through. For that reason, I put the "set has
been printed" kind of code (it may well have more complex criteria than the
simple one shown) in code behind a Command Button, and instruct the user not
to press that button until they have reviewed the report output.

DAO code to execute that Query, if you name it qupdHasPrinted, would be:

Private Sub cmdHasPrinted_Click()
On Error GoTo Err_cmdHasPrinted_Click

Dim stDocName As String

stDocName = "qupdHasPrinted"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdHasPrinted_Click:
Exit Sub

Err_cmdHasPrinted_Click:
MsgBox Err.Description
Resume Exit_cmdHasPrinted_Click

End Sub

This code was generated by the Command Button Wizard. All I had to do was
respond to prompts and point and click. Choose Miscellaneous in the First
Dialog, and select Run Query.

If P and N are the only values, you have used a text field to represent a
Yes/No. There is a Yes/No type of field which you could use instead of text,
but the text field works just fine.

Larry Linson
Microsoft Access MVP
Click to expand...
 
Back
Top