Field Value Substitutions 3
By Richard Rost
7 months ago
Fix Typos with Field Substitution, Update Queries Part 3 In this Microsoft Access tutorial, I will show you how to handle field value substitutions by creating a text-based combo box. This video will teach you to maintain a dynamic list allowing users to select from known country names or type in new entries without binding to an ID field. You'll learn how to construct an aggregate query to generate a unique list of countries and use it to populate the combo box, enabling flexible data entry. This is part 3 of the series. MembersIn the extended cut, we will learn how to check if a typed-in value is a known country or a known misspelling and make the appropriate substitution. Additionally, we will set up a prompt to ask the user for confirmation if the entry is not found in either list, allowing them to add it if desired. Silver members and up will have access to all extended cut videos, where I will show you how to implement these features. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, field value substitution, misspelled countries, known country list, text-based combo box, aggregate query tutorial, unique country list, combo box text field, form design combo box, CountryCombo, aggregate query countries, unbound combo box, tab order form design, Limit to List property, handle new entries in combo box
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part three of my field value substitution series, where we're taking a list of known countries and their misspellings and then applying them to any new misspellings that people type in. Go watch parts one and two if you want a better explanation for what we're doing. Watch those two videos and then come on back.
Alright, so we're all set on the table level and with our two queries. We have one query to fix known misspellings, and another query to bring up misspellings we don't know about. This is good for batch updates or if you import stuff, or if you want to check it once a month. But what about day-to-day?
What about if you want to make the users pick from a list here but still give them the option to type in what they want? That's where we can easily use a text-based combo box. It's a combo box filled with a list of items, but it's not bound to an ID field. It's just a text field. It's a little more rare as far as combo boxes go, as far as the way I use them. I usually bind my combo boxes to ID fields, but again, I have to emphasize this is for situations where you don't want to have a properly relational, properly normalized relationship between two tables. We're just going to make it a list that the user can still edit.
What I'm gonna do is delete this country text box. We're going to put something better there. Actually, before we do that, let's make a single list using our known data. We have a list of known countries here, but I don't want to have USA show up six times. Let's use an aggregate query to make a nice pretty list of just one of each of these known countries. If you don't know what an aggregate query is go watch this video and then come on back. It's basically you can take a big long list of stuff and break it down so they're all grouped, so you get one of each.
Alright, we'll go to Create, Query Design, and we're going to get rid of this thing and bring in our country table. I'm bringing in my country table, not the misspellings, just the country. Go to Query Design and set this to a Totals or Aggregate Query and make sure this is grouped by. Now, when I run it, I get a unique list of just the countries, in there once each. I'll save this as my CountryUniqueQ.
Notice I'm starting all the country stuff with "Country" because later on if you want to find all your country stuff just come over here and type in "Country" and there's all your country stuff. See how easy that is? So now this is a good list I can use to build that combo box with.
Back in here, let's go to Form Design. Find the Combo Box and drop it down. We're going to look up the values from a table or query, the values that go in the box, right, next. Where are we going to get that list of values from? They're stored in the country table, but that CountryUniqueQ is going to give us what we want: just one of each country. Next. There's only one field in there, bringing over. Normally there'd be an ID in here, but we're not binding this to an ID. We just want the text. Next. Do you want to sort it? Yes, sure, why not? You could sort it here or in the query. Either one is fine, it doesn't matter. Next. This is what it's going to look like. Resize this if you want to. Notice there's no bound hidden column over. That's fine. Next. What are you going to do? We're going to store that value as text in the Country Text field in the customer form. Next. What label would you want? "Country" is fine, and don't forget to name your combo box too. I wish the wizard did that. Access team, hint up in the sky. Go to All, find the name, and we're going to call this my CountryCombo, Country Combo Box. Alright.
Then I'm going to use a little Format Painter and get that label color. Okay, slide that up. While we're at it, let's put it in the tab order where it belongs. Instead of CountryCombo at the bottom, we're going to put it after Zip Code and before Notes, so when I tab off of it that goes over here instead of the next record. That'd be another thing on my wish list for the Access team. When you put a control on the form, automatically stick it in the tab order where it goes. I know you really can't tell because the tab order could go across this way or down that way, but like I said, that's why it's on my wish list.
Alright, save it, close it, open it. Nope, wrong one, open it. Now if I come in here I can drop this down, pick a value, or I could type something in myself like "Starfleet" and it accepts it. It doesn't have to have an ID behind it. It's not bound anywhere. The actual data, the text that I type in, is still being stored in the Country field even though I can pick from a list of options. It's a nice option to have for combo boxes. I don't use this often. I really don't. It's rare that I do this, where I have a combo box based on just text and it stores text in the table. Usually it's based on an ID.
Now the user can either pick from the list or type something in. If you don't like what they type in, you can still go through here. You could fix this. It'll fix any known misspellings. You can check for unknown ones. Oh, there's Starfleet. What do I do with that? Well, that's for you to decide. Add it to your table, yell at the user, fire them, fire them from a cannon, whatever you want to do.
Now if you do want to force them to pick from the list, which totally defeats what we're doing here, you can set "Limit to List" equals yes. If you come in here to the properties, go to Data, there's Limit to List. You can set that to yes, then you're forcing them to pick from your list. But that defeats what we just built. You want to still allow them to type in a value.
What if they type something weird in here like Starfleet? You then have the box to:
a) Check to see if it's a known country. If it is, you're good.
b) Check to see if it's a known misspelling, and if it's in that list, you're still good. Make the substitution at that point.
c) If it's not in either of those lists, ask the user if they're sure they want to add it. Say, "Hey, Starfleet isn't in the country table. Are you sure you want to add it?" And if so, let them add it anyway. Wouldn't it be nice if you could do that? Well, you can.
We're going to do that in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Gold members can download these databases, and everybody gets some free training. So check it out; click that blue Join link below. But that is going to do it. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Field value substitution for country names
Creating a query for known misspellings
Using aggregate queries to list unique countries
Creating a combo box with a text field
Building a country combo box from a query
Setting up combo box properties
Using Format Painter for consistent design
Adjusting the tab order for form navigation
Using a combo box with text storage in tables
Allowing text input alongside combo box selection
Setting Limit to List for combo boxes
Validating user input against known lists
COMMERCIAL:
In today's video, we're continuing with part three of our field value substitution series. We'll discuss how to handle known and unknown misspellings of countries in your Access database. We'll create an aggregate query to produce a unique list of countries and set up a text-based combo box that allows users to pick from a list or type in their own entry, even without a bound ID field. You'll see how to format your form to make it user-friendly and manage user inputs effectively. For those interested in extended features like checking unusual entries, this is covered in our extended cut for members. 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 purpose of using an aggregate query in this video?
A. To create random entries for each country
B. To count the total number of misspellings per country
C. To generate a unique list of known countries
D. To find relationships between different tables
Q2. What type of combo box is used in this tutorial?
A. A combo box bound to an ID field
B. A text-based combo box not bound to an ID
C. A combo box that only accepts predefined values
D. A combo box with multiple columns for sorting
Q3. Why would you use a text-based combo box instead of one bound to an ID field?
A. To store numbers instead of text in a table
B. To allow the user to add new entries that aren't in the database
C. To ensure all data entries are standardized
D. To limit inputs to a specific format
Q4. What feature is suggested to handle new entries not present in the database?
A. Automatically update the combo box with new entries
B. Add a dialog box to confirm entry of new values
C. Delete the entry if it's not recognized
D. Ignore new entries and proceed with existing data
Q5. What happens if "Limit to List" is set to Yes?
A. Users can add any new values they want
B. The list will be sorted automatically
C. Users are forced to select from the existing list
D. It prevents any data from being entered
Q6. What is the consequence of setting "Limit to List" to No?
A. Users will not be able to enter duplicate entries
B. Users can type in any value, even if it's not in the list
C. Entries will be automatically corrected to known values
D. All entries will be ignored if not predefined
Q7. What potential user action does the combo box design allow for?
A. Create reports based on selections
B. Prevent users from modifying data
C. Warn users about invalid entries
D. Allow users to type their own values
Q8. What is suggested as a way to group related database items for easier access?
A. Use color-coded tags
B. Name the queries starting with a common word like "Country"
C. Create separate databases for each category
D. Use advanced search algorithms
Answers: 1-C; 2-B; 3-B; 4-B; 5-C; 6-B; 7-D; 8-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 TechHelp tutorial from Access Learning Zone focuses on part three of my field value substitution series. I'm Richard Rost, and today we're tackling the issue of managing known and unknown misspellings of country names in Microsoft Access. If you're not caught up, I suggest watching parts one and two before continuing with this lesson.
At this stage, we've got a solid setup with our table and two queries. One query corrects known misspellings, while the other highlights unknown ones. This is particularly useful for periodic reviews, such as monthly checks or data imports. However, how do we handle this on a day-to-day basis?
For daily operations, we want the users to select from a predefined list while still being able to input their own entries if necessary. A text-based combo box is perfect for this. It allows users to choose from a list of items without being bound to an ID field; it simply stores text. Though it's not the usual way I use combo boxes (which are typically bound to ID fields), it's suitable here since we're not aiming for a standard relational database setup.
To achieve this, we'll replace the current country text box with something more sophisticated. First, we'll create a simplified list of known countries, eliminating duplicates. To do so, we use an aggregate query that helps us extract a unique set of country names, removing repetitions like multiple entries of USA.
In Query Design, link to your country table, but exclude misspellings. Set the query to Totals or Aggregate mode, grouping by the country name. Running this will generate a list containing each country only once. Save this query as "CountryUniqueQ". By labeling everything related to countries with "Country", it's easy to locate all country-related items later.
Switching to Form Design, locate the Combo Box tool and set it up to pull values from a table or query. Our goal is to use the "CountryUniqueQ", which holds our list of countries. We're only interested in the text field, not any ID. You have the option to sort the list here or directly in the query. After finishing the setup, make sure the combo box is labeled appropriately, and rename it "CountryCombo".
Use a Format Painter to maintain consistent styles and adjust the tab order so the new combo box fits seamlessly in your form's navigation flow. Save your changes, and test by opening the form. Now you can select a country from the list or input new text entries like "Starfleet". The data is stored directly in the Country text field.
This configuration permits user flexibility, allowing them to select from the list or input their own entries. While I don't use text-only combo boxes often, preferring ID-based ones, it's a useful arrangement for specific scenarios. If users input unconventional entries like "Starfleet", you'll have the opportunity to manage these through your misspelling queries and decide on further actions.
For those who prefer to restrict input to the list, the "Limit to List" property can be set to yes, though this counteracts the flexibility we've established. Our approach gives users freedom while allowing you to review and manage new entries.
In the extended cut for members, we'll explore a method to notify users of unlisted entries and offer options to add them. Silver members and above can access all extended cut videos, while Gold members can download database examples. Everyone can benefit from some free training as well by exploring this additional content. Visit the link to learn more.
That's all for today's tutorial. I hope you found it valuable. Live long and prosper, my friends.
Topic List
Field value substitution for country names
Creating a query for known misspellings
Using aggregate queries to list unique countries
Creating a combo box with a text field
Building a country combo box from a query
Setting up combo box properties
Using Format Painter for consistent design
Adjusting the tab order for form navigation
Using a combo box with text storage in tables
Allowing text input alongside combo box selection
Setting Limit to List for combo boxes
Validating user input against known lists
|
