Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Can I automate fields in table 1 based on data in table 2?

    Sorry to jump right in like this, but I've been browsing for half the morning now, and I'm not sure if its just me but I'm still really stuck with this issue.

    I'm trying to make it so that a field in one table will automatically update itself when I input data in a different table.

    As an example: (its a pretty impractical one, I know)

    I have a table relating to Clients, and another relating to Next of Kin.
    The tables are related through a shared unique client ID number.
    In the Next of Kin info, there is a field for "relation to client".
    In the Clients info, there is a field for "Marital Status".
    I want this field to automatically update to "married" when I check the box stating that the Next of Kin is the spouse of the client.

    Can I do this using the IIF function? Or a macro? Or do I have to use something else? I've tried macros and the IIF function but I cant get them working (although its possible I messed that up)and I'm running out of ideas.

    Thanks in advance for your time and help.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes you can do it several ways.

    In the AfterUpdate property for the the Next of kin relationship field:

    Code:
    If Me.NextOfKinRelationshipField = "Spouse" Then
      Forms!ClientFormName!MaritalStatus = "Married"
    End If
    Of course you are going to have to substitute out the names for ones you have... and do data type matching as well, but you should get the idea.

    And both forms would need to be open at the time of course
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK it may be your example is flaky, but I remain to be convinced that you can identify if someone is married or not based on their being assigned as someones next of kin.
    this is a good argument for not violating the normalisation principles inherent with 3NF. store the information once in one place, dont' try and keep multiple versions of the same information.

    its quite possible to have one person who is next of kin to multiple people, even within the same family, I suspect you need a many to many relationship in an intersection table with foreign keys to both the original customer / client table, and the next of kin table, with the type of realtionship in that intersection table, and possibly a foreign key to realtionship type if you are going to be picky.

    It is however relatively trivial in SQL to set a value based on a value elsewhere, but as its a derived value its not neccesarily a smart idea. you can create the value in a query, or int he presentation layer, depending on the data content its nearly always smarter in the query as you do the work once and use in subsequent reports / forms / screens etc.....

  4. #4
    Join Date
    Jan 2008
    Posts
    2
    StarTrekker - Thanks for the reply. I tried that code but I'm getting no luck, which leads me to beleive I made an elementary mistake somewhere, so I need to ask this (forgive me if its a foolish question) but what did you mean by data type matching?

    healdem - I had thought that maybe a query was the best way, but I was trying to avoid that for lots of reasons, mainly that I would rather it update "in real time" so to speak, so it lessens the number of errors, since I'll see it update each record as it happens. If I make a query to update the fields, but i make a slight error in the query, then I could mess up hundreds of records rather than just one....or am i talking crazy?

    I think my example was bad though, because I'm not identifying marital status based on being assigned as next of kin. Let me elaborate a bit...

    CLIENTS
    client number*
    name
    d.o.b.
    marital status
    address

    NEXT OF KIN
    (client number)*
    name
    d.o.b
    relation to client

    imagine those are the tables, with records linked via the client number.
    If in next of kin, they are listed as "spouse" in the relation to client field, then the marital status field in the client table will update automatically to "married".

    Thats what I was trying to say. I know the example isnt a particularly practical one but its the simplest one that came to mind at the time.

    Also, I'm not looking to store the same data in multiple locations, because even if two people list the same person as next of kin, they might not be the same relation to that person. I was just hoping I could set a property of a value (AfterUpdate, I assume) to make it automatically alter another value based on itself, almost like running an automatic update query on that specific record as I update a certain field, if that makes more sense?

    I managed to do it when the fields are in the same table, (eg "under 18" will check itself when the date of birth I enter for a person is before today's date 18 years ago) but when they're in different tables, I'm stuck.

    Sorry if this doesnt make sense again but I really dont know how else to explain it.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe you know this already, but anyway:

    there are no triggers in Access. none. zilch.
    data in tables is just there. changing it makes it there-but-different. the change in the table itself cannot cause a change in another table (cf SQLserver and triggers).

    your code can do whatever it likes, but you have to write the code.

    depending on trigger-like behaviour whilst using an engine that doesn't support triggers is a minefield: you are obliged to grab control of every possible route to editing the data and make sure your ersatz-trigger code fires. it is not easy.

    have you considered moving the data to an engine that suports triggers (i believe that freebie SQLserver-Express does, but i have not played with Eypress).

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2007
    Posts
    70
    Hi,

    Quote Originally Posted by StarTrekker
    Yes you can do it several ways.

    In the AfterUpdate property for the the Next of kin relationship field:

    Code:
    If Me.NextOfKinRelationshipField = "Spouse" Then
      Forms!ClientFormName!MaritalStatus = "Married"
    End If
    Could you adapt this code to fill in three different fields from the original?

    i.e. [code]IF Me.Field1 = "A" Then
    Forms!Form1!Field2 = "X"
    Forms!Form1!Field3 = "y"
    Forms!Form1!Field4 = "z"

    And could this be done multipule times in the same code? So if Field1 was say "B", Fields 2, 3 and 4 could be set to other figures? And so on. Is there a limit to the number of options in Field1?

    Cheers,

    OB1

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, yes and no.

    But you might also like to consider a Select Case statement rather than a big If structure.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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