value you entered violates the settings
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: value you entered violates the settings

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default value you entered violates the settings

    Hello,

    New poster here so I apologize if this specific question has been asked. I've searched all over this forum and Google and cannot find the solution. I have two databases where the only difference is which computer they're on. The db was built on comp1 and then copied to comp2. All linked tables were moved to onedrive and both db's reference the same files from onedrive.

    The db is meant to pull inventory updates from various vendors download the files, find certain products, do some things and then create an output for our system to upload.

    Everything works fine on comp1. Always has. On comp2 it regularly throws an error "you cannot record changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum). correct the error and try again". If we close the db and reopen it (macro's run on open) it will eventually run without any errors. And the output is all correct.

    It seems to be throwing this error on the first query it runs. This query looks at our inventory levels and imports them into a table in access. Again both db's reference the same source files. Everything between the two db's is identical. Please help this is very annoying.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,393
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: value you entered violates the settings

    Welcome to the Board!

    Is this a properly split Access database? When multiple users are using the same database, it should be "split", and each user is given their own copy of the front-end to use.
    "Splitting" works better than copying it, especially if there are any native data tables in them.
    See: https://support.office.com/en-us/art...3-51b1d73498cc

    Also, do both users have the same version of Access? If not, what version does each have?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Feb 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: value you entered violates the settings

    Quote Originally Posted by Joe4 View Post
    Welcome to the Board!

    Is this a properly split Access database? When multiple users are using the same database, it should be "split", and each user is given their own copy of the front-end to use.
    "Splitting" works better than copying it, especially if there are any native data tables in them.
    See: https://support.office.com/en-us/art...3-51b1d73498cc

    Also, do both users have the same version of Access? If not, what version does each have?
    It was not split. I will look into that. It was just copied. They are never used at the same time. Basically comp1 is a desktop and comp2 is a laptop that is used while traveling. They both have access 2013. I will look into the splitting of it thank you.

  4. #4
    New Member
    Join Date
    Feb 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: value you entered violates the settings

    Quote Originally Posted by Tacti_Steve View Post
    It was not split. I will look into that. It was just copied. They are never used at the same time. Basically comp1 is a desktop and comp2 is a laptop that is used while traveling. They both have access 2013. I will look into the splitting of it thank you.
    I split the database but the new copy does not have any of the linked tables, queries, macros or modules. Do I now have to copy everything from one to the other? How is that going to solve this problem?

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,393
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: value you entered violates the settings

    No, you should not need to copy anything over. If you split the datbase properly, this should already be done. A properly split database witll have two databases this result:
    - Back-end database: this contains all the data tables
    - Front-end database: this contains links to all the data tables, and has all other objects (Queries, Forms, Reports, Macros, Modules)

    Did you use the Database Splitter functionality, or try to do it yourself manually?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    New Member
    Join Date
    Feb 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: value you entered violates the settings

    Quote Originally Posted by Joe4 View Post
    No, you should not need to copy anything over. If you split the datbase properly, this should already be done. A properly split database witll have two databases this result:
    - Back-end database: this contains all the data tables
    - Front-end database: this contains links to all the data tables, and has all other objects (Queries, Forms, Reports, Macros, Modules)

    Did you use the Database Splitter functionality, or try to do it yourself manually?
    I used the splitting wizard.

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,393
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: value you entered violates the settings

    Did you confirm that the back-end only has tables, and then the front-end has everything else, and links to those tables?
    If you try using a copy of the front-end on the computer that was having issues, does it still have issues?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    New Member
    Join Date
    Feb 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: value you entered violates the settings

    Quote Originally Posted by Joe4 View Post
    Did you confirm that the back-end only has tables, and then the front-end has everything else, and links to those tables?
    If you try using a copy of the front-end on the computer that was having issues, does it still have issues?
    Yes the be only has tables. The original one I split it from looks unchanged and still gives the error.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,439
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: value you entered violates the settings

    I don't like to raise alarms unnecessarily but one drive (or in general any remote file systems) is not generally for MSAccess. Access is a file based system so there is no safety provided for lost packets or bad connections. It's a little complicated topic - for instance, certain VPNs and remote desktop apps are fine (they don't actually send data across the wire, just connect you to the machine where the data is stored).

    You might want to decompose this problem so instead of running everything automatically when you open the database, you open the database then run the macro (preferably in steps, with good error checking for each step). The error message itself sounds like a custom error message so you could look into precisely what is happening when this error occurs.

    But overall, you might also want to search for a more robust way of handling for remote operations.

    I don't have any experience with MSAccess and one drive which is why I hesitate to weigh in, but it sounds a little iffy to me. Some similar advice:
    https://answers.microsoft.com/en-us/...e-d80e45ba5a17
    https://stackoverflow.com/questions/...6-on-one-drive

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,393
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: value you entered violates the settings

    I have to defer to xenou on this, as I do not use OneDrive at all. But I have heard of some issues with it and Access also, so I would wager to say that the problem probably has something to do with that.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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