Open New Record
By Richard Rost
4 years ago
Open a Form and Go To New Record in Access
In this Microsoft Access tutorial, I'm going to teach you how to open a form and go to a new record and a specific field. Then as a bonus, I'll show you how to set the description text of your contact form equal to the first 50 characters of whatever notes are typed in, if the user doesn't enter a description. Cool stuff.
Pre-Requisites
Recommended Courses
Links
Topics
- OpenForm
- GoToRecord
- GoToControl
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, DoCmd.GoToRecord, acDataForm, acNewRec, DoCmd.GoToControl, DoCmd.OpenForm, open form to new record, Go to new record on opening form, How to open Form in Add record
Intro In this video, I will show you how to open a form directly to a new record in Microsoft Access, while still allowing users to browse and edit existing records. We'll use a couple simple VBA commands to accomplish this, and I'll explain how to set the focus to a specific field when the form opens. You'll also learn a practical tip for automatically filling in a short description field when entering notes, making your forms easier to use and more efficient.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to open a form to a new record but still be able to browse the other records in the form using Microsoft Access.
One of my pet peeves when you open a form is that it starts on the first record. If you're looking up data, that's fine. But sometimes, depending on the form, if I go to open the contacts form, for example, I don't want to be sitting on the first one. I want to be sitting here on a new one, ready to type in a new record.
It's more likely that I'm going to be typing in stuff into the contacts field, the contact history, information about our meeting, that kind of stuff. It's less likely that I want to go back through and scroll, but I don't want to open this in data entry mode. I did a whole separate video on data entry mode. If you go into the form's properties, right-click here, go to data, there's data entry. If you set that to yes, this becomes a data entry form, which means now you can't go back through and look at the previous records.
Here's a video I did on that a little while ago, and this is good if you have a form that you just want to give to someone who only does data entry and never needs to go back and look at the previous records. We don't want that for this one because I want the user to be able to go back and see the previous records. But if this thing has got 5,000 contacts in it, I don't want to have to come down here and click on new all the time. I want to just start there.
So how do we do that?
First things first, this will require two lines of VB code, just two. If you've never done any VBA programming before, go watch this video. Don't be scared of VBA. It's really easy. You'll need to learn a couple of commands, and you can make your database a whole lot more powerful.
Watch this video - 20 minutes long. It's free. It's on my website, on my YouTube channel. Go watch this now and then come on back.
Close the contacts form. This, by the way, is my TechHelp free template. This is on my website. You can grab a copy if you want to.
Now, I'm going to go into the button that I made for opening up the contacts form. Right-click, go to build event. Here's my code builder. Now here's the line of code that opens up the contact form:
DoCmd.OpenForm "ContactF", , , "CustomerID=" & CurrentCustomerID
In other words, open up the contact form and show only the contacts for this customer. If you want to learn how I built this database, by the way, go watch this video. It shows you how I put the customer form together, the contacts form, and all that good stuff.
After the form is open, I need to issue a command to go to a new record. Here's what that looks like:
DoCmd.GoToRecord acDataForm, "ContactF", acNewRec
That's it. Pretty straightforward. DoCmd.GoToRecord means you're going to go to a record on a form because you could use this on different types of objects - tables, queries, whatever. The name of the form you're working with is the contact form, the one I just opened. Where do you want to go? I want to go to a new record.
Save it. Close that. Close that. Reopen the customer form and then watch this - contacts. Now it's sitting right there, ready to enter a new record. But I can still go back through and scroll up these if I want to.
What if you want to also control what field it goes to, because the default I got it going to right here is the description field? Let's take a look at design view here. That's description. I know it says notes, but let's more correctly put description here. That's the description field, which is short text. We got the contact date, we got the big notes field down the bottom here.
Let's say, for example, you want it to go to contact date. I know I have this set as not a tab stop, so the first tab stop is this one, but just for the sake of class, let's say I want to force it to go to contact date.
Close this. Go back into here, design view. Go to the contact button again, and then we're going to issue another command:
DoCmd.GoToControl "ContactDate"
What control do you want to go to on the active form? The active form was just opened and we moved to a new record. What's the name of the control you want to jump to? ContactDate.
Save it. Close this. Close this again. Open up contacts. Now it's sitting on contact date. See that? Pretty cool. You can control what form opens, what record you go to, what control you go to, or what field you go to.
Ready for some bonus stuff? Let's get ready to play. Just kidding.
What I like to do is I like to have it so when this form opens, I like to put the focus down here. Go to a new record. Drop down here. When I type in some stuff, this is just a notes field here. If I don't specify a description, I take the first 50 characters of that and put it up there.
Ready? Here we go.
I'm going to hit escape. So the first thing is I want to go to a new record. I want to jump the focus down to notes. Let's do this. Go back in here, design view. Right-click. Change this. The notes. Save that name of that. That's it.
If I just type in some stuff now, notice it added a new record up top. But if I hit tab, come up here, for example, there's nothing in there. It's blank.
What I'm going to do now is I'm going to put an AfterUpdate in this guy. It's going to say if this is blank, if it's null, then put the first 50 characters up there. So there's three different things I'm going to use: IsNull, If Then, and AfterUpdate. I got videos for all those things. Go watch this one for IsNull if you don't know what null is. Here's a video on how to write If Then statements: if something, then do this. And here's a video on the AfterUpdate event to do something after a field is updated. Again, these are all free videos on my website. Go watch these if you've never seen any of these things before.
In here, let's design the contact form. Let's go to the properties for the notes field. For events, find AfterUpdate. Click ... for the code builder. I'm using my travel laptop right now, so I haven't turned this off. Normally, I turn the Choose Builder off. Click OK.
What we're going to say here is:
If IsNull([Description]) Then
[Description] = Left([Notes], 50)
End If
I forgot one thing. You need to know what the Left function is. Got a video for that too - Left, Right, Mid, Len, and String in my string functions video.
So, after the notes is updated, we're going to take a look at the description field. If it's null, if it's blank, we're going to set description equal to the left 50 characters of the notes field. Then we're all done with that, we're going to say:
Me.Refresh
which basically says save the record to the table.
Ready? And yes, I have a refresh video somewhere.
Save that. Ready? Contacts. Oops, I moved that. Let's put that back over here. Save it there. One more time. Ready? Contacts.
My focus now is sitting down there in notes, right down here. This is some cool stuff. And I'm going to press Tab. Look at that - it saved it up there in the description.
The reason why we want that text up in the description is because I could come down here and I could type in the Magna Carta. I could type in paragraphs and paragraphs and paragraphs of stuff. But there's a reason why we want a description field and a notes field.
The description field gives you a brief summary of what this is about, like "came in for an interview," but you could put all the information about the interview down here. There are some limitations to long text fields - sorting, searching, that kind of stuff. So you want a description field that's short text in addition to a big, long text field where you can put whatever you want.
So that's why I like to take - I like to say, hey, if the user hasn't specified a description, then just put in the beginning of that. It's kind of like what Microsoft Word does. If you type in a document and go to save it, it guesses what the title, what the file name should be based on the beginning of what you typed. Same kind of thing here.
So if I come back up here, for example, and I go, "this was some old stuff," it puts it up there. But if I come in here and change this now and hit tab, notice it didn't change the description because it already had something in it, hence the IsNull.
If you like this kind of stuff, this is the stuff I teach in my developer lessons. I have, right now, 40 of them. It's September of 2022. I try to make one or two new ones every month. Unlike my Fast Tips and TechHelp videos, I don't make you jump around a lot. Like today, I'm showing you that you have to know this, you have to know that, you have to know this. In my developer series, I start from the beginning. I start from the basics. I start from scratch, and I walk you through stuff in the order that you should learn it.
We start with the basics. We move on. So if you've never done any programming before, you want to learn how to program. You see how powerful you can make your databases with just the stuff I show in these Fast Tips, right? Start with Developer 1, work your way up.
This is also the kind of stuff I do in my extended cut TechHelp videos too. So if you like little programming tips and stuff, consider joining my TechHelp memberships. Gold members, for example, get access to all my stuff in the code vault. Lots and lots of code in there. Hundreds of videos to watch.
So there's your fast tip for today. I hope you learned something. I'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is Richard's main goal when opening the contacts form in Microsoft Access?
A. To open the form on the first record and restrict scrolling
B. To open the form in data entry mode so only new records can be added
C. To open the form to a new record while still allowing browsing of other records
D. To display only previous records and lock record editing
Q2. What is the downside of setting a form's Data Entry property to Yes?
A. It causes the form to open very slowly
B. Users cannot go back and view previous records
C. It allows users to accidentally edit all records
D. It adds extra unnecessary navigation buttons
Q3. Which VBA command is used to open a form filtered to a specific customer's contacts?
A. DoCmd.OpenTable "ContactT"
B. DoCmd.OpenForm "ContactF", , , "CustomerID=" & CurrentCustomerID
C. DoCmd.FilterForm "ContactF", "CustomerID=" & CurrentCustomerID
D. DoCmd.DisplayForm "ContactF", , , "CustomerID=" & CurrentCustomerID
Q4. What does the command DoCmd.GoToRecord acDataForm, "ContactF", acNewRec do?
A. Deletes the first record in ContactF
B. Moves focus to the last record in ContactF
C. Opens ContactF in read-only mode
D. Moves to a new (blank) record in ContactF
Q5. How can you control which field (control) the form focuses on when opened?
A. By rearranging the tab order only
B. By setting the focus on the desired field manually each time
C. By using the DoCmd.GoToControl command in VBA code
D. It is not possible to control the initial focus in Access forms
Q6. In the sample script, what is the purpose of the event procedure placed on the Notes field AfterUpdate event?
A. To sort all records alphabetically by notes
B. To copy the entire notes field into the description field every time
C. To set the description field to the first 50 characters of notes if the description is null
D. To clear the notes field if the description is already filled
Q7. Which function is used to get the first 50 characters from a text field in Access VBA?
A. Mid([Notes], 50)
B. Left([Notes], 50)
C. Right([Notes], 50)
D. Len([Notes])
Q8. Why is it beneficial to have both a description field (short text) and a notes field (long text) in the contacts form?
A. Because long text fields cannot store as much information
B. Because you need a duplicate in case one field gets corrupted
C. Because description provides a searchable summary while notes can hold detailed information
D. Because short text fields load faster than long text fields
Q9. What would happen if the user changes the notes field and presses Tab, but the description field already contains data?
A. The description field is overwritten with the new notes summary
B. Nothing happens; description remains the same
C. The notes field is cleared automatically
D. The record is deleted
Q10. Where can you find free videos on VBA basics, If Then statements, and specific Access functions?
A. Only in the Developer 1 course
B. On Richard's YouTube channel and website
C. Only via a paid membership
D. Only in Microsoft documentation
Q11. What is a benefit of becoming a Gold member as described in the video?
A. Access to all lessons in every language
B. Access to all full beginner courses for every subject
C. Access to all sample databases, code vault, and extended cut videos
D. Unlimited one-on-one programming help
Q12. When does the Me.Refresh command need to be used in the Notes AfterUpdate event?
A. Before modifying the description field
B. To save the record to the table after updating fields
C. Only when sorting records
D. To delete the current record
Answers: 1-C; 2-B; 3-B; 4-D; 5-C; 6-C; 7-B; 8-C; 9-B; 10-B; 11-C; 12-B
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 a simple technique to open a form in Microsoft Access directly to a new record while still allowing users to scroll through previous records.
One thing that has always bothered me about opening forms is that they typically start at the first record. While this is fine when you are searching for data, it is often more efficient to have the form ready for a new entry, especially for something like a contacts form where you're frequently entering new information about meetings, calls, or other interactions. However, I do not want to open the form in data entry mode because that prevents navigation to earlier records. If you're not familiar with data entry mode, it simply restricts the form to adding new data and hides all prior entries. This setup is suitable for specific data entry purposes, but it's limiting if you need to reference what has previously been entered. If your database contains thousands of entries, you probably do not want to keep clicking to reach the "new record" row each time.
To solve this, all you need is a couple of lines of VBA code. If you're new to VBA, I recommend watching my introductory video, which covers the essentials and shows how much more powerful your database can be with just a little programming knowledge.
Once you've got a handle on the basics, here's how you can set this up. In my TechHelp free template database, I created a button to open the contacts form. Normally, I would use a command to open the form filtered to only show contacts for a given customer, but after the form opens, I immediately move the focus to a new record. Using VBA, after the standard command to open the form, I add another line that tells Access to jump to a new record. This way, every time you open the form, you're ready to enter new information, yet you can still scroll back to previous records without restriction.
Sometimes you might want the cursor to land in a specific field instead of the default. For instance, you may want to start entering data in the contact date field as soon as the form opens. This is easily controlled by adding another line of VBA to set the focus to that particular field upon opening. Just replace the field name as appropriate for your situation.
Now, let's look at a helpful automation to make data entry even easier. On my contacts form, I prefer that when users land on the form, they are immediately ready to type their notes. If a user fills out the notes field but leaves the description field blank, I like the database to automatically copy the first 50 characters of the notes into the description. This keeps the description field useful as a summary or headline for searching and sorting, while the notes field stores the full detail. To accomplish this, I set up an AfterUpdate event on the notes field. If the description is empty, the code copies a short snippet from the notes. This means every new entry has a ready-made description, unless the user manually supplies one.
It's important to understand why this is valuable. Description fields, usually set as short text, are perfect for quick summaries, indexing, or sorting. Notes fields, which are generally long text fields, can hold far more data but come with some limitations in terms of searching and performance. By automatically filling the description when needed, your forms remain efficient and easy to navigate, mimicking the convenience you see when saving a new document in Word and it suggests a filename.
If you want to expand your skills, this type of automation and many other programming techniques are covered in much more depth in my developer lessons. These courses are carefully organized starting from the basics, with each step presented in a sequence that builds on what you have already learned. If you're interested in making your databases more dynamic, I suggest starting with my Developer 1 course and moving up from there.
For those looking for even more, I also produce extended cut videos for TechHelp members, where I explore topics in more detail and provide solutions to specific programming challenges. Members also get access to resources like sample databases, the code vault, and priority question submissions, depending on membership level.
I hope this fast tip helps you work more efficiently in Access. 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 Opening a form to a new record in Access with VBA
Allowing browsing of existing records while starting on a new record
Difference between data entry mode and normal mode in Access forms
Adding VBA code to open a form and go to a new record
Using DoCmd.OpenForm to open a form for a specific customer
Using DoCmd.GoToRecord to move to a new record in a form
Setting focus to a specific control when a form opens using DoCmd.GoToControl
Configuring tab order and tab stops in form design
Using AfterUpdate event to automate field population
Automatically setting a description field based on notes input
Using IsNull and If Then statements in VBA for validation
Applying the Left function to extract characters from a string
Refreshing a form record using Me.Refresh after automation
Differentiating between short text (description) and long text (notes) fields
Rationale for keeping both description and notes fields in a form |
