Random Name
By Richard Rost
4 years ago
Build a Random Name Generator in Access VBA
In this Microsoft Access tutorial, I'm going to teach you how to build two types of random name generators. The first will be a jumbler that will put together a random first name with a last name to give you a unique combination. The second will build completely unique, unusual names using random combinations of characters. Great for fantasy novelists, D&D players, or pharmaceutical companies.
Pre-Requisites
Recommended Course
Database File
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, Visual Basic Random Name Generator function, Random Name Picker, Get Random Name, create a random name, randomly pick names, select random names from a list, fantasy names, D&D names, character name generator, drug names
Subscribe to Random Name
Get notifications when this page is updated
Intro
In this video, I will show you how to create two different random name generators in Microsoft Access using VBA. First, we'll learn how to build a jumbling generator that combines random first and last names from your database, and then we'll build a generator that creates entirely new names using random sequences of vowels and consonants—including letter groupings and custom weights for realism. We'll cover creating the necessary tables and queries, using DLookup, working with random numbers, and adding pauses in code for proper randomization, with plenty of tips along the way for building flexible randomization tools.
Transcript
Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today we're going to have some fun. We're going to build a random name generator. We're going to build two different kinds of random name generators. One is going to be a jumbler, like this guy here, where you've got a list of first names and last names, or just people with their first name and last name, and we're going to jumble them up and just put random first name last name pairs together. You'll get things like John Peerth or Deanna Reese Davies from existing names that are in the database already.
The second one we're going to build is going to be just completely random names with random sequences of characters. It's great for fantasy novelists. I've always wanted to write a fantasy novel myself or a science fiction novel. D&D players, if you need a name for that character and can't come up with one, it's always the hardest part of the game. Or pharmaceutical companies. In fact, I got the idea for this video watching TV the other night. I happened to be watching Family Feud, and these names of these drugs came on TV, and it's like, are you suffering from blah blah blah? Try tonessit or assinuate. I literally think they just take a bag of Scrabble tiles, shake it up, and just pull letters out to name their drugs now.
But anyways, I figured this would be a cool exercise to do with a little practice with some VBA, and it's just fun. I like having fun like this from time to time. So let's do some randomizing and some name jumbling and get out those Scrabble tiles and let's make some names.
Before we get started, I have some prerequisites for you. This is going to be based on a bunch of other stuff you're going to have to know. If you haven't watched my intro to VBA video yet and you don't know how to program in VBA, go watch it. It's about 20 minutes long, it's not scary, and it'll teach you everything you need to know. To do this good random name generator stuff, we're going to need some VBA, but none of it's scary. It's real easy stuff, especially if you watch other videos, and these are all free. Go watch them.
Go watch this video on random records: how random numbers are generated. We build a little dice roller, 3D6 (you can tell I'm a D&D player). I also show you how to return a random 25 records, for example. So if you're doing drug testing and you've got to return the top 5 or whatever randomly, go watch this if you've never done randomization before in Access.
Go watch my coin toss video. I just did this one a little while ago. More with randomizing and stuff. You don't have to watch this one, but this one's pretty cool.
This one's a staple. Every good Access developer should have this one under their belt. You should know the DLookup function to pull a value out of a table. We'll use it to get a random record that we pick. The Nz function (null zero) is for if you get a null value returned from that DLookup, but we'll put a zero there or whatever you want.
Go watch my sleep video for putting a sleep timer in your code so you can put a little pause in there while we're generating random numbers. You'll see why in a few minutes, but go watch this. It'll teach you to put a little sleep timer in your code.
Status box. We're going to use a status box to return our random names. These are all displayed in a status box.
Finally, go watch If Then, because if something then do something else. These are all free videos around my website, around my YouTube channel. Go watch them.
So, there's lots of stuff you have to know to learn more stuff. This is a lesson that's going to build on a lot of other lessons. That's how education works. If you don't know any of this stuff, go watch it and then come on back.
Are you ready to make up some D&D character slash drug names? Here we go.
Here I am in my TechHelp free template. This is a free download. You can grab a copy from my website if you want to or just build it yourself. It's no big deal, but I've got a big long list of names for you for the name Jumbler. These are all my Star Trek characters and Rush band members and other cool people.
So we're going to take a name from the last name list and a name from the first name list and jumble them together. I have honestly seen situations where companies wanted to take a big long list of names and jumble them together and come out with just names for whatever reason. This could also work as a team-building exercise. If you've got to build a team, like three people, pick three names from the list, whatever. There are so many uses for this stuff. The examples that I sometimes pick for videos might seem silly, but the techniques that I'm teaching you can be used in lots of other ways.
The first thing we have to do is build a query to pick a random first and last name. I'm going to do two separate queries. Why? Because if you look at the list of names, I've got all first and last names, but you might have a situation where you're missing some data in either table. Let's say we're missing Bashir and we're missing Crusher from last names, and from first names, maybe we're missing Malcolm and we're missing, I don't know, we're missing a Mystery. We have three Mysteries as it is. Let's go to two Mysteries. We don't want to pick that record for a first name. That's why I'm going to build a separate query for last name and a query for first name. Give me the top one random number from each, and then DLookup them, and we can display them that way. Easy enough.
Let's create query design. I'm going to bring in my CustomerT. I'm going to bring in the CustomerID, bring in the first name, and now we have to generate a random number over here. If you remember from our random number video, that goes like this: R is going to be a random number seeded with the CustomerID times the timer, times negative one. I don't know why I did all of that. Go watch the random number video. I'm not going to explain it again. That's what I got the other videos for. That's basically going to put a random number in here seeded by the current timer and the CustomerID, so it's completely unique for each record.
I'm going to put over here "Is Not Null" so that we don't get any null first names. I'll save this guy as CustomerRandomFirstQ (random first name for the customer). If I run it now, every customer gets a random number from 0 to 1. I'm going to sort it ascending (oops, wrong row), sort ascending, and then I'm going to say return one record. You can return five records, 100 records, 5 percent of the records, all the records, or just give me one. One record, not 100. I want one record. So when I run this guy now, boom, I get Jane. Run it again, boom, I got Eddie. Every time you run it, it gives you a random name. That simple. Save it. Close it.
Let's copy it. Copy. Paste. Wait. Let me show you that again. This happens to me all the time. Normally, if you copy and paste something, you'll go Ctrl+C, Ctrl+V, and then you go Ctrl+C, and it gives you that error beep. It's really weird in Access. You have to click on it. I just noticed this started to happen recently. It must be something they changed because it didn't used to behave like this. You've got to go copy, click on it again, and then it pastes. It's weird. It's super weird. Didn't used to have that problem. I've been doing this for a long time.
CustomerRandomLastQ. Now we're going to take last name and do the same thing. Slide last name over here. Make this guy Is Not Null. Delete first name. The random number is going to be the same. Save it. Close it. Now we can run this, and there's Fry. Run this, and we get Troy. See what we're going to do? We're going to run that and that together.
We just happened to get Killy Fry. Look at that. That's the character's actual name, Firefly fans. Let's try it again. That was a fluke. First name: Christopher. Last name: Uhura.
Now, what we're going to do now is use DLookup to pull in those values. Design view. This one will be our Jumbler. We'll call this Jumble. Now I'm hungry for some Jumble. I am. Let's right-click and let's change the name of the button. The Hello World button. We'll call this the JumbleButton. We're going to right-click, build event. Then I'll open up my code builder, and it's really, really big because that's doing some work. Now we've got rid of this. We've got the JumbleButton here. We can get rid of this code here from the old Hello World. Whenever you rename a button, any code that was in it is not going to come with. You have to go find it and delete it. I know it's a pain. We don't need this open in this class either.
For the jumble button, what am I going to do in here? Well, we need two variables. I forgot to put variables on the prerequisite list. So go watch that if you don't know what variables are. We need first name and last name for variables. Basically, it's something to hold these names in. So dim fn as string and ln as string. Guess what those stand for. Financial? No, just kidding.
So fn is going to be equal to DLookup. What are we looking up? FirstName. From where are we getting it? The CustomerRandomFirstQ that we just built. That's all. We don't need any parameters or any criteria there because that query only returns one record. We don't have to DMax and look up stuff and figure it out. No, we're just relying on the query itself to just give me one record.
Now just in case, you could wrap this in an Nz, although we already took care of that in the query too, so we shouldn't need an Nz here. It'll only return non-null values. The only way we'd have a problem is if there are no first names, then you get an error. So, you want to look at all possibilities. Just in case the user deletes all the first names, we're going to wrap that in an Nz, and at least they'll get that back. We'll get an empty string back. You always have to be thinking of ways your users are going to break your database, and they will. They'll find ways to break stuff.
So, that's the case. You're going to say if fn equals blank then maybe Status "No first names. Go add some dummy." Whatever. Exit sub.
Now, at this point, we've got a first name. Let's go get a last name. We're going to do the same thing. Copy. Paste. Oh look at that. Wasn't that easy? And I can even just do this. Copy ln, ln, ln. Last names. And we'll change this to last. So now we've got a last name and a first name, and we put them all together and status them out. Status fn and a space and ln. If you want to get fancy, you can put all kinds of colorful stuff in here and a sleep timer, whatever you want. We'll just keep this one simple.
Ready and go. Oh, hold on. I like to leave my mistakes in my videos. Can you see what the problem is? Pause the video, see if you can figure it out.
This is just a case of me typing too fast and sometimes I get talking to you and I'm not paying attention to my code. The next step to do if you get this problem is, since it says "The expression you entered as a query parameter produced this error: FirstName." In other words, it doesn't know what FirstName is. The clue is you have to know which one of these queries is generating that error. So hit debug and it takes you there. That's the line that caused the problem. It's trying to find FirstName in CustomerRandomLastQ. This query doesn't have a FirstName field. So I goofed. I copied and pasted the code and I forgot to change that guy.
For those of you that complain that I leave the bugs in my code and my videos, too bad because for every one who complains, ten people tell me that they like to see my mistakes. So I leave them in the videos now. I used to cover them up and go back and change it. No. That's good because, trust me, if I make these mistakes, you are too. If you think it's a waste of time, go watch someone else's videos. Sorry. Bye.
Here we go. Ready? Save it once in a while. Give me a Debug Compile just to make sure.
And then go: Christopher Pike, Johnny Stavis, Oman Lashman, oh, I'm getting, what's all this? I'm getting all the actual names. Julian Lifeson, that's weird. Reginald Br- is Julian Lifeson an actual- oh, you know what? That one probably had- I remember, I got rid of Bashir. That one had a blank one.
Alright. Reginald Barkley. These are all Mr. Data. These are all regular names. What's happening? What's going on?
It took me a second to figure this out the first time too. If you think about it, the timer, this code runs so fast that the timer is essentially the same value, so our source seed doesn't have a chance to give it a different timer seed. It literally, to the millisecond, is exactly the same. So we need a short pause in here. That's why you had to watch the sleep video. You need a tiny, tiny pause between these two. One second should be more than enough. Let me try it. sleep 1000.
Oh, I didn't put the sleep code in here today. I did not. Let's go get the sleep code. We need to do Create Module. Go to my website and get the free source code out of the Code Vault. There's SleepSec, and all we really need is this guy here. We don't need SleepSec, we just need Sleep because we can just do it by milliseconds. That's all you need is that line of code right there. Drop it in here. Paste it. That's it. That's all you need. Save it. We'll call this MyGlobalModule, just like I did in the sleep video. Now let's go back. Now let's go back in here. We've got sleep 1000, so it's going to generate this, wait a second, and a second is an eternity for a computer.
So now when I run this, jumble them up: Tammyard, Regina Pike, we're good, Pasha Washburn, it's mixing them up. We could probably get away with less of a- let's put 100. Let's put a one-tenth of a second delay in there. See if we're good. Eddie Worf, Regina Lee. Yep, that's enough of a delay. Like I said, a second is an eternity.
See how that works? Let's make this a little bit bigger so we can see it better. I normally keep the status box small because it's only for occasional messages, but if it's the center of the class, it looks good.
So that's the name jumble. That was easy. That was super simple. Now comes the good part. Now comes the completely random name generator.
What I'm going to do for this is along the same lines as jumbling a first name and a last name, but I'm going to jumble vowels and consonants together. In addition, I'm also going to add vowel sounds and consonant sounds, consonant groupings, and vowel groupings. So, for example, a lot of names will have "oo" or "ee." A lot of names, a lot of consonants will have pairs, like "dr," "mp," or "gh," or diphthongs, those kinds of things.
So we're going to make two tables, one for vowels and vowel sounds and diphthongs, and one for consonants and letter groupings. I've already put these together, so I'm just going to copy them from my other database and show them to you. I'm not going to make you watch me recreate these tables.
Here's the vowels: click and drop. And here's the consonants: click and drop.
Let's take a look. The syllable name vowel table, what I've got in here is there's an ID (auto number, no big deal). Here is the actual vowel itself. You got A, E, I, O, U, and Y. Then you've got the double pairs: AA, EE, II, OO, UU. I feel like I'm going to sing like AEIOU. Yeah, yeah, yeah, yeah.
Then we've got the random pairings of all the vowels together. Pretty much, it's every vowel with every other vowel because these could appear in names. Over here I've got a weight, and the weight is going to be multiplied by the random number so that the vowels with the higher weights will show up more often, because something like AEI is not going to show up as often as an O.
I did the same thing with the consonants. Let me show you the consonants. Here's your consonant list, all the basic letters. I've got a weight over here too. If I sort these by weight, let me sort these largest. The top ones with the highest weight are your real "Wheel of Fortune" letters. They're always in words: R, S, T, L, and N. Those are the most popular consonants. They're the most commonly found in names.
I just generated these weights based on my trial and experience with this. So I built this sample randomizer first just to make sure I can run through it and that it would work well, and these are the values I came up with that look good. You might want to adjust them if you want more Rs in your database. Jack that up to 2.5, whatever.
These ones are a little less common than these, but still more common, and so on down the line. You get down here to the good Scrabble letters: Z, V, X, and Q. I put Y down here because Y as a vowel shows up a lot more often than Y as a consonant. Obviously, if you're a pharmaceutical company, you want to take these guys, Z, V, X, and Q, and jack those values way up. It seems like every drug I see advertised on TV has a Q, a Z, and a V in it. Vyza, Quarks, right? I'm not making this up.
Then I put down here all the random- I just looked at lists, honestly. I went out, googled random lists of baby names, and these are just pairs of letters that you commonly see in names, like GH, ST, PP. (Yeah, I've seen some PPs together, like pepper, P-E-P-P-E-R.) Obviously, every pair is not going to work. Anyway, if you look at even the stuff that I generated for the title slide, not every name is going to be perfect. I actually deleted a couple that looked kind of funny when I was running this. You know, Asulita, Asalatus, that's a cool name. Say LAT, whatever.
But they're not all going to be perfect, but they're good enough if you're looking for something to give you some inspiration for your character name when you're writing that great American novel.
So, these are the tables. The queries are going to work pretty much the same way as the ones we built before. And again, I've already got them built. Same thing. I'm just going to copy them up here. There's the vowel one. Here's the consonant one. Random vowel, random consonant.
You'll see it works the same way: give me the name consonant and look over here (that's a goof, actually, this should- doesn't matter because this value isn't actually read; that's why it worked for me before. It should be the consonant or R; leave it as R, doesn't matter). This value is inconsequential. What you're actually looking up is this.
But notice what I did here. I generated the random number in the same way using the timer and the ID. But I multiplied it by weight, so the letters that I want showing up more often, that have a higher weight, are going to go up the list. I initially tried with like one to ten, but that was too much because the tens far outweighed the ones. That's why I brought it down. So, right now it's a number roughly between one and two. That seems to be a good distribution.
So save that. Again, this number here doesn't matter. That could again just be R.
Now comes the fun part - the code. You've got the tables and the queries. I walked through building them the first time. It's the exact same thing, adding the weight for this one too, just slightly different.
Let's make a jumble - let's make a random name button here. Copy, paste. Here, we use the same status box. Randomizer. We'll call this RandomButton. Right-click, build event.
Now, I am going to make a function that will return the random name, because I want to do it twice to get a first name and a last name, and I don't want to duplicate all the code. So, let's say, give me s equals- let's make a dim s as a string, and s equals get random name. Then we'll just status s to see what it is, whatever we get. We'll put that here.
We're going to go Private (that means only this form can use it), Function (that means it returns a value as opposed to a Subroutine, which does not return a value - a function returns a value). Let's call it GetRandomName. It's going to take in no parameters. (We might change it in a minute.) We're going to return a string, so "As String."
Now, I want to determine a minimum length and a maximum length for each name. Let's say a minimum length is going to be three, and a maximum length would be eight. So I'll make those constants. Const MinLength = 3, and MaxLength = 8.
We're going to add together letters until we reach that value. In fact, we'll randomize the actual length itself. How's that sound? So, let's Dim NameLength As Long. That's going to be the actual length of our name. We're going to decide what it is. It's has to be between three and eight characters, so I basically want to take a random number from zero to five and then add three to it. So we're going to say NameLength = Int(Rnd * (MaxLength - MinLength + 1)) + MinLength.
Before we do that, let's throw in a Randomize, and I like to test stuff every step of the way. So let's status NameLength and just see what we're getting. Right now when I push the button, I'm going to get a bunch of numbers. They should fall between three and eight every time. Let's see. Eight, eight, eight, seven, three. I'm looking for twos or nines; I'm not seeing any. I think we're good.
So that's going to be the length of the name we're generating.
Now we need to know if we're going to start with a vowel or not. Some names start with vowels: Alan, Eddie. So, I'm going to say IsVowel, which we need to make a variable for. (I do this a lot. As a programmer, you don't always line up your variables ahead of time and make a big long list of them. No, you're dimming them as you go along. In fact, it's technically allowed to dim stuff down here if you want to, but it's considered good programming to dim them all at the top of the subroutine or the function.)
So, Dim IsVowel As Boolean. Definitely, definitely dim your variables. Up top here, make sure you have Option Explicit set. I have a whole video about that. It'll help you find compile errors. Very, very important. I turned it off in my database years ago because I thought it was a nuisance, and that was a big mistake as I found out over the next couple of years.
How are we going to determine if it's a vowel or not? How about we flip a coin, 50/50. IsVowel = CoinToss.
Well, what is CoinToss, you ask? Well, that's the whole reason why you had to watch that coin toss video. Let's go grab the coin toss code. Now, I went over to the code vault and grabbed it. You guys are going to have to either join as Gold Members to get into the code vault or do a little typing right there. There's not much. That's the CoinToss function. It just returns a little- it generates a random number and then returns heads or tails, true or false.
That's the benefit of being a member. Gold Members can just go into the code vault and get this stuff.
Anyway. So now we have a CoinToss. So now, let's just again do our checking. Status IsVowel, make sure everything's working. Save it. Come back out here and hit the button: True, False, True, False, True, False. Okay. Everybody good? Everybody happy?
Back in here. Now, it was bugging me by the way, I couldn't figure out why I'm getting that extra line break even though this one works fine. You know why? Because I'm Statusing something as a test here. I threw a Status x in there, and guess what this guy does? It gets the random name, and then it does a status x out here. We're not going to do that anymore. So that's why we're getting that extra line break in there.
So, I know whether to start in a vowel or not. Now, the whole name itself is going to go into another variable. We'll call it n for the whole name. Dim n As String. Then I'm going to look up one character at a time from those queries, the consonant query and the vowel query, so we'll call that c, the character, as string. I think that's all we need as far as variables go.
Now, let's start our loop where n is nothing. So we've got an empty string for the name. We're going to use a While loop. (I don't have a Fast Tip or a TechHelp video for While loops yet. I'm probably going to make one soon, but if you can't wait, I cover them in Access Developer Level 12.)
By the way, if you're wondering why I make you jump around between a lot of different videos, that's because when we're doing these TechHelp and Fast Tips videos, that's just how it is. You have to know this, you have to know that, to do what we're doing in class. But in my full course, my Developer Series, for example, I teach you things so Level 1 follows the level before. Level 12 follows Level 11, follows Level 10. So in every level you're learning a little bit more and they build on each other, and I don't make you jump around like this. In my full course, you watch them one after the other and they're designed to be continuous.
So, While loop. While Len(n) < NameLength (you might want to use <= if you want a chance at 0). So we're going to keep looping and adding letters, and as long as the length of n is less than the NameLength we've already chosen for our name. Now, it might not be exactly that; the name might go over by one, for example, because some of the consonants and vowels are double letters. Some of them, actually, I think are three - I got an RST as a three. I got an EIGH as a vowel sound. So that's just how it is. It could be a little bit more or a little bit less.
When down here.
Now, if IsVowel, then we want to pick a consonant, because we're basically- we're on a vowel, so I want to pick a consonant now. So c = DLookup("NameConsonant", "NameConsonantRandomQ"). You shouldn't need an Nz there because you control this table. Don't let the user delete everything.
Else, c = DLookup("NameVowel", "NameVowelRandomQ"). End If.
Now, what I also want to say at this point is, if it's the first letter of the name, capitalize it. Otherwise, leave it lowercase. All the letters in the table are lowercase. But if it's the first letter, capitalize it. So we're going to say If Len(n) = 0, then c = UCase(c) ' capitalize that guy.
Now tack c onto the name: n = n & c.
Now we have to flip the vowel. So, if it was a vowel and we got a consonant, flip it back. So, IsVowel = Not IsVowel. That flips it. (If it's true, it makes it false. If it's false, it makes it true.)
And again, if you want to change the distribution - if you want more consonants than vowels, that's fine. Roll a random number here. If you want one third vowels, two thirds consonants, do the math there. Pick a random r, find out what it is. If r is greater than 0.67, then make it a vowel. Handle it however you want to. These are your Legos. Put them together however you want to.
Now, like we learned earlier, we need a tiny, tiny sleep timer here: sleep 100 (milliseconds). That should be enough for the timer to move on to the next seed. You don't want too big of one here because that will go before each letter, so it's going to take a little bit. You might even want to drop that to 50, but 100 works fine for me.
Now, when this loop is over, that means the length of n is now greater than the NameLength, so we're one greater than the name length. You might even want to make this less than, because once you hit NameLength, you want to stop. If it's equal to NameLength, drop out.
Now we have to return the function value, so GetRandomName = n. That's it. We should have one random name now.
Are you ready? Here we go. Randomizer. Oh. Sweet. We got Illa. That's a pharmaceutical name! Illa: Are you suffering from irritable vowels and deadly nightmares? Oot. That's a weird one.
Why did you get two capitals? Oh, I know why. That's interesting. Because the vowel that it picked is a double vowel sound, and it just so happened to pick a double vowel and it capitalized that.
We could write code to handle that problem and get around it. I would probably say we could force the first character to be one that's a single character. There's a lot of ways around this, but since this is just a randomizer and you're going to look at it and pick what you want, for now we'll just move on past it.
Assessie. That's the name of the tavern owner in town in my D&D group. Assessie. No, just kidding. Will. A Hazer. This could also be Marvel comic superhero and villain names as well.
Now we need a second one, though. Let's get a second one. So, random name and a space and get random name. Now we're going to get two names. Okay, ready? Here we go. Randomizer. There we go. L.L.L.L.A. Her. Illegit. Legit. Two legit to quit.
What else we got? Enuts Ten. That's a cool name. River Ten.
You could even do a jumble where you pick a real first name and randomize the last name. Oh, that would be a fun project. You can do that for homework. Pull out a random first name and then randomize the last name. That's sweet, huh? Or vice versa.
See, they're not all going to make complete sense, but sometimes you get a really cool one. If you are working for a pharmaceutical company, by the way, and you're planning on using this randomizer for your next drug name, I get 0.01 percent of proceeds as a royalty fee for using my code, or I'll settle for if you sign everybody in your company up as a member.
Now, generally, first names tend to be shorter than last names. What we can do is we could actually send MaxLength up here as a variable instead of making it a constant, and you could send both of these if you want to. Let's send them both in. Let's say Optional MinLength As Long = 3, and Optional MaxLength As Long = 8. Now we can get rid of the constants. If you don't send anything up here, you get three and eight, but you can specify. So you can come down here and say I want a random name, minimum 2, maximum 7 for the first name, and for the last name, let's go 5, 10.
You could have short last names too, like Woo or whatever. Let's see what we get now. Lays Solar. Roozy. You're getting slightly longer last names, they're not all going to be, but that's pretty cool.
There's a double O again. That's going to bother me now. What we're going to do in that case is we're not going to UCase it here inside the loop, because we could pick a character that is a double or even- you could even get the EIGH as a first character. So what we'll do is we'll capitalize it down here. So, n = UCase(Left(n, 1)) & LCase(Right(n, Len(n) - 1)). That should fix that problem. I want the left one, and then the right with all the rest of the characters, Len(n) - 1.
Now let's try it. We shouldn't get our UU problem anymore.
One other thing I did, I don't know if I put it in this table or not. Did I put them in here? No. Are they in here? See, there they are, in the vowel table. I put dashes and apostrophes, like my fiancee's last name is D'Angelo with an apostrophe, D' A. You could have a dash in there for a hyphenated name. They don't show up that often, but you could increase the weight if you want to, and then of course you could put in code. You could run down the string and say, if you see a hyphen or apostrophe, capitalize the next character. You can get really crazy with this stuff, but I just show you how to get started.
If you want to see more, you know what to do. Put a comment down below and say, I want to see how to do that. That's how I make these videos. Sometimes, like today, I just do one because I want to have fun and I'm enjoying it and I like to do this stuff. I saw a commercial and it was a really weird- I can't remember the name of the drug, but I'm like, that would be a cool video. It's a randomly generated name. I think I wrote a random name generator years ago for, like I said, a fantasy role-playing game.
But this has practical business application too, especially if you're a pharmaceutical company.
That's going to be it for today. Did you have some fun? I liked it. I had a good time. This is what I do for a living. I do it for my own Access work, and I love doing it. I hope you enjoy watching me be a goof and do stuff with Access. I hope you learned something and had some fun while learning it. If nothing else, it was a good little practice for your VBA skills.
What's today? Today's Friday. You have a good weekend. I'll see you on Monday.
Quiz
Q1. What are the two types of random name generators demonstrated in the video?
A. One that combines random names from a list and one that picks names alphabetically
B. One that jumbles existing first and last names, and one that generates names from random character sounds
C. One that generates company names and one that uses only existing names
D. One that sorts names by length and one that sorts names by popularity
Q2. Why are separate queries created for random first names and last names in the jumbler?
A. To avoid Access errors when copying names
B. To ensure the database runs faster
C. To avoid selecting null or missing values from either list
D. To combine all names into one field automatically
Q3. In Access, what function is primarily used to retrieve a single random record from a query?
A. DCount
B. DSum
C. DLookup
D. DMin
Q4. Why is Nz() used when retrieving names in VBA?
A. To sort the names alphabetically
B. To ensure no null values cause errors in the code
C. To display the names in uppercase only
D. To split first and last names automatically
Q5. What problem occurs when generating two random records (first and last names) too quickly in VBA?
A. The records might be deleted from the database
B. The same seed is used, leading to repeated or predictable results
C. The query will not run at all
D. Null values are always returned
Q6. What solution is suggested for ensuring different random numbers are used between generating names?
A. Sorting the query results before running
B. Using two separate Access databases
C. Inserting a short sleep or pause (using a sleep timer) between picks
D. Duplicating the query multiple times
Q7. What is the purpose of the 'weight' field in the vowel and consonant tables?
A. To determine the case (upper or lower) of the letter
B. To adjust the text color in the status box
C. To influence the likelihood of a sound being selected in name generation
D. To alphabetize the sounds in the results
Q8. In the random name generator using character sounds, how are the name lengths determined?
A. User manually enters the name length each time
B. A random value between specified minimum and maximum lengths is selected
C. It automatically uses the first five letters found
D. Names are always seven characters long
Q9. What VBA technique is used to alternate between vowels and consonants in the generated names?
A. Modulo division
B. Array sorting
C. Flipping a Boolean variable (IsVowel) each loop iteration
D. Using a string split function
Q10. What is done to ensure the first letter of generated random names is capitalized, even if it is a group of letters?
A. The entire name is converted to uppercase
B. The first letter is capitalized using string manipulation after constructing the name
C. Only vowels are capitalized by default
D. All consonants are removed after generating the name
Q11. Why might a dash or apostrophe be included in the list of possible vowel sounds?
A. To allow for hyphenated or compound names
B. To mark the end of a name
C. To only generate last names
D. To improve query performance
Q12. How can the random name generator code be customized for different needs, such as making first names shorter than last names?
A. By editing the database table names
B. By passing different min and max lengths as parameters to the random name function
C. By removing vowels from the database
D. By hardcoding all the output names
Q13. What is a suggested way to further enhance name generation for uses like fantasy novels or pharmaceuticals?
A. Add more rare letter combinations and adjust their weights
B. Remove all common sounds from the tables
C. Only use the letters A, E, I, O, and U
D. Pick names alphabetically by default
Q14. What role does DLookup play in both the jumbler and random character sound generator?
A. It adds new records to the table
B. It retrieves a random value from a specialized random query
C. It sorts the records
D. It deletes records with nulls
Q15. According to the video, what is an important practice when handing software to users?
A. Assume users will never make mistakes
B. Ignore potential errors during development
C. Anticipate and handle ways that users might break the code
D. Allow only administrators access to features
Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-C; 7-C; 8-B; 9-C; 10-B; 11-A; 12-B; 13-A; 14-B; 15-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Access Learning Zone explores how to build two different random name generators using Microsoft Access and some simple VBA coding. I want to illustrate both practical and playful applications for this type of project, whether you need to jumble names for creative projects, generate inspirational fantasy or sci-fi names, create unique D&D character names, or even devise quirky pharmaceutical brands.
First, I explain the two approaches we'll use. The first method involves mixing and jumbling existing first and last names stored in your database to create new, randomized combinations. For example, if you have a collection of names, you might randomly pair "John" with "Peerth" or "Deanna" with "Davies." This technique simply takes actual names you already have and recombines them for fun or team-building exercises.
The second method generates entirely unique names by combining random sequences of characters, vowels, and consonants, much like the exotic names you might invent for fictional stories or pharmaceuticals. I got the idea for this variation after laughing at some of the drug advertisements on TV, where drug names seem to be assembled by drawing Scrabble tiles from a bag!
Before getting started, I encourage everyone to review several foundational lessons that are essential for this tutorial. If you are new to VBA or need a refresher, my Intro to VBA video is a good starting place. Understanding how to work with random numbers in Access is key, so make sure you have seen my lesson on generating random records – including examples like dice rollers or selecting a random subset of records. My coin toss video further explores randomization concepts. You'll also need to know DLookup, a fundamental function for grabbing data from tables – and its partner Nz, which helps you handle null values. Additional skills like inserting pauses with a sleep timer and using a status box for on-screen feedback will be important. If-Then logic basics round out the prerequisites. All of these are available for free on my site and YouTube channel.
This lesson builds on these earlier teachings, layering them to create something new. That's the heart of practical learning and how real coding proficiency develops.
Let's move to the first generator: the name jumbler. Using a ready-made template, I show how to work with a list of first and last names, many of which are inspired by Star Trek and classic rock band members. The core idea is simple: select a random first name and a random last name from the list and join them. This concept works for any scenario where you need to randomly assign or generate new team combinations, character names, or simply have some programming fun.
To implement this, I show how to construct two separate queries: one to select a random first name and one for a random last name. This separation allows you to handle cases where data might be missing in either list, ensuring robust selection each time. The process uses a random number seeded by each record's ID and the system timer so that every time you run the query, you get a truly random result.
Once you have these queries, you can use DLookup inside your VBA code to fetch the random first and last names and display them together in a status box. If you're following along with code, it's important to check for edge cases, like what happens if the lists are empty, and to handle those gracefully in your application. Also, a brief pause between fetching each name is necessary, because the code executes quickly enough that, without a pause, you might end up with the same random seed for both selections, which reduces randomness. Simply inserting a short sleep timer (for example, 100 milliseconds) solves this problem and ensures better randomization.
Now, moving to the second, more complex generator: creating totally random names. This version does not draw from existing names. Instead, it piecemeals together strings based on constructed groups of vowels and consonants – including ordinary letters, common pairings like "ee" or "th," and weighted values to reflect real-world name frequency. I provide details about creating tables for vowel and consonant groupings, assigning weights to each so that common sounds like "R," "S," and "T" show up more than less familiar combinations. These weighted tables give the generated names a more natural feel, mimicking familiar language patterns.
With the tables in place, I guide you through building queries that use the timer and record ID as seeds for random selection and multiply the output by the assigned weights, increasing the frequency of more common characters.
The VBA code for this randomizer becomes a bit more intricate. I build a function that assembles each name, randomly determining its length (for example, between three and eight letters), and chooses whether to start with a vowel or a consonant by using a simple 50/50 coin toss. The function then alternates between random consonants and vowels, pulling each from the queries above, until the desired name length is reached. Special care is taken to handle capitalization properly, so generated names will look right regardless of the combination of letters that appear first or later in the string.
To add polish, the function allows for flexibility by accepting minimum and maximum lengths as parameters, so you can control, for example, that first names tend to be shorter than last names. Adjusting these parameters makes it simple to tailor generated names for particular needs. If you wish to enhance your randomization, you can further adjust vowels-to-consonant ratios, introduce special characters like hyphens or apostrophes for more exotic names, or refine the weighting system to better mimic real-life language statistics.
Throughout the tutorial, I address common pitfalls, like matching variable names with column labels and properly managing delays between random selections. I also discuss some of my personal teaching philosophy: I leave small errors in the video because debugging is part of programming, and seeing the process of finding and fixing mistakes is valuable for all learners.
If this kind of creative coding sparks your interest – whether for fiction writing, gaming, playful applications, or even business scenarios – this project provides a fantastic foundation and plenty of room for your own customization. You could even make hybrid generators, jumbling real first names with randomized last names or vice versa.
Everything discussed here, including the databases, queries, table structures, and VBA code techniques, is covered step by step in the full video tutorial available on my website at the link below. Live long and prosper, my friends.
Topic List
Building a random name generator in Access VBA
Creating a "jumbler" that mixes real first and last names
Building separate queries for random first and random last names
Filtering queries to exclude null values in name fields
Using DLookup to retrieve a random first and last name
Adding error handling for missing data in name lists
Inserting sleep timers to vary random seeds in VBA
Creating weighted tables for vowels, vowel sounds, and consonants
Designing weighted random selection of vowels and consonants
Using VBA to alternate between random vowels and consonants
Controlling name length with randomization and parameters
Ensuring proper capitalization for generated names
Using Option Explicit and variable declarations in VBA
Incorporating coin toss logic to randomize vowel/consonant starts
Customizing name length using optional VBA function parameters
Generating both first and last names with different lengths
Handling special characters like dashes and apostrophes in names
Troubleshooting randomization issues due to timer seeding
Displaying generated names in a status box on a form
|
