[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to limt new records added to an Access 2013 table?

Posted on 2014-09-06
2
Medium Priority
?
157 Views
Last Modified: 2014-11-28
How can I create an Access form which will only allow the user to enter just one record into a table.  This form will be emailed to a client, and will need this feature in order to avoid duplication.  Thanks
0
Comment
Question by:Eric Burton
2 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 40307544
Do you mean the TABLE will hold only one record, or do you mean the Table will hold multiple records, but you want your client to one enter one?

If the first, then just check the record count of the table before you allow the form to update:

Sub Form_BeforeUpdate(Cancel as Integer)
  If Me.NewRecord Then
    If DCount("SomeField", "YourTableName") >0 Then
      MsgBox "You cannot add more records"
      Cancel = True
    End If
  End If
End Sub

If the second, then the best way to prevent duplication is to add a Unique Index to the table, and include the Fields that would define a duplicate record. For example, in a Customer table, I might consider the CustomerName, CustomerZip and CustomerPhone to be unique. If so, then I'd create an Index on the table, and include all three of those fields, and set the Unique property to True.
0
 
LVL 53

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 40308714
Have a field of data type Long Integer with a default value of 0 (zero) and set this as the Primary Key.
Don't show the field on the form.
The client may create, update, or delete this single record but cannot add another record.

/gustav
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question