Printing updated records (Access 2K/SR1)

Topic

New Reply

I had a request from an end-user that stumped me.

Below is an excerpt of what he wanted…

“lognotes that have new entries during the month of July would be listed and then printed. Of course we would want them to print from the point were new notes have been added, not from the beginning. The average minimum might have only one additional page per client identified. ”

How do I tell Access to identify those records that has been printed already and ignore them?

TIA.

Reply | Quote

Replies

Create a print log table and enter the PK for the records printed and the print date into that log when you print the report. Then use that table in a query to exclude any records that have already been printed.

Reply | Quote

Thanks for your suggestion. How do I get this information entered into the table automatically?

Mark, thanks for the warning, I believe in my situation, it isn’t mandatory that the method to be foolproof. As long as it works most of the time, it will do. Grin.

Reply | Quote

First of all, you would key the log table to have a unique index on the Primary Key you were adding, which will keep you from adding or printing the same record twice. Keep in mind though, that that will also keep you from printing it again if you need to.

I would create an append query that used the same query as the recordsource of report you’re printing for its table and append the PK from that query to the log table along with today’s date. There are more elaborate and more accurate ways to do it, but this will get you most of what they want. You would execute this query right after printing the report.

If you find you *do* need to reprint certain records, you’ll have to remove their records from the log table first.

Reply | Quote

Thanks for your help. I’ll try that.

Reply | Quote

Charlotte, how do you automatically run an action query after printing a report? I have an append query that does exactly what I need it to do. However, I’m not sure how do I tell the report to run the Append query?

Secondly, do I add the log table to the query I use to prepare the Log Notes report in order to limit the records to those that has not been entered into the log?

Reply | Quote

Second question first: Yes you need to add the log table to the query to only include those that don’t already exist.

First question: you have a couple of options. One is to run the query from the Close event of the report, assuming that the user (or your code) closes the report after it prints it. The other is to use the code that prints the report to run the append query. In the latter case, you would put it right after the DoCmd.OpenReport or whatever. However, in that case, it will run before the report actually finishes printing. Neither option really knows whether the report has been printed or not, so how accurate does this need to be?

Reply | Quote

I’d need the report to append after printing. I’ll try both options and see which works the best.

By the way, I found this article in Microsoft KB: Q154894 that does practically the same thing for Access 97. I tried it out and it worked great, however, it only enters the name of the report. I’d love to modify that code to also enter the specific PERSON the log notes is for (In my report, there is a “field” that prints name of person). Do you want me to attach the code that is listed in that article if you think it would be a better way of doing it?

TIA again.

Reply | Quote

Just an update…

I decided to use RunSQL code because I didn’t want to open Append Query window each time the report closes. However, there are two new issues:

1) Since I use a parameter query to ask the user for the name of person that the user want to see log notes for. Now, each time I close the report, it asks me for the name of client. As far as I can tell, it is because I am using Append query based on the parameter query that I’m using to filter the reports on. How do I tell RunSQL to extract the name from the report itself so that it wouldn’t ask me for name again after closing?

2) It warns me that it would ‘append’ one new record to the table. I want to turn off this warning and it seems like using DoCmd.SetWarning 0 doesn’t work.

TIA

Reply | Quote

I decided to use RunSQL code because I didn’t want to open Append Query window each time the report closes.

When you open an action query in code, it just executes, it doesn’t open the query window. However, this should work with RunSQL

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

You have to declare a parameter object for your query and then pass it the value it’s looking for, but you can’t do that using RunSQL. You’ll need to post your code to get more help, since DAO and ADO are very different in the way you create and pass query parameters and we can’t guess at exactly what you’re doing.

Reply | Quote

All righty. Here’s the snippet.

Private Sub Report_Close()
DoCmd.RunSQL “INSERT INTO tblPrintedReports ( ClientID )” & _
“SELECT First(LogNotesQry.[Client ID]) AS [FirstOfClient ID]” & _
“FROM LogNotesQry”

DoCmd.SetWarnings False

End Sub

After I posted the question about SetWarnings, I read the help file again and realized that I had used 0 instead of FALSE so I changed that and it worked perfectly.

Reply | Quote

You need to set warnings to false *before* you do something that generates error messages or notifications. If you do it after, the way you have here, you’ve set warnings off for the rest of the application, which is *not* what you want to do.

How familiar are you with ADO? If you’re more comfortable with DAO, then I’ll post some code using that object model. In either case, you can’t use RunSQL if you need to pass parameters into the query. Oh, and the parameters need to be set for the query from the Query–>Parameters menu item, not just by putting them into the criteria. Have you done that?

