Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: Update Table when report is printed

    I'm sure this is an easy one, but I have had no formal VB Training so I am struggling! I have the following code (something I found on the Internet) which updates TBLLetterHistory with the report name and date when the report is printed. However I also need to record the ID of the record printed. This ID is based on a Combo Box. (the user selects which record to print and then opens up the report they want). I thought the code in orange was right, but that does not work. Any help would be greatly appreciated. Also, on another note, the report name and date that is entered into the table is duplicated!! Don't know why.


    Private Sub Report_Activate()
    Flag = 0
    End Sub

    Private Sub Report_Deactivate()
    Flat = -1
    End Sub

    Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("TBLLetterHistory")

    Flag = Flag + 1
    ' If the current value of Flag = 1, then a hard copy of the
    ' report is printing, so add a new record to the history table.
    If Flag = 1 Then
    rst.AddNew
    rst!ReportName = "RPTGeneralLetter"
    rst!Date = Now
    'rst!LeadID = Forms!FRMLetter!ComboCoName
    rst.Update
    Flag = 0

    End If

    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by lisastar
    I thought the code in orange was right, but that does not work.
    I don't suppose you were intending to tell us how it doesn't work were you?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello lisastar!

    I do it with a Query.
    Look at "DemoRptPrintA2000.mdb".
    Open "Form1", try report (preview), close report,
    Look at "tblReportHistory"
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2005
    Posts
    4

    Thanks MStef-ZG, however.....

    This works great, but what about if I run a query which pulls out records based on their postcode. I then want to send all of them a 'postcode' letter (so many records instead of just one in the first senario I posted).
    I would like to have a history for every record that has the 'postcode' report ran.

    I have this....

    Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

    Dim dbs As DAO.Database, rst As DAO.Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("TBLLetterHistory")

    Flag = Flag + 1
    ' If the current value of Flag = 1, then a hard copy of the
    ' report is printing, so add a new record to the history table.
    If Flag = 1 Then
    rst.AddNew
    rst!ReportName = "RPTLetterByPostcode"
    rst!Date = Now
    rst!LeadID = [QRYLetterByPostcode.LeadID]
    rst.Update
    Flag = 0

    End If
    End Sub

    but only the first record from the query is inserted into the history table.
    Any ideas?
    Much appreciated!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good lord - I think you want to be sure you want to do this before you carry on. You want a record of the value of each and every record each and every time the report is run?

    If so - don't piddle about with recordsets. Write some SQL to append the data to the history table. In fact, you might as well flag it as recently inserted (with a random number integer key for example), and retrieve the data you run the report on from the history table. It might be quicker than running the original query twice (once to populate the histroy table, once to report on). BTW - I would make the history table a heap to speed up writing to it although this might preclude taking your data from there when done.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2005
    Posts
    4

    Confused? I am!

    Yes, I know I am a little confused. I am certainly treading water when it comes to VB and SQL! Sorry for the confusion.

    I want to insert a record into the history table when the postcode letter is printed to get a trail of which letter was sent to which Lead.

    So, to clear things up...

    RPTLetterByPostcode is ran. This is based on QRYLetterByPostcode which selects the records based upon the postcode typed in. So far so good?
    When the report is printed for all the records in the query, I would like TBLLetterHistory to be appended with the LeadID, ReportName and Date for only those records selected by QRYLetterByPostcode.

    Does this make sense?
    I'm not sure what the SQL should be for this..?

    Thanks for your help thus far.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    which lead has an ID?
    which letter has an ID?

    ...looks like a new table

    tblLetters-N-leads
    IDlead
    IDletter

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its getting late so apologies if my undertanding of your problem is wrong

    if all the data you want to save to your history file exists in or is available to the report (via queries) have you considered writing to audit log from the report itself.

    as others have suggested you could do an SQL insert (or if you prefer to a recordset a vba procedure - the former is preferable) based on the same values as the values the report is run from.

  9. #9
    Join Date
    Nov 2005
    Posts
    4

    How do I write an Audit Log?

    Thanks for this, but as I am very unexperienced in the VB area I have no idea how to write an audit log from the report. What code would I need for that?

Posting Permissions

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