Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing updated records (Access 2K/SR1)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing updated records (Access 2K/SR1)

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

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Printing updated records (Access 2K/SR1)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing updated records (Access 2K/SR1)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing updated records (Access 2K/SR1)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

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

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing updated records (Access 2K/SR1)

    Thanks for your help. I'll try that.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing updated records (Access 2K/SR1)

    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?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing updated records (Access 2K/SR1)

    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?
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing updated records (Access 2K/SR1)

    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.

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing updated records (Access 2K/SR1)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing updated records (Access 2K/SR1)

    [quote]I decided to use RunSQL code because I didn't want to open Append Query window each time the report closes. [quote]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.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing updated records (Access 2K/SR1)

    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.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing updated records (Access 2K/SR1)

    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?
    Charlotte

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing updated records (Access 2K/SR1)

    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.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Printing updated records (Access 2K/SR1)

    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:
    <pre>Public Function RunParamQuery(ByVal strQryNm As String, _
    ParamArray varParamValues()) As Boolean
    <font color=448800>'Created by Charlotte Foust
    '12/9/99 </font color=448800>
    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
    <font color=448800>' See how many parameters there are to pass</font color=448800>
    If intNumParams = UBound(varParamValues) + 1 Then
    blnPrmVal = True
    Else
    blnOK = False
    End If '<font color=448800>intNumParams = UBound(varParamValues) + 1 </font color=448800>
    If Not blnPrmVal Then
    <font color=448800>' Loop through the parameters and
    ' prompt user for values except for
    ' any passed in the call</font color=448800>
    If intNumParams = 0 Then
    intNumParams = 1
    End If <font color=448800>' intNumParams = 0 </font color=448800>
    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 <font color=448800>' Err = 0 </font color=448800>
    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 <font color=448800>'varPrmType </font color=448800>
    prm = varParamValue
    End If <font color=448800>' Not blnPrmSet </font color=448800>
    Next intLoop <font color=448800>' intLoop = 0 To intNumParams - 1</font color=448800>
    End If <font color=448800>' Not blnPrmVal </font color=448800>

    If blnOK Then
    '<<any special handling goes here>>
    intQDFType = qdf.Type
    Select Case intQDFType
    Case dbQMakeTable, dbQAppend, dbQDelete
    Set wsp = DBEngine(0)
    wsp.BeginTrans
    qdf.Execute dbFailOnError
    wsp.CommitTrans
    End Select <font color=448800>' Case intQDFType</font color=448800>
    End If <font color=448800>' blnOK</font color=448800>

    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</pre>

    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •