Fitness 51
By Richard Rost
27 days ago
Use Click & Shift-Click to Select Contiguous Records
In this Microsoft Access tutorial, we'll talk about how to set up shift-click functionality in your fitness tracking form so that you can select and update multiple records at once using the Shift key. I'll show you how to use the MouseDown event to detect shift-clicks, work with recordsets to loop through records in the current day, and ensure updates stop according to your criteria. We'll also discuss handling issues that can come up with loops, troubleshooting glitches, and adding logic to turn items on or off in a group with shift-click. This is part 51.
Members
There is no extended cut, but here is the file download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, shift click selection, MouseDown event, recordset object, foodDateTime field, looping records, updating records, SQL WHERE clause, descending order, EOF property, short circuit evaluation, Do While loop, exit do, hasEaten field, checkbox toggle, bug fix, timestamp increment, bulk select, select all
Subscribe to Fitness 51
Get notifications when this page is updated
Transcript
Alright folks, in the last video I talked about doing something where we can click on one item and then hold the Shift key down and click on another item and it selects everything between them. I think that'd be a good lesson. Alright, it's going to involve some recordsets so if you don't know what recordsets are, go... Well, we've done a bunch of recordsets, but if you're coming in new, go watch my recordsets video. This guy.
Trying to make these fitness videos since people stopped watching that weren't interested in fitness by around like 18. So I'm trying to... If you haven't noticed, I've tried to retitle these so they're having more broad appeal. Since I have, the views have gone up so that's wonderful.
So what we're going to do is, here's our algorithm. Think about it this way in your head: we only want to affect the current day. We're going to loop from the record that is clicked on to the beginning of the day. We're going to add some more to it in a little bit, but that's our starting algorithm. So I'm going to start from this record and have a recordset that walks back through all of these until it hits the start of the day - nothing from yesterday or before.
Alright, let's start with that. Now, to figure out if the user clicks or just shift-clicks, we have to use the MouseDown event because if you look at the events, the OnClick event, which is the one you think about - the OnClick event does not have any way in here to tell you what the status of the Shift key is. So get rid of that.
But what we can use is the MouseDown event. There's MouseDown, MouseUp, MouseMove. MouseDown is when the user clicks a button and presses the button down. MouseUp is actually when they release that button. You'd think Access would have a lot more like click and drag stuff available to it with those two events, but not really. So I almost never use MouseUp, but MouseDown is handy because MouseDown sends you what button it was - left, right, or middle - then the shift, which indicates the status of the Shift, Control, and Alt keys. We only care about the Shift key.
So in this particular case, we're going to say if shift equals 1, then that's the shift-click. And you could say, I believe it's button equals 1 too, so we could say if button equals 1. Pretty sure. If button equals 1 and shift equals 1. I know shift equals 1 is the shift key. I think Control is 2 and I think Alt is 4. I'm not positive. We can test this by simply message boxing. Hi.
So if the user hits shift left-click, we should see a "Hi" now. If I just click, I get nothing. If I go shift-click, I get "Hi."
Now, instead of just message boxing, we want to loop through the records starting with the current one, going backwards until we hit the start of the jump. So we need a recordset object. So, dim rs as a recordset. In here, we're going to start up our recordset and we're going to say set rs = CurrentDb.OpenRecordset. Select all the records from food or all the fields from foodlogT.
Then we've got some WHERE conditions coming up. Let's go to the next line. WHERE let's say that the foodDateTime, which is the field we're looking at, has to be less than the current foodDateTime.
Now, remember, the way we have our table set up, no two foodDateTimes can have the exact same time. Because if we type in a second moment at the same time, it'll add a second to it. So if you're doing this with something that doesn't have a chronological ordering with a date/time stamp, you could look at an autonumber or some sort of field or whatever you've got, but we're using foodDateTime.
And even if you didn't have that thing in there where it's a second off, like we programmed in, it'll still work. You just might have several records that might be less than it with an equal time. So if you get like 8 p.m., 9 p.m., 9 p.m., 9 p.m., 10 p.m., and you click on 10 p.m., all three of those 9 p.m.s will be included in there. So you don't necessarily need that second offset that we built in.
So WHERE foodDateTime in the recordset is less than, not less than or equal to, less than, because the click itself will change the value of the record that we're on. We're not to change it in the recordset. And foodDateTime, that's the actual field on the form this time. It's confusing: you get the value in the recordset and the field on the form. Close your pound signs.
Now we also need to stop when we get to the beginning of the day. Space, and then we're going to make another continuation. And foodDateTime is greater than or equal to right now, which is this guy up here, which is logDate. LogDate and close that up.
Actually, we need one more space in there because we need our ORDER BY. ORDER BY is going to be foodDateTime descending. Start with the current record and walk backwards. If all that is valid and you hit Enter... Oh, that's how you can tell something's wrong in there.
Oh, I know what I did. Anybody see it? Take a look real quick, pause the video if you have to see the problem. It's actually not my SQL. It's the fact that I forgot to close one parenthesis right there. SQL is actually good. There we go. See, that's a check there.
What you want to do is make sure when you hit Enter all your stuff properly capitalizes and all that.
So now inside the loop, while not rs.EOF, do some stuff. rs.MoveNext. I always put the bookends on my loops so that, and always remember this guy, so that you don't endless loop.
Now at this point, let's just test it. Make sure everything's working so far. Let's edit the record and set hasEaten to true. We'll just set them all to true. So we're going to say rs.Edit. rs.HasEaten = true. rs.Update.
That updates all of the records. Whenever I shift-click, it updates all the records before the one I'm on to true. You ready?
Debug, compile once in a while, come back out here. Now I'm going to shift-click on this guy. Boom. There you go. It looped backwards through all until at the beginning of the day and set them all equal to true.
For a Star Trek pun, it checked off. Get it? Chekov. All of those. I couldn't resist.
Now, moving to the next step. What I'd like to happen is let's say I want to see just how many calories are in this coffee item here. I like this guy and this guy. So what I'd like to do is I'd like to have it so when I check the first one, if I check this guy here, it stops when it hits a checked one.
If I do this now, right now it's doing all of them, but I want it to stop there. So in my loop, I can stop the while loop. Let me make a little space here so we can see this.
Right here you'd think it'd be just as easy as saying and rs.HasEaten = false. As long as it's false, then you can loop backwards. Stop when you hit one that's not false. But look at what happens. Let me uncheck these. Ready? Click, shift-click. Sometimes it works. Sometimes it works. Sometimes it doesn't.
I just found a case where it doesn't. Watch this. If I do shift-click here now. Boom. That's what happened - you get the no current record because what's happening is it's going past the EOF.
Watch what happens. If I put a breakpoint here. Let me uncheck these again. If I do shift-click now, right here rs.HasEaten is false. Fine. It does that record. Goes to the next record. Goes to the next record. It's going backwards. Eventually it's going to hit the beginning where the rs.HasEaten is true. You get no current record because it can't evaluate rs.HasEaten.
This is an issue with VB and VBA. It tries to evaluate the entire line instead of just stopping here and saying, okay, I hit the end of the record. That means this is going to be false. I should just drop out now. That's called short circuit evaluation.
Languages like C, C++, and all that have that, and it won't even bother evaluating the rest of the line. But with VB, it does. It evaluates the entire line. So the trick is to simply move this part inside the loop.
What we're going to do is we're going to say while not rs.EOF, we're going to come down in here, and we're going to say if rs.HasEaten = false then do this stuff. And then, end if. Save it, debug, compile. Now it should work. Watch. I can do this one to this one. Well, it's going all the way to the beginning though, because we didn't tell it where to stop. So we have to put an exit the loop inside of here.
So else, exit while. Oh, wait a minute. Why is it red? Anybody know? Yeah, there is no exit while. That's one of the downsides of a while loop, you can't exit out of it gracefully. You can put a GoTo in there, but I hate GoTo statements. I avoid them if I can, with the exception of error handling - GoTo statements are helpful for error handling.
So we just have to slightly rewrite this. I love while loops. I usually do everything with while loops and for loops. If I know how many times I have to loop, I use a for loop. If I don't, I use a while. In this case, we just have to switch the while loop to a do while loop. With that, you can exit out of it. So it's a newer, like, version 2.0 while loop.
So it's do while, and then in here, you can say exit do. And then "when" simply becomes "loop." It's a do while loop, just changing the structure slightly.
Now what'll happen is it'll go backwards until it hits a false. When it does hit a false, it'll exit the do loop because you're done. Save it, debug, compile, come back out.
Every time I hear "do," I think of my dogs because whenever I want to do something with the dogs, I'll say "do." Their heads will tilt like, "Do you want to go for a car ride or do you want to go swimming?" I'm saying all the keywords now and I can see their heads moving. They know all those; they're getting all excited.
Anyways, let me uncheck all these. Let's try it again. Let's shift-click here. Okay, that worked. Let's click here and then shift-click here. It stopped and exited the loop. See?
Now we've got one more thing left to do. Let's make it so we can undo those as well. Instead of making it all false, let's make it equal to the value of the guy we're checking. So if we're unchecking the box, do the same thing, just backwards. That's just changing a couple things. So we're going to say if rs.HasEaten = HasEaten (the box we're checking on), then all you want to do is flip it. So rs.HasEaten = not rs.HasEaten.
That's it. Save it. Back out here. Now we can go click, shift-click to turn all those on, or click, shift-click to turn them all off. Look at that. Isn't that cute? Come down here. When I get rid of these guys, click, shift-click. Then get that one in the middle. Let's see here. What happened? Click, shift-click.
Hold on. Let's figure this one out. I think it just glitched because sometimes Access does that, folks. I just exited out and tried it again. Click, click, click. I can go click and then shift-click, and now it's working. I think it was just a glitch.
That's why a lot of times you see me tell you, save it, save it, save it. Close it and reopen it because VB does glitch sometimes. It's working. I've tried like ten times off camera, and it's working perfectly now.So yeah, sometimes a glitch throws in there. There is another bug that I discovered and we'll fix it another time. But our little one second offset trick does not fire if we manually type the time in here. So if I put in here 9 p.m. something. My caps lock is on. If I come in here and type in 9 p.m. something else, and if I look at the table now, on the bottom, look, see 9 p.m. 9 p.m.
So we have to add that little code that increments the time if it sees that exact time in there when we manually add an item too, and we'll do that in the next class. We'll start off with that bug fix.
But there you go. Now you have a really cool way to click, shift-click any of your items that you want. Click, shift-click turns them all off. I like that. We can also set up one to select them all, select all next time too. I do not know where I am going to put it. Maybe down here. Select all. We'll figure it out.
TOPICS:
Implementing shift-click multi-select on a form
Using the MouseDown event to detect shift-click
Checking the status of the Shift key in VBA
Working with the MouseDown event arguments (button, shift)
Creating and opening a recordset with criteria
Building SQL with WHERE and ORDER BY in VBA
Looping through records with a recordset
Editing and updating records in a recordset
Updating boolean fields based on user actions
Using a loop to iterate backwards in time within a day
Handling duplicate datetime records with offset logic
Implementing stopping logic when a checked record is reached
Understanding short circuit evaluation in VB and VBA
Restructuring a While loop to Do While for exit control
Flipping checkbox values based on selection
Troubleshooting Access glitches in VBA forms
COMMERCIAL:
In today's video, we're learning about how to make your Access database let you click and then shift-click to select and update a whole range of records at once, just like you can with file lists. I will show you how to use the MouseDown event to detect shift-clicks, loop through records for just the current day with a recordset, and make sure your code stops where you want it without getting stuck or missing records. You will see how to handle marking records as eaten or not, how to fix some tricky bugs, and even learn tips for dealing with those classic Access glitches. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main goal of the feature discussed in the video?
A. Allow users to delete multiple items at once
B. Allow users to select and modify multiple items between clicks using shift-click
C. Allow users to copy items from previous days
D. Allow users to export data to Excel
Q2. Why is the MouseDown event used instead of the OnClick event?
A. MouseDown allows drag and drop functionality
B. MouseDown can detect the status of modifier keys like Shift
C. OnClick is not supported in Access
D. MouseDown is faster to process than OnClick
Q3. What does the 'shift' parameter in the MouseDown event indicate?
A. Which mouse button was pressed
B. Whether the Shift, Control, or Alt keys are held down
C. The row number being clicked
D. The name of the database
Q4. In the MouseDown event, what value does 'shift' have when the Shift key is pressed?
A. 0
B. 1
C. 2
D. 4
Q5. Which field is being used to ensure chronological ordering of records?
A. AutoNumber
B. logDate
C. foodDateTime
D. recordID
Q6. Why is the SQL WHERE clause using 'foodDateTime <' rather than 'foodDateTime <='?
A. To exclude the currently clicked record from selection
B. To include all possible matches
C. To select only records with today's date
D. To prevent errors in the recordset
Q7. Why did the initial attempt to stop at already checked items inside the loop fail?
A. The SQL was missing an ORDER BY clause
B. The loop condition tried to evaluate a field in a non-existent record, causing an error
C. The code was missing a GoTo statement
D. The form did not have focus
Q8. What programming concept does Visual Basic for Applications (VBA) lack that causes evaluation errors in loop conditions?
A. Type inference
B. Short circuit evaluation of logical expressions
C. Automatic error correction
D. Variable scoping
Q9. What was done to overcome the limitation of not being able to exit a While loop gracefully in VBA?
A. Replaced the While loop with a Do While loop
B. Used an infinite loop and forced termination
C. Added error handling with GoTo
D. Removed all loops
Q10. How is the feature finally made to both check and uncheck consecutive items using shift-click?
A. By always setting hasEaten to true
B. By always setting hasEaten to false
C. By setting rs.HasEaten to the inverse (not) of its current value based on the clicked box
D. By deleting the records
Q11. What is a potential issue with manually entering times in the foodDateTime field discussed in the video?
A. Manually entered times trigger infinite loops
B. Manually entered times can be duplicated without the one-second offset, breaking uniqueness
C. Manually entered times convert to the wrong data type
D. Manually entered times are automatically deleted
Q12. What is highlighted as a best practice when dealing with code and possible Access glitches?
A. Avoid saving frequently
B. Rely on auto-recovery
C. Save, close, and reopen the application often
D. Ignore errors and proceed
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-A; 7-B; 8-B; 9-A; 10-C; 11-B; 12-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 TechHelp tutorial from Access Learning Zone will focus on enhancing the user experience in an Access database by allowing users to select a range of records by clicking on one item, holding down the Shift key, and clicking on another. This is a common feature in many applications and it is something many users find helpful for bulk editing or updating records.
Before getting into the details, it is important to be familiar with recordsets in Access VBA. If you have not worked with recordsets before, I recommend reviewing my previous lesson on that subject first.
For this example, imagine we have a food log form, and we only want to affect records from the current day. Here is the basic plan: when you Shift-click on a record, the code will loop backwards from the selected record up to the start of the day, but not include records from previous days.
This process relies on the MouseDown event instead of the standard OnClick event. The OnClick event in Access does not provide information about the status of the Shift key, but MouseDown does. MouseDown gives you the button that was pressed (left, right, or middle) and a value for modifier keys like Shift, Control, or Alt.
If the Left mouse button is pressed and the Shift key is held, we can program the logic to handle a Shift-click. In this context, the Shift key value is 1. By checking if the shift argument is equal to 1 during the MouseDown event, we know if the user performed a Shift-click.
Once Shift-click is detected, the goal is to loop from the currently selected record backwards to the first record for that day. This means constructing a recordset with an appropriate WHERE clause, checking for those whose timestamp (foodDateTime) is less than the currently selected record, but still within the same day.
In our setup, each food log entry has a unique date and time. If for some reason two entries share the exact same timestamp, the system offsets the time by a second to maintain uniqueness. While this is helpful, it is not strictly necessary for this solution, but keep it in mind if you are applying the technique to a table without strictly unique timestamps.
With the recordset in place, we can loop through the records using a While loop to update a field such as HasEaten to True for all relevant records from the current point back to the start of that day. After testing this logic by Shift-clicking, you should notice every record up to the beginning of the day is updated as intended.
However, it is also useful to stop the update if you reach a record that has already been checked. This means the loop should halt once it encounters a HasEaten value that is already True. Initially, you might think to include HasEaten = False as an additional condition in the loop's test, but VB and VBA do not always evaluate conditions in the order you expect. They do not always use short-circuit evaluation, which can lead to errors such as "no current record."
The workaround is to place the HasEaten check inside the loop, rather than in the loop's condition. That way, you can use an If statement to check HasEaten for each record and then use Exit Do if you want to stop processing. Since While loops in VBA do not allow Exit While, you need to switch to a Do While loop, which does allow for graceful exiting with Exit Do.
With this logic in place, the process works as intended: Shift-clicking updates all records from the selected one back to the first unchecked record, but not past previously checked ones.
For further refinement, say you want both the ability to check or uncheck a range of items in one go — not just set all to True but match the status of the item you clicked. This is accomplished by checking the value of HasEaten on the record you clicked, then applying the opposite value to all selected records. If you are unchecking, all will be set to False; if checking, all will be set to True.
Occasionally, Access VBA will glitch and you might need to save, close, and reopen the form to get everything working smoothly. It is a good practice to save your work frequently and recompile the code to prevent any lingering issues.
Lastly, there is an outstanding minor issue: if you manually type in a date and time that already exists, the code that provides the one-second offset does not run. This will need to be addressed in a future lesson to maintain unique timestamps across all entries, even those added manually.
This method now gives you a flexible way to select and update ranges of records in your Access forms using Shift-clicks, much like you can in other programs. In the future, I'll show how to add a "Select All" feature as well.
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
Implementing shift-click multi-select on a form
Using the MouseDown event to detect shift-click
Checking the status of the Shift key in VBA
Working with the MouseDown event arguments (button, shift)
Creating and opening a recordset with criteria
Building SQL with WHERE and ORDER BY in VBA
Looping through records with a recordset
Editing and updating records in a recordset
Updating boolean fields based on user actions
Using a loop to iterate backwards in time within a day
Handling duplicate datetime records with offset logic
Implementing stopping logic when a checked record is reached
Understanding short circuit evaluation in VB and VBA
Restructuring a While loop to Do While for exit control
Flipping checkbox values based on selection
Troubleshooting Access glitches in VBA forms
Article
In this tutorial, you will learn how to implement a "Shift-click to select a range" feature in a Microsoft Access form, similar to how you can select multiple files in Windows Explorer by clicking on one item, holding Shift, and clicking another to select everything in between. Specifically, I'll show you how to use the MouseDown event to detect when the Shift key is pressed and how to use recordsets to efficiently update all relevant records between your two clicks in a single day. I'll also explain how to handle toggling checked states in both directions and address some quirks and errors you might encounter in VBA.
To start, let's explore the core idea. The objective is for users to click a checkbox in a continuous form or datasheet, then hold the Shift key and click another checkbox further up or down in the same day. All checkboxes in between should be checked or unchecked to match the second click's state. For this we want to process only records within the same day and between (and including) the two clicked records.
First, we need to capture when a user shift-clicks a checkbox. You might think to use the OnClick event, but this event does not provide information about whether any modifier keys (like Shift) are held down during the click. Instead, use the MouseDown event on the checkbox control. This event has three arguments: Button (which mouse button was pressed), Shift (which modifier keys were held), and X/Y (position of the click, which we do not need here).
Here's how you can check specifically for a Shift + Left Mouse Button click:
If Button = 1 And Shift = 1 Then
' Your shift-click code goes here
End If
In this context, Button = 1 means the left mouse button, and Shift = 1 means the Shift key. (Control and Alt have other values, but here we only care about Shift.)
Once you have detected a shift-click, you want to select all records between the current record and the start of the day, or until you hit a pre-checked box. Here comes the use of a recordset. Let's assume you are working with a table called foodlogT, and the date/time field is foodDateTime. To uniquely order the records, this field must be unique, which can be accomplished by auto-incrementing seconds for duplicate time entries if needed.
Here is the basic structure for opening the recordset:
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM foodlogT WHERE foodDateTime < #" & Me.foodDateTime & "# AND foodDateTime >= #" & Me.logDate & "# ORDER BY foodDateTime DESC")
This SQL statement selects all records from the current time back to the start of the current day (logDate). Adjust the field and form names as appropriate for your setup.
To update all records in this range, loop through the recordset:
Do While Not rs.EOF
rs.Edit
rs!HasEaten = True
rs.Update
rs.MoveNext
Loop
This sets HasEaten to True on each record, effectively "checking off" each item from the most recent (your current click) back to the start of the day.
But the real feature is to make this act like standard multi-selection: If you start from an unchecked box, then shift-click on a checked box, you want all the boxes in between to flip to checked as well; if you do the reverse (start from a checked box and shift-click on an unchecked one), you want them all unchecked. So it's better to set each HasEaten field to the value of the ending box.
First, determine the intended new value: is the user checking or unchecking? Use the value of the checkbox under the mouse.
Then, update the loop to set all HasEaten values in the range to either True or False, matching the destination box:
Dim newValue As Boolean
newValue = Me!HasEaten
Do While Not rs.EOF
If rs!HasEaten <> newValue Then
rs.Edit
rs!HasEaten = newValue
rs.Update
End If
rs.MoveNext
Loop
However, you may want more precise logic: for instance, to stop updating if you hit a record already in the target state. Visual Basic's While...Wend loop structure does not support breaking out early with an Exit While (unlike Do While...Loop, which does with Exit Do). So, use Do While...Loop for better control:
Dim rs As Recordset
Dim newValue As Boolean
newValue = Me!HasEaten
Set rs = CurrentDb.OpenRecordset("SELECT * FROM foodlogT WHERE foodDateTime < #" & Me.foodDateTime & "# AND foodDateTime >= #" & Me.logDate & "# ORDER BY foodDateTime DESC")
Do While Not rs.EOF
If rs!HasEaten = newValue Then
Exit Do
End If
rs.Edit
rs!HasEaten = newValue
rs.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Now, whenever you shift-click a checkbox, all prior records in the same day (according to foodDateTime and logDate) will be set equal to the value of the box you clicked. If you check a box, all unchecked boxes back to the start of your day (or another checked box) become checked. If you uncheck a box, all checked boxes back to the start, or until you hit an unchecked box, become unchecked.
Whenever you make changes like this, be sure to frequently save your VBA code and recompile (Debug > Compile) in the VBA editor. Access can occasionally experience glitches between design and runtime; if things start behaving strangely, save, close, and reopen your form and Access to clear any errors.
A final note: If your datetime records are not always unique, you might need to adjust your logic slightly. For example, if two records have the exact same foodDateTime, both will be included, since the filter uses <, not <= or equality. This generally works unless users manually enter duplicate times, in which case you may want code to auto-increment or otherwise enforce uniqueness.
With this setup, you have implemented efficient shift-click multi-select logic for a continuous form in Access, just like selecting files in Windows or items in many programs. If you want to add features like Select All, or handle other quirks (such as duplicate times from manual entry), you can build on this approach. This logic is both flexible and efficient for handling bulk updates to contiguous records.
So, the next time you want to change the status of several checkboxes in a row, just click one, hold Shift, and click another: all between will follow suit, making tracking, updating, or logging activities much faster in your Access applications.
|
