Prevent Duplicate Products or Services in an Order in Microsoft Access with a Composite Key
HTML-код
- Опубликовано: 30 июл 2024
- In this video, I'm going to show you how to prevent your users from adding a duplicate product or service to a customer's order in your Microsoft Access database, using something called a Composite Key.
CORRECTION
In the video I mention that you cannot index Long Text fields. This used to be the case in older versions of Access. As of 2022, you can index Short Text, Long Text, Number, Date/Time, AutoNumber, Yes/No, and Hyperlink fields. You cannot index any of the field types that you shouldn't be using anyways: OLE Object, Calculated, Attachment. In older versions of Access you didn't used to be able to index Memo fields, which are now called Long Text fields, or Hyperlink fields. Honestly, I almost never index Long Text as that's going to be a real performance hit on your database.
Ramiro from Houston, Texas (a Platinum Member) asks: I run an auto detail shop. My database is pretty simple. We have about ten services that we provide. The problem is that sometimes one of my guys accidentally adds the same service twice, which should never happen. Is there a way to prevent this in the database?
Silver Members and up get access to an Extended Cut of this video. Members will learn a different method to prevent duplicates. We'll use a little bit of VBA and the DCount function to check if the product is already on the order. If so, we'll ask the user if they want to add it or not. This also allows us to use a custom warning prompt instead of the default system message.
MEMBERS VIDEO:
• Prevent Duplicates MEM...
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
599cd.com/PreventDuplicates
LINKS:
Invoicing: 599cd.com/Invoicing
Relationships: 599cd.com/Relationships
Relational Combo: 599cd.com/Relational
SUGGESTED COURSES:
Access Expert 8: 599cd.com/ACX8
Access Expert 23: 599cd.com/ACX23
Access Developer 27: 599cd.com/ACX27
Access Developer 36: 599cd.com/ACX36
OTHER COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, composite key, prevent duplicates, prevent duplicate entries, How do you prevent duplicate entry in Access, Preventing Duplicates from being entered, Preventing Duplicate Records Across Multiple Fields, How to Prevent a Duplicated Data Entry, vba code to prevent duplicate entries in access, sql how to prevent duplicate entries, ms-access check for duplicates before update, how to avoid duplication of data in database
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
In the orderdetail design view, you can also just select multiple fields and select primary key from the ribbon. It then adds the new index value for you.
Great video again steve
Thanks again!
How do you run no duplicates in a query criteria?
What if someone wants to use composite key and traps errors on Form OnError event? I believe that this option might no be good for complex applications with multiple tables. What is your opinion about that?
Sure... if it works for you, gopher it. :)
It's funny that you mention this because it's actually on my list for an upcoming video. :)
@@599CD So i guessed one of your upcoming videos. Look, to be honest, from my point of view i believe that this stands for simple tasks , for example if i have one field with unique values in my table, then i can manipulate the onError event in form. But if for example, my table has 2 or 3 or 4 fields that have to be unique then if the OnError event rises, i have to perform several Dlookups in the form's OnError event sub to see which of the unique value fields have the duplicate values so user gets an appropriate prompt . In this case this might causes delays in response (index maintenance plus dlookups if error) in case of large amount of data especially in a multi user environment
I am looking forward for this video. Thanks again!!!
Hi Mr Richard Rost
I'm looking for a method to sum the quantity of duplicate items instead of picking new record in my Order form .
I will be great full if show me.
thanks .
That's on my list
Sir can you please make a video on access (query) that between 80 marks to 90 marks. Please I need your help.
I have no idea what that means.
Sir I would like to ask if it is possible in a Query to hide the duplicate values
Aggregate query: 599cd.com/aggregate
Sir, I am getting issue in Invoice Number field... I when I am using AutoNumber field format... When if I cancel after generates invoice number (using AutoNumber) then that generated invoice number skipped and there getting gap in INV Number.... So what field/ function should be used?
Sir, nobody on RUclips can give solution on it. I hope definitely you will solve it.
So Sir, please make a example video on this issue on priority please.. Thanks!
AutoNumbers are not for YOU. They're for Access to make relationships. It doesn't matter if there is a gap in them. If you want sequential numbers, DON'T use AutoNumbers. 599cd.com/Counter
How do use codes to prevent Dublicates
Huh?
10
out of....??? :)