Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Latest Issue (2003)

    I have a simple form, a query, and report. I want the report to show the latest issue. So if there has been an amendment to the form/tables, the issue level goes up by 1. I may be making this more complicated than neccessary, but I want to eliminate human error in having to remember to alter the report.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Latest Issue (2003)

    Do you mean that you want to update some kind of version number automatically when you modify the design of the table or form? That is neither feasible nor desirable in my opinion. Or do you mean that you want to update a number if data are edited?

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Latest Issue (2003)

    Sorry, yes it's a number on the report I need to change if there is any change to the data in the table, not the actual design

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Latest Issue (2003)

    Access does not have any triggers at the table or query level, but since end users should never edit data directly in a table or query, that shouldn't be a problem. You can use code in the data entry form to maintain the issue level.

    Do you want a single issue level for the entire table, or an individual issue level for each record in the table?

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Latest Issue (2003)

    A single issue level, so if any record in the table is amended, the issue level goes up. Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Latest Issue (2003)

    Create a separate table tblIssueLevel, with two fields:
    TableName - text, size sufficient for the names of your tables; this is the primary key.
    IssueLevel - number, Long Integer.

    Add a record for each table you want to monitor this way; enter the table name and set the IssueLevel field to 0.

    Add code to the After Update event of your form (you need a reference to the Microsoft DAO 3.6 Object Library in Tools | References...)
    <code>
    Private Sub Form_AfterUpdate()
    Dim strSQL As String
    strSQL = "UPDATE tblIssueLevel SET IssueLevel=IssueLevel+1 WHERE TableName='tblData'"
    CurrentDb.Execute strSQL, dbFailOnError
    End Sub
    </code>
    Replace tblData with the name of the table behind your form. On your report, place a text box with control source
    <code>
    =DLookup("IssueLevel","tblIssueLevel","TableName=' tblData'")
    </code>
    Replace tblData with the name of the table behind your report.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Latest Issue (2003)

    Thanks Hans. Just got round to trying it and it's just what I wanted. If I add date() to the tblIssueLevel, will that give me the date it was updated, or will it just give the date the report is printed.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Latest Issue (2003)

    If you add a date/time field IssueDate to tblIssueLevel, you can expand the code to
    <code>
    Private Sub Form_AfterUpdate()
    Dim strSQL As String
    strSQL = "UPDATE tblIssueLevel SET IssueLevel=IssueLevel+1, IssueDate=Date() WHERE TableName='tblData'"
    CurrentDb.Execute strSQL, dbFailOnError
    End Sub
    </code>
    To display the IssueDate on the report, use a text box with control source
    <code>
    =DLookup("IssueDate","tblIssueLevel","TableName='t blData'")
    </code>
    This will be the date of the last modification to the data, not the print date.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Latest Issue (2003)

    That's great Hans. Thanks very much

Posting Permissions

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