Composite Key Example in Microsoft Access

In this Viewer Request video, we'll take a look at a user request to enable a composite primary key made up of a category field plus an incrementing integer field that increments from 1 for each category and re-uses integer values if a record is deleted. We'll program a simple VBA function to determine the next available integer for a chosen category. This function can be called anywhere in your app where you create a new record for the target table.
Important takeaways from this video are:
1. Building a composite primary key in the table builder.
2. Writing VBA logic to determine the next available number for a category.
Don't spend too much time on the form design part since each app will be different. In this case I used one unbound control to enable composite key insertion. However, you may create an entire unbound form, use this procedure in your other procedures, or perform other tasks with the logic in point 1 and 2 above.
Join me on Patreon!
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Want the code from this video?
mackenziemacken...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....

[@seanmackenziedataengineering]

Requirements: We need a composite key for items (eg. egg, rice, bread) that starts at 1 for each category. The composite key logic must reuse integers for records that were deleted. Notes: There are MANY ways to implement this function on forms, and in code that inserts records into your table. You can use this function to insert records as part of other procedures, on forms with events (as shown), or in other form operations. In a situation like this, it tempting to create a solution that will cause many hits on a database, which is inefficient. This solution demonstrates how to use a partial pass of a single recordset using variable comparison along the way, making for a faster solution than doing a db search for each possible ID. For databases with large datasets, another (more complicated) scheme can be used which tracks available ID numbers between 1 and n for each category.

[@davegoodo3603]

That was a great educational video! I've learned a lot from pausing, rewinding and playing again. Made some notes as I went, very re-usable content. Thanks Sean.

[@seanmackenziedataengineering]

Thanks Dave! This one was a request but also a situation I have seen in the wild. Glad it is useful!

[@gfmon1]

Many years Im looking like this. I made one, but its hard coded and lenghty. Thank you for this. This perfectly im looking for.

[@seanmackenziedataengineering]

Glad it worked for you! Cheers

[@hassanrozh6579]

Great, thanks so much.

[@seanmackenziedataengineering]

Glad it was helpful!

[@omegacodedeveloper3592]

Good day sir, please do you have, or can you make a video about the understanding of how access stores attachments, and also how to retrieve all the attachments files at once

[@seanmackenziedataengineering]

Thanks - That’s a good idea for a video! I’ll add it to my list.

[@mirojobu]

Great video, very useful!

[@seanmackenziedataengineering]

Glad to hear it!

[@RockasKane]

Hi Sean. I wonder if you have ever made (or intend to) a video explaining how is it possible to store formulas in a table and use them later wherever we need to. I know there is the eval() function and I've been playing with it but when trying to use field to "fill" the parameter of the stored formulas, it doesn't work as expected. Any plans for somthing like this? Thank you.

[@seanmackenziedataengineering]

A long time ago I wrote a system that used this pretty extensively. Good idea for a video, actually! Let me consider it. Thanks!

[@RockasKane]

@@seanmackenziedataengineering Thank you, Sean. I will be watching ☺️

[@gerfer6261]

🛗🔀

[@seanmackenziedataengineering]

Thanks!