If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Marking records as printed
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 |
#2
|
|||
|
|||
Marking records as printed
"Sue" wrote in message
... 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. 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 |
#3
|
|||
|
|||
Marking records as printed
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" wrote: "Sue" wrote in message ... 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. 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Deleting specific records | [email protected] | General Discussion | 6 | June 22nd, 2005 11:35 PM |
Problem with VBA Code behind form | Tom | Using Forms | 11 | February 21st, 2005 05:42 PM |
Problem with VBA Code behind form | Tom | General Discussion | 11 | February 21st, 2005 05:42 PM |
Edit properties of linked subforms problem (repost) | Allen Browne | Using Forms | 3 | November 12th, 2004 08:16 PM |
selecting multiple records | sps | Using Forms | 3 | August 3rd, 2004 08:22 PM |