Coin Toss
By Richard Rost
4 years ago
Simulated Coin Flips in Microsoft Access VBA
In this Microsoft Access tutorial, I will teach you how to generate a random coin toss. We'll write a function in VBA to flip a coin. Then, we'll see how the heads/tails percentage changes the more flips you make.
Pre-Requisites
Recommended Course
Links
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, coin toss, random numbers, Multiple Coin Toss, VBA Coin Toss, Simulate a coin toss, Simulating Coin, simulated coin flip, Multi Coin Toss example, multiple flips
Subscribe to Coin Toss
Get notifications when this page is updated
Intro In this video, we will learn how to create a coin toss simulator in Microsoft Access using VBA. I will show you how to build a function to simulate heads or tails, run multiple tosses at once, and display the results in your database form, including tracking heads, tails, and win percentages. We'll cover key VBA concepts like the If function, For Next loops, variables, and handling user actions with controls like Abort. Along the way, you'll see how probability plays out as you increase the number of tosses.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to have some fun. I'm going to show you how to generate a coin toss, or a thousand coin tosses, or a hundred thousand coin tosses. We'll see how the probabilities change the more coin tosses you flip.
This is something that came up in another project that I'm working on and another video that I'm putting together for you. I had the need to flip a coin. I'm like, well, alright, we've already done a video on random numbers. Let's just tweak that a little bit and make a coin toss function. You can see it right there. It's nice and simple.
Let's put it in a database and then we'll do some looping and have some fun. This video is going to involve some VBA, quite a lot of it in fact. If you've never done any VBA before, go watch this, and I have a bunch of other videos for you to watch too. You'll find a list of them all down below in the prerequisites. We cover the If function, If Then statements, variables, For Next loops, all kinds of stuff.
If you're confused as to how any of this stuff works, go watch those videos first and then come on back to this one. Also, go watch my Random Records video. This teaches you how random numbers are generated. That's a big part of today's video, so go watch this first too. These are all free. They're on my website and on my YouTube channel. Go watch those first and then come on back.
I also want to warn you that even though it's a Fast Tip video, it's not exactly fast. It's over 20 minutes long. The reason why is when I call it a Fast Tip, I try to get to the point of the video quickly. I'll teach you how to do the coin toss function within the first couple of minutes. After that, sometimes I just have fun. I enjoy doing this, so I go off on a bunch of tangents. But the Fast Tip will be done first, and then we'll get into some other stuff. So I'll just have fun.
We're here to have fun and learn, so here we go.
So, if anybody is complaining that the Fast Tip is 20 minutes long, stop it. Short videos like TikTok – I've tried. I just can't. I can't condense my stuff down to three minutes. It's impossible. Like these YouTube shorts, I need at least 20 to do something cool.
Here I am in my TechHelp free template. You can download a copy of this off my website. If you watched the Contacts video, you probably already have a copy. The first thing we're going to do is make a coin toss function, and I'm going to put that in a global module down here so that everybody in the database can use it.
We're going to go to Create and then Module, not Class Module, Module. That'll bring up this guy. This is the VBA window. If you watched the VBA video, you know what this guy is.
We're going to make it public; that means everybody can use it. Every other form and report, whatever in your database, can use it. It's going to be a function, which means it returns a value, as opposed to a subroutine, which does not return a value. It just does stuff.
We're going to call it CoinToss. It's not going to take any parameters in, and it's going to return a Boolean. It will return True if it's heads and False if it's tails. Since we know how to generate random numbers, we know the first thing we have to do is Randomize. I'm going to randomize this guy every time the function is called. Do you have to do that? No, not really. You could randomize the database once when the form opens. Randomizing takes just a split second, so you might as well just do it every time.
Now, CoinToss, the value of that function, is going to be set equal to—we're going to use the If function, and we're going to say Random. Now, what does Random return? Random returns a random number from zero to one. Remember that? So, basically, if it's point five or greater, it's heads; if it's less than point five, it's tails. So, if Random is greater than or equal to point five, then give me a one or a zero.
I'm going to include point five in the later half because technically the Random function can be from zero—greater than or equal to zero, but less than one. So Random will never return one. That way, we're going zero up to but not including point five, and then point five up to but not including one. So that's an even number of values there.
That's it. That's all you need. If that random number comes back big, it's a head. If it comes back little, it's tails.
Save that. If you're planning on putting more random stuff in this module, you can call it the Random Module. I'm just going to call it MyGlobalModule. I usually have a bunch of little functions that I put together in the course of a database, and I throw those in a global module. No sense in having 50 modules here.
Let's go back to the database now, Design View here. I'm going to make use of my Hello World button. We're going to change this to say, just go, whatever—you can change a name and all that stuff if you want to. Not that big of a deal.
Right-click Build Event, and instead of status-ing Hello World, what we're going to do is we're going to come in here. I'm going to say, give me a variable. Let's call it isHead, and that'll be a Boolean value. Remember, Boolean is a True/False value. Since CoinToss is returning a True or a False, that's what we're going to store in here.
So, isHead equals CoinToss. Technically, you should go CoinToss like that, with the open/closed parentheses after it, since it's a function. Access will usually let you get away without those, but if you put them in a query sometimes it doesn't work. Like Date, it'll just think it's a field.
Let's just status this guy real quick. So status, and I'll just say X is whatever isHead. We should see a True or False value here for every coin toss.
Let's go back over here, close it, open it. Click. True, False, False, True. Every time I do that, it's generating a coin flip. Easy enough.
Now, I don't want just one at a time. I want 500 coin flips. Let's make it so we can do a bunch of them.
Take this guy. How many flips do we want? Oops, nope, capital L. I do that all the time. Does anybody else do that when you're typing? I hold down the Shift key just a slight second too long and I get that capital L. I've got to get used to it. I used to use maybe my left thumb for that Shift key. I've been doing it since I was a kid.
Number of flips, this—now this is a date. Come on over here. I'm going to call this guy Flips and let's get rid of that equals date control source. We're going to type a value in here. Get rid of the format, which is Short Date. We just want a standard number.
Go to Data. We'll make the default value, let's say, 100 flips. Save that.
Now I want text boxes to store the results, and then maybe one more that shows the winning percentage—and you win if you get a heads. So let's go copy, paste, paste, paste. Give me a couple more boxes and you actually probably got rid of the default value before you pasted. That's okay.
Select all three of these and we'll take that default value and get it out of there—zero. This guy will be Heads and we'll name the box Heads as well. Over here, Heads. This guy will be Tails and this will be Tail. This will be Winning Percentage. I'm going to name this guy WinPC for Win Percent.
Now, the control source I can generate automatically. I'm going to say equals [Heads] divided by parentheses, then I'm going to name this guy [Heads] plus [Tails]. Since that's calculated, we're going to gray that out so the user knows they can't change it. Just like that.
Save this, close it, open it back up again, and we've got pound num in there. Why's that? Lost division by zero. Not a big deal. You can get rid of that and start it by just not setting these to zero if you want to. You can use an If function to say, okay, if [Heads] plus [Tails] is zero, then don't do that, whichever one you want.
Let's just get rid of the default value because when you do that, you can't do null divisions. Basically, you get no error message unless you literally put something in here. There you go. Anything plus, minus, times, divide null is null. That's just how null math works. So as long as you don't have [Heads] plus [Tails] as zero, you're okay.
Let's go into the button. Actually, let me slide this out. This looks a little messy. Let me do a little bit of this and a little bit of that. Let's better that stuff on there a little bit better.
Go into the button, right-click Build Event. Now, I don't want just one coin toss, I want a bunch of coin tosses. I want [Flips] numbers of coin tosses. I need a counter variable so I can loop from one to [Flips]. If I tap in 10, I want one to 10.
So, comma X as Long. That's my counter variable. Let's start off by saying StatusBox equals "Begin." Just put the word "Begin" in there. I'm not actually using the Status function because I don't want it to add to the box. I'm just going to reset the box. In fact, I'm probably going to just use it this way throughout the whole video. Now with that box, you just update it with the current status.
Now, let's set our variables Heads equals zero, Tails equals zero. We have none right now. We're starting.
Now a For loop. I'm going to say For X = 1 To [Flips]. Next. In here, I'm going to loop [Flips] number of times. If [Flips] is 10, it's going to loop 10 times.
In here, I'll put the isHead equals CoinToss. Then I'll say If isHead Then Heads = Heads + 1 Else Tails = Tails + 1. That'll update our boxes.
If you want to, you can status it. Let's say StatusBox = X, which is our X counting. Then we're all done, we'll say StatusBox. Now, we're not using the actual Status function; we're just changing the box. If you run this now, you're not going to see it go. Run it, group just says "Done."
Now, if you've got a really long loop, then it'll take some more time. If I go to a thousand, and then 10,000, takes a second. 100,000 takes even longer. Looks like it's frozen, but it's still going.
The thing is, the Status function that I wrote has a DoEvents in it. If you take a look at the Status function, which is right up here, there's a DoEvents in there. If you use the Status function, it'll keep updating the display, but I'm not doing that. I'm just changing the StatusBox. So if I just say here Status, and then that, when I run now, you can see it updates the box. But it's going to take forever because every time it updates the box, it's nice and slow.
So, we're going to stop this. What you can do, if you do want to see a status, is you can say something like, if there's a lot of flips—if [Flips] is greater than 10,000, then we'll do something like this in here. We'll say, If X Mod 10,000 = 0 Then StatusBox = (X / [Flips]) * 100, and then we'll put a percentage sign after it. So basically every 10,000, if we put a value in greater than 10,000, every 10,000, we'll see a percentage.
Now, in here, since we're setting StatusBox equal to this, we have to make sure we put in here our DoEvents, because we're not actually using the Status function. We're going to get into enough funky stuff—this is just having fun today. This is fun for me.
Now if I run it, there we go. See? And it runs a lot faster too. Did you notice that? Before, it took forever because the slowest thing you can ever do with programming is screen refreshes—they're slow. I think they're even slower than hard disk writes. I think hard disk writes are faster than screen refreshes. Refreshing this whole screen takes a lot of processing power. But there, it's relatively fast. If we jack this number up even more, you'll see it runs like everyone now.
You might want to put an abort checkbox on here somewhere. I like to use those because if you're using it as this, they've got no way to stop this. So, we're going to come in here and stop it ourselves. We can put an abort checkbox on here too.
Design view, and since we're only using this as a single-line StatusBox now, we can do that. Drop an abort box down underneath here. Abort. We'll color that white. Let's align that left. Let's name this Abort. We're going to start it off so it's not visible.
Save it. Now, in our code, when this thing runs, we're going to say Abort.Visible = True. We'll set its value Abort = False. So the checkbox itself is false, but its visible property is true. Now you can see it.
If at any time the user hits Abort, we want to get out of dodge. That'll only happen during a DoEvents, because if a DoEvents doesn't take place, then what the user clicks on is meaningless. DoEvents is crucial here. So, I'm going to say right here, If Abort Then Exit For. That says drop out of the For loop.
Once we do drop out of the For loop down here, we can say If Abort Then StatusBox = "Aborted" and you can color red and do whatever other stuff you want. Else, StatusBox = "Done" or "Completed successfully," whatever. Then we'll say Abort.Visible = False.
The other thing is that we do that all the time. We're running on for the party—just one of those things. Let's do one more. They're doing some cool stuff here. This is the kind of stuff, by the way, that I teach in my developer classes. We have fun like this—we'll have a topic and we'll just go to town. I'll do the topic, and then I'll usually add a bunch of little embellishments to it, because I can get carried away sometimes. If I make a mistake, chances are you'll make the mistake too.
Now, you see what I did here? I said StatusBox = "Aborted." StatusBox is the name of a control, Status is the name of the function. If I just say status aborted, it will work, but if I mean call StatusBox directly and send something to it, I have to say StatusBox = "Aborted." I did it correct down here.
Save it. Every now and then give it a good Debug > Compile. Make sure you have no syntax errors in here.
Now we should be good to go. Ready, go. Now, the abort thing just comes and goes so fast you don't even get a chance to see it. Jack that up to 100,000 and go. There's my abort. That's going, and it's done. Let's do it again. Go. Good.
I want to change this because I need to multiply that by 100. Let's come in here. Oh, I did multiply [by] 100. What did I forget to do? I think I forgot to format it correctly. Let's see. Format. We're going to set the format: 0.0 like that. That should fix it. Ready, go. Good. Looks good.
All right, let's put a million in here. If you want to be able to see the commas in here, that's another thing you could do. You can come in here and make this format: #,##0. The number signs are optional—they'll be there if you need them. The comma says, give me a thousand separator. The zero says you have to have a number there.
Save that. Close it. Open it. There's my 100. If I type in 100,000, you get that comma there. You see, I type in a million, you get that. Remember, Format is different from Input Mask. I've got separate videos on those—look them up, they're on my website.
Let's go. There we go. There's a little guy—he's running. If I hit Abort now, pink. Oh, you can't hide a control that has focus. That's another good error message. I always forget about this one. I'm glad these things are coming up and we're just goofing around. I didn't really run through most of this beforehand.
So like I said, it's good. If I run into it, you're going to run into it. What does that mean? Well, Debug. I tried to hide a control that has focus. In other words, I clicked on the Abort checkbox while it was running. So that means my focus—where the cursor is—is sitting on that control. That means I can't hide it. You have to move somewhere else before you can hide that control. Very good error.
So let's come here before we do that. Let's move somewhere else. Where do you want to move? I don't know. I'm going to move back to the Go button, which I believe is HelloWorldButton. So that's the name of the control. Yeah, I know, we should have named it something different. That's OK. So if you aborted—well, either way, it doesn't matter—we'll just say right down here HelloWorldButton.SetFocus. There's also another one called DoCmd.GoToControl, but I like SetFocus better. Save it.
Here we go. Ready? Click and abort. It goes back up here. Look at that—very nice.
One thing I did want to mention is that take a look at how the percentages are all over the place the fewer flips you do. If I do 10 flips, 60. 60 again. 70. See, huge margins vary. You can get seven heads, three tails. Eight heads, two tails. There's 50/50. Seven and three, 70. One and nine. It's possible—it happens.
The higher this number gets, the closer this always is to 50. That's just the rule of probabilities. We go to 100 now, and now look, we're between 53, 47. We're a lot closer to 50. I just saw 38 in there. I mean, it's possible to get a 99, but it's very unlikely. See, we're floating in the 40s and 50s now.
1,000 flips: 51, 49, 48. Now we're closer to 50 again. See how close we are? I haven't seen one under 40 or above 55 yet. Let's get even bigger—let's go to 10,000: 49, 49, 50, 50, 49. See? Now we're getting even closer.
We'll do 100,000. Now we're getting up there. See, it kicked in—it's over 10,000. Now we're starting to see our little percentages go, because this takes a little longer. 49.9, 50.
Now you might even want to add more decimal places, so if this is the kind of stuff you're doing, you can make this more decimal places. 50, 50.1. Let's do that—let's add more decimal places. Let's come in here and we'll go 0.00. Ready? Let's go—we're going to 100,000. Go. 50.014, 50.00, 49.88. Let's do a million. The more flips you do, the closer you get to 50. That's going to take a second. I wouldn't think you'd watch the whole thing.
There we go. 50.1. That's actually pretty high for the few past rolls I've seen. There you go. There's your random coin toss generator.
If you like stuff like this, you're really going to love my developer lessons. If you want to learn how to program in Visual Basic for Microsoft Access and you want to have fun doing it, you see all the crazy wacky stuff I do. This is the kind of stuff I love to do—just pick a topic like random numbers and just go at it, or accounts receivable, or whatever: pivot tables. I don't know. No, that's Excel.
So check it out—my developer classes, and also the extended cuts for my TechHelp videos. I love doing the TechHelp videos. I got the Fast Tips, I got the TechHelp. TechHelp usually goes longer and they cover all kinds of weird stuff. What I do is I do a little TechHelp, like quick answer the question quickly, and then I go into tons more wacky stuff in the extended cut.
So, if you want to learn this stuff, if you want to have a good time, if you want to have some fun, check it out. I'll give you information in a second on how you can join. I try to keep this stuff fun. I have fun doing it. I want you to have fun learning it.
That's your Fast Tip for today. I almost did that as TechHelp for today. I hope you learned something. I hope you had some fun, and I'll see you next time.Quiz Q1. What is the main purpose of the video tutorial?
A. To show how to create a random number generator for dice rolls
B. To generate and analyze multiple coin tosses using VBA in Access
C. To program a spreadsheet for statistics using Excel formulas
D. To design custom icons for Access forms
Q2. What programming language is primarily used in this tutorial?
A. Python
B. JavaScript
C. VBA (Visual Basic for Applications)
D. SQL
Q3. Where is the CoinToss function created so it is accessible throughout the Access database?
A. In a form's code module
B. In a report's code module
C. In a global module
D. In a macro
Q4. What does the CoinToss function return?
A. An Integer representing heads or tails
B. A String, either "Heads" or "Tails"
C. A Boolean value: True for heads, False for tails
D. A Double with the random value
Q5. Why is Randomize called in the CoinToss function?
A. To reset all values in the database
B. To ensure a different seed for the random number generator
C. To stop all loops running in VBA
D. To format the result as a percentage
Q6. What range of numbers does the Random function in VBA return?
A. 1 to 100
B. 0 to 1 (but never actually 1)
C. -1 to 1
D. 0 to 2
Q7. How does the function determine if the result is heads or tails?
A. By checking if Random is equal to 1
B. By comparing Random to 0.5: >= 0.5 is heads, < 0.5 is tails
C. By counting the number of tries
D. By generating two random numbers and comparing them
Q8. What is the purpose of using a For loop within the button's event code?
A. To initialize variables to zero
B. To flip multiple coins as specified by the user
C. To copy form controls
D. To automate report generation
Q9. How are the results of multiple flips stored and updated?
A. In arrays indexed by the number of flips
B. By incrementing Heads or Tails variable counters in each iteration
C. By writing new rows to a table for each flip
D. By updating a Label control with each result
Q10. What is the purpose of the StatusBox in the application?
A. It displays the final total of heads and tails
B. It shows progress updates during the loop of coin flips
C. It stores the results permanently in the database
D. It prevents users from clicking buttons
Q11. Why might the process seem slow when updating the StatusBox frequently?
A. Because updating the hard disk is slow
B. Because VBA loops are inefficient
C. Because frequent screen refreshes are slow
D. Because of database record locking
Q12. What does the DoEvents function accomplish in this VBA application?
A. It increases the speed of code execution
B. It allows the interface to update and respond to user actions during a loop
C. It disables all form controls during code execution
D. It saves changes to the database after each flip
Q13. The Abort checkbox is included in the design for which purpose?
A. To reset all results to zero automatically
B. To allow users to exit the flipping loop before completion
C. To change the coin from heads to tails
D. To hide the StatusBox
Q14. What common error occurs when trying to hide a control that currently has focus?
A. Compile error: syntax error
B. Type mismatch error
C. Cannot hide a control that has focus
D. Cannot update a hidden field
Q15. Why does the video mention that percentages get closer to 50 as the number of flips increases?
A. Because of Access limitations
B. Due to increased randomization
C. As a demonstration of probability and the law of large numbers
D. Because Boolean variables require it
Q16. What formatting is used to show percentage results with more decimal places?
A. Format: 0.0
B. Format: General Number
C. Format: 0.00
D. Format: Currency
Q17. What should you do if you are not familiar with concepts such as If statements, variables, or loops?
A. Skip the video
B. Watch prerequisite videos on these fundamental topics
C. Only run the provided sample database
D. Consult the Access help file exclusively
Q18. What is the benefit of placing utility functions like CoinToss in a global module?
A. They load faster on startup
B. Only certain users can access them
C. They are accessible from anywhere in the database
D. They use less memory
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-C; 12-B; 13-B; 14-C; 15-C; 16-C; 17-B; 18-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 focuses on building a coin toss simulation in Microsoft Access with VBA. This is a bit of a fun project that actually came out of a need I had in another video and project. I wanted to simulate flipping a coin, not just once but potentially hundreds of thousands of times, and see how the results stack up statistically as you increase the number of flips.
If you've watched my Random Numbers video or the Random Records video, you'll find those concepts are important for this project, so if you need to brush up on those, I encourage you to check them out first. There are also video tutorials covering the If function, If Then statements, variables, and For Next loops, which will help you understand the VBA used in this video. All of those videos are freely available on my website and YouTube channel.
Now, even though this is a Fast Tips video, I should warn you that it's not especially short—it's over 20 minutes long. I get to the main point quickly, showing how to create the coin toss function right at the start, and then later on I explore additional ideas for those who want to dig deeper or just have a bit of fun with Access and VBA.
I start out by opening up my TechHelp free template, which is available for download on my website. The first step is to create a coin toss function within a global module. This way, the function is accessible throughout your database by any form, report, or other module. This coin toss function will return a Boolean value: True for heads, False for tails. Essentially, we're generating a random number between 0 and 1, and if it's greater than or equal to 0.5, we call it heads; if it's less, it's tails. The function makes use of the Randomize statement to help ensure better randomness, although you could randomize just once when the form opens if you wanted to. For this project, I randomize each time for simplicity.
With the function in place, I switch back to the database and hook it up to a button—originally a "Hello World" button, but you can rename that to whatever fits your application. When the button is clicked, it generates a coin toss and displays either True or False, as you would expect.
Of course, flipping coins one at a time is just the start. To simulate hundreds or thousands of coin tosses, I add a text box where you can enter the number of flips you wish to make. I also set up text boxes to count the number of heads, the number of tails, and one to display the percentage of heads, calculated as heads divided by the total number of tosses. To prevent errors like division by zero, I make sure to remove any default zero values that might cause trouble.
In the VBA for the button, I introduce a loop: for X from 1 to the number of flips. Inside the loop, the coin toss function is called repeatedly. If the result is heads, it increments the heads counter; otherwise, it increments the tails counter. I also add a status box to keep track of progress, which becomes especially useful with larger numbers of flips.
If you've ever tried refreshing the screen or user interface in VBA within a long loop, you'll know it can slow things down enormously. To create a real-time status update that does not bog down the simulation, I include a DoEvents statement and only update the status box at set intervals, for example, every 10,000 flips.
For safety and usability, I include an Abort checkbox control on the form. It becomes visible during the simulation so that you can halt the process if needed. This works by checking the value of the control during each loop iteration, and if checked, the loop exits early. I also take care to move focus away from the Abort control before hiding it at the end, since Access throws an error if you try to hide a control that has focus.
To make the results user-friendly, I format the output boxes with thousand separators and set the percentage box to show one or two decimal points as desired. Along the way, I demonstrate how the percentages fluctuate more wildly with fewer flips and settle closer to 50-50 as you increase the number of flips, which illustrates the law of large numbers in probability.
As always, this project is typical of the teaching style in my developer classes and extended TechHelp videos. I like to pick a topic and see where it takes us, often adding little extras to keep things interesting and practical. Developer classes and my TechHelp extended cuts dive even deeper into topics like these, so if you enjoy this kind of hands-on, exploratory learning, consider checking those out on my website.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Creating a CoinToss function in VBA
Using the Randomize statement in VBA
Generating random numbers for coin toss simulation
Writing a public function returning Boolean in VBA
Implementing If Then logic for random outcome
Setting up form controls for simulation results
Creating variables for heads and tails counts
Looping through multiple coin tosses with For Next
Displaying real-time status updates during loops
Using DoEvents to update form controls while looping
Adding a user Abort feature with a checkbox
Handling control focus to prevent hiding errors
Formatting output to display percentages and thousand separators
Calculating and displaying the winning percentage
Adjusting display precision for percentage results
Observing probability convergence with large sample sizes |