Reply | Quote

Interesting tidbit about SetWarnings. I’ll move it so that it would turn off the warning only for the append event.

I’m not too sure what is the difference between ADO and DAO? I have only 3 months’ worth of experience in VBA. I’ve been working a lot with Macros for a while and decided to take a jump into VBA, hence all those questions of mine! grin. Yeah, I know how to use Parameters in the query instead of using Criteria. I usually find it faster using criteria field but for this purpose, I’ll use the Parameter.

Reply | Quote

You don’t really have a choice when you’re going to create the parameter in code. You have to have added it to the parameters collection of the query to make it work. Here’s a routine I wrote way back to handle parameter queries:

Public Function RunParamQuery(ByVal strQryNm As String, _
                      ParamArray varParamValues()) As Boolean
  'Created by Charlotte Foust
  '12/9/99 
  On Error GoTo Proc_err
  Dim dbs As DAO.Database
  Dim wsp As DAO.Workspace
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Dim blnOK As Boolean
  Dim varPrmType As Variant
  Dim strName As String
  Dim blnPrmVal As Boolean
  Dim blnPrmSet As Boolean
  Dim intLoop As Integer
  Dim intNumParams As Integer
  Dim intQDFType As Integer
  Dim varParamValue As Variant
  
  Const DUPLICATE_KEY_OR_INDEX = 3022
  
  blnOK = True
  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs(strQryNm)
  intNumParams = qdf.Parameters.Count
  ' See how many parameters there are to pass
  If intNumParams = UBound(varParamValues) + 1 Then
      blnPrmVal = True
  Else
    blnOK = False
  End If 'intNumParams = UBound(varParamValues) + 1 
  If Not blnPrmVal Then
   ' Loop through the parameters and
   ' prompt user for values except for
   ' any passed in the call
    If intNumParams = 0 Then
      intNumParams = 1
    End If  ' intNumParams = 0 
    For intLoop = 0 To intNumParams - 1
      blnPrmSet = False
      Set prm = qdf.Parameters(intLoop)
      strName = prm.Name
      varPrmType = prm.Type
      On Error Resume Next
      prm = varParamValues(intLoop)
      If Err = 0 Then
        blnPrmSet = True
      End If ' Err = 0 
      On Error GoTo Proc_err
      If Not blnPrmSet Then
          varParamValue = InputBox(strName, "Enter value")
          Select Case varPrmType
            Case vbLong
              varParamValue = CLng(varParamValue)
            Case vbInteger
              varParamValue = CInt(varParamValue)
            Case vbDouble
              varParamValue = CDbl(varParamValue)
            Case vbDate
              varParamValue = CDate(varParamValue)
            Case Else
              varParamValue = varParamValue
          End Select 'varPrmType 
        prm = varParamValue
      End If ' Not blnPrmSet 
    Next intLoop ' intLoop = 0 To intNumParams - 1
  End If ' Not blnPrmVal 

  If blnOK Then
   '<>
   intQDFType = qdf.Type
    Select Case intQDFType
      Case dbQMakeTable, dbQAppend, dbQDelete
        Set wsp = DBEngine(0)    
        wsp.BeginTrans
          qdf.Execute dbFailOnError
        wsp.CommitTrans
    End Select ' Case intQDFType
  End If ' blnOK

Proc_exit:
  On Error Resume Next
  Set qdf = Nothing
  Set dbs = Nothing
  Set wsp = Nothing
  RunParamQuery = blnOK
  Exit Function
Proc_err:
  Select Case Err.Number
    Case DUPLICATE_KEY_OR_INDEX
      'Ignore this one
    Case Else
      MsgBox "RunParamQuery error #" & Err & "--" & Err.Description
      blnOK = False
  End Select
  Resume Proc_exit
End Function

Reply | Quote

Thanks for all of your efforts in helping me, however, I know when I should quit when I’m ahead.

Unfortunately, from your code sample, I believe this particular requirement (limiting records to those that hasn’t been printed) is way too advanced for my current skillset. I guess the person who requested this will have to bite the bullet and print the entire recordset under that client.

I even tried DoCmd.OpenQuery as you originally recommended and it works just fine with one exception — The Client ID doesn’t get passed on (I believe it is due to the parameter).

Thanks so much for your patience and support.

Reply | Quote

In a similar vein, you could store the date/time of last printing, then use this as a selection criteria to print the next time. However, be forewarned that none of these methods are foolproof. I would manually set the date when I had the printed report in hand, rather than try to automatically set the date from a report event.

Reply | Quote