Results 1 to 9 of 9
  1. #1
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58

    Input masks causing key violations in Append Query

    I can find no reference to this issue anywhere, so hoping someone can help here.



    I have an append query that updates a (Sales) table that has several foreign key fields. Input masks are specified in some fields on both tables. However, on one FK field in the Sales Table, the append query will not work if input masks are specified in either the table with the PK, the Sales table with the FK, or the source (Stock) table which is not joined to either table (Parent and Child tables). The other FK's with input masks from the source table don't cause any problems.

    I do not understand why not. The mask in question is "AA/AAA;0;" which was created in the Wizard. The fields in question are SHORT TEXT type, field size 50 and the date entered would always be numbers, so, for example, 21/132 (they are invoice numbers)

    I really don't want to store the data without a mask as it's confusing to the eye, and also I just simply can't identify the problem. If I remove the mask on all 3 tables it works fine but if I put it in any table, in any combination, I get a key violation error.

    I would appreciate anyone's thoughts/suggestions.

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Why are you using Input Masks on a FK ?

    PK's & FK's should not be used for anything other than identifying Records in a table. The Users should not see these fields at any time.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Because the primary field in the invoice table is a unique invoice number in the format I specified. So obviously it's essential that the user can see it.

    I can get around this by setting up the primary key as an autonumber and and the actual number in a secondary field (as you are suggesting) but I don't see the necessity of that given that other fields with similar masks don't get key violation errors.

    Thanks anyway.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by cfljanet View Post
    Because the primary field in the invoice table is a unique invoice number in the format I specified. So obviously it's essential that the user can see it.

    I can get around this by setting up the primary key as an autonumber and and the actual number in a secondary field (as you are suggesting) but I don't see the necessity of that given that other fields with similar masks don't get key violation errors.

    Thanks anyway.
    Well this appears to be one such necessity?

    However I have just created a table for that field, 50 chars long (long field for 5 chars?), applied the input mask to the table and was able to add similar records?
    So something else going on.?
    I would still go with an autonumber, every time.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    If you are formatting the Primary Key as a Text Data Type then do think again.

    Primary Keys should be just Autonumber Data Types

    If you want a Unique Number visible to the user then add this additional field to the table.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    I agree that it may be a necessity! However, it is a mystery given that the other FK field that gets updated/appended is also a Text field with an input mask specified, and no violation occurs.

    when I was trying to research what the problem might be, there was no reference at all to the input mask causing an issue - they are pretty simple tables otherwise so I'm stumped. FYI most of my database has user-specified PK with input masks and in 15 years this is the first time I've come across an issue with it.

    Thanks anyway!

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Basically, primary key fields should never be based on meaningful data. Enforcing a format for data is not a reason to do so. I see no reason why the controlled field cannot use a mask while the PK (usually autonumber) field related to that value is the FK value in the related tables. That is how it should be done.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by cfljanet View Post
    I agree that it may be a necessity! However, it is a mystery given that the other FK field that gets updated/appended is also a Text field with an input mask specified, and no violation occurs.

    when I was trying to research what the problem might be, there was no reference at all to the input mask causing an issue - they are pretty simple tables otherwise so I'm stumped. FYI most of my database has user-specified PK with input masks and in 15 years this is the first time I've come across an issue with it.

    Thanks anyway!
    That was my first sentence?
    Then I tested that it works, so the input mask is not the issue?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    when you say the query will not work - what does that man? you get an error? the wrong value or no value? something else?

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

Similar Threads

  1. Replies: 7
    Last Post: 06-21-2017, 10:15 AM
  2. Append Key Violations
    By Chad Access in forum Access
    Replies: 4
    Last Post: 04-27-2017, 03:32 PM
  3. Input Masks
    By jlgray0127 in forum Forms
    Replies: 3
    Last Post: 12-14-2013, 12:50 PM
  4. Input Masks
    By ICTTeacher in forum Database Design
    Replies: 1
    Last Post: 11-07-2013, 10:08 AM
  5. Key Violations in an Append Query
    By petefc in forum Queries
    Replies: 1
    Last Post: 11-30-2012, 01:47 PM

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