Results 1 to 10 of 10
  1. #1
    mandiahk is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4

    relationship between fields

    I have a table in which one field is related to another. When, in a given record, field 1 is a ten digit number, field 2 must be the same number as field 1. Is there any way to avoid rewriting field 2 manually? I want it to be inserted automatically in field 2.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It seems like a calculation that should be done in a query instead of value that is stored in a field (what is the point of saving a field that is the exact duplicate of another field)?

    Also note, I believe in Access 2010, you can store calculated fields in tables now too. See: http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

  3. #3
    mandiahk is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    Let me explain the problem more: If filed 1 has any value other than a ten digit number, filed 2 has its own (different) value; but when filed 1 has a ten digit number, field 2 must have the exact same number. I want not to be obliged to enter the ten number digit manually because it has a high possibility of committing an error. I'll appreciate if anybody can help me in simple words!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Let's talk specifics. Please describe your table and the fields involved in plain English. The meaning and purpose of each.

    If someone has to type in 10 digits, you can be sure there will be typos.

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think I would try to control all of this through a Form, using the "After Update" event of your first field. If your first entry is 10 characters, you could copy it over to your second field and disable the field so they cannot change it.

  6. #6
    mandiahk is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    Will you give me a step-by-step guide to solve this problem? I have to type dozens of records, for some of which these two fields are the same only! Is there any way that when I enter a ten digit number for field 1, field 2 is filled automatically?
    By the way, do not forget that I am a NOVICE!!!

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  8. #8
    mandiahk is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Posts
    4
    Yes dear orange: My table is about some characteristics of every customer (each customer is a record). Filed 1 is ID number and field 2 is National Personal Code. For customers younger than 25 years old the two numbers are the same (a ten digit number); but for older customers they differ. That's it.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is the issue? Do you have a list of business facts/rules that your database is suppose to support?
    Do you have a data model? Do you have test data? Have you tested your model with the test data?

    What specifically would you like posters/readers to help you with?

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Will you give me a step-by-step guide to solve this problem? I have to type dozens of records, for some of which these two fields are the same only! Is there any way that when I enter a ten digit number for field 1, field 2 is filled automatically?
    By the way, do not forget that I am a NOVICE!!!
    Following up on my suggestion, the key is to control all data entry through Forms. Users should rarely, if ever, be allowed to enter information into Tables directly. By using Forms, you can control data entry and set rules (like you want to do here).

    So, let's say that we have this Entry Form, with fields named Field1 and Field2 (among others). Do do what you want (automatically update Field2 with the value from Field1 if it is exactly 10 characters long), go into the Properties of Field1 on your Form, go to the Event tab, go to the "After Update" property, and choose the Code Builder option. Then in the resulting VB Editor window, you will want to add code that looks like this:
    Code:
    Private Sub Field1_AfterUpdate()
    
        If Len(Me.Field1) = 10 Then
            Me.Field2 = Me.Field1
            Me.Field2.Enabled = False
        Else
            Me.Field2.Enabled = True
        End If
        
    End Sub
    This code should do what you want, if I understand you correctly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 02-20-2013, 12:49 PM
  2. Replies: 2
    Last Post: 04-08-2012, 03:04 PM
  3. Replies: 6
    Last Post: 03-09-2012, 01:07 PM
  4. Replies: 0
    Last Post: 03-06-2012, 11:55 PM
  5. Replies: 5
    Last Post: 05-18-2011, 08:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums