Results 1 to 7 of 7

Thread: Need Help!

  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: Need Help!

    I am currently creating a database that tracks tenants and what property they live in. Each tenant has a unique referance as does each property.

    I would like to know how to track if a tenant leaves a property and transfers to another one how i would track this without losing the information from the previous property the tenant lived in. This way I could have a running list of what property the tenant has lived in and until what dates.

    I have a table for tenants and a table for properties. In the tenants table the fields are: Tenant Ref (primary key), tenant name, status, booking date, booking ref, scheme, company, transfer date, transfer ref, cancellantion date, cancellantion ref. In the property table is: Property ref (primary key), property address, county, postcode, property type, bedrooms, floor, start date, end date, rent, supplier ref, Landlord ref, tenant ref.

    Any help would be much appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need a History table, a Log table, a Transaction table, a Diary table, a Movement table..., name it whatever you want.

    When a modification on the data is performed , you first write the before-update data for the modified record(s), the kind of modification and possibly the date of modification and ID of the person who performs the modification. The amount and the nature of the information you store in the Transaction table (my favourite choice of name) depends on what you intend to do with such information later on.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    When a modification on the data is performed , you first write the before-update data for the modified record(s), the kind of modification and possibly the date of modification and ID of the person who performs the modification. The amount and the nature of the information you store in the Transaction table (my favourite choice of name) depends on what you intend to do with such information later on.
    I'm sorry if I seem quite thick here, but can you explain that again in Laymans terms. I understand that I need to create a Transaction table but what is contained within here, and how will it link to the other two tables?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thanks to you I learned a new word in English: layman (English is not my first language)

    Back to your problem now. As I wrote, it all depends on what you want to find in the Transaction table and what you want to do with it. There are several approaches:

    1. You copy the contents on the row being modified to the Transaction table before modifying it. You can add several additional pieces of information such as: the date/time of the modification, the id of the user performing the modification, etc. This can be rather easily done by calling an INSERT query at the beginning of the modification process. This allows the implementation of some kind of rollback mechanism (i.e. you can "undo" a modification later on).

    2. You only insert some pieces of information in the Transaction table: (e.g. Primary key of the row being modified, Old data-New data, ...) here too it's up to you to decide what this table must contain. The rollbak mechanism would be harder to implement here and this solution is more adapted to keeping a history of the data but nothing more ("You @#^}&_! You changed the amount due in the invoice #15.003 three days ago without telling me.")

    Unfortunately, and contrarily to a more complex database engine such as MS SQL Server, Oracle, etc., Access does not have Triggers that would automate the process. In any case you'll have to rely on VBA for performing the necessary steps.
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    4
    I'm glad I could help with your English

    Thank you for that, I will try it later and let you know.

  6. #6
    Join Date
    Sep 2011
    Posts
    4
    How do I capture the ID of the user performing the modification?

    I have looked around at various ways in the forum but most suggest using User Level Security which has been in eliminated in my version of Access (2007). There has been a suggestion of capturing the windows log-in ID, but i think i may have to create a module to do this and have never done this before so wouldn't even know where to start.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's what you need. Paste this in a new module:
    Code:
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Public Function GetNTUser() As String
    
    ' Retrieve the login name of the current user.
    '
        Dim strUserName As String
        Dim lngUserNameSize As Long
        
        strUserName = String$(255, 0)
        lngUserNameSize = Len(strUserName)
        GetUserName strUserName, lngUserNameSize
        GetNTUser = Left$(strUserName, lngUserNameSize - 1)
        
    End Function
    Have a nice day!

Posting Permissions

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