MS Access Changing a database field maximum value

Question

I used an Access DB to record member information for a very small non-profit. It's been used for years and the person who created it has passed away. When we add a new member, the main input panel displays the last member number and also shows what the number for the new member will be. All worked fine until I added member number 1000. Now when I try to add another one, 1001, it shows last member created as being 999 (instead of 1000) and tries to make the member be number 1000 (which actually already exists). When I try to enter that new member (which should be number 1001, but DB tries to make it 1000), I get this error message;

"The changes you requested to the table were not successful because they would create duplicate values in index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again".

I am not a DB person but need to know how do I get on the DB as an admin and how do I change the parameters with the customer (member) number field to allow it to continue past 1000? I don't know if this is in any way related, but while simply poking around I found a parameter labeled, 'Show Values Limit' that has a value of 1000. Could this limit be what's causing it to not go past 1000? an if so, I do I change it? All I want to do is continue with exactly as we have up to know, but be able to grow beyond 1000 members - no other changes are needed. Thank you so much    

 

Answer 1

Hi ,

 

Our forum focus on a software called Microsoft Teams, based on your description, the problem is related to the MS Access, so I moved this thread to access for developers forum.

Thanks for your understanding.

---

Best Regards,
Leon Lu

---

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [e-mail].


Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

Answer 2

Hello Uncle Vic,

How do you add a new member? Did you do it on a form? 

If you add the data to table directly, will you get the error?

Where did you see the 'Show Values Limit' ? 

I would suggest you share a simply database file so we could try to reproduce your issue.

Please remember to remove unrelated contents and privacy information.

For sharing file, you could share the file via Cloud Storage, such as One Drive, and then put link address here. Thanks for understanding.

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

The is obviously code involved in this process and we'd need to see it to be able to advise you on correcting it.

To add a new record, I suppose you press a button?  If so can you open the form in design view and go and look at the command buttons click event code and post it here?

Another option would be to make a copy of the database available to us for review by placing a copy on OneDrive, Dropbox,... (Removing any sensitive or personal information first) and then we can quickly rip through it and correct the problem for you.

Actually, for a nonprofit, you can contact me through either of my websites listed in my signature below and I'll help you out.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net