Quick Queries #83
By Richard Rost
48 days ago
Line Breaks in Rich Text Fields, Using HTML BR Tags In today's Quick Queries video, we discuss why inserting line breaks using VB New Line in Access VBA may not work in rich text boxes and explain how to fix it by using HTML tags. We also cover issues with parsing driver's license barcodes, sorting months in modern Access charts, the two-gigabyte database file size limit, differences between refresh and repaint in Access, limitations with rich text box line counts, legacy 32-bit ActiveX dependencies, and answer various viewer questions on filtering subforms, many-to-many relationships, and other Access tips. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, VB New Line, Access VBA, rich text line breaks, VBA HTML tags, barcode scanning driver's license, barcode field identifier, MID function, SelTop property, SelHeight property, modern chart limitations, X axis month sorting, Excel chart automation, Form.Filter vs record source, multi-select list box VBA, SQL Server Linux, two-gigabyte database limit, Me.Refresh vs Me.Repaint
Intro In today's Quick Queries video, we discuss why inserting line breaks using VB New Line in Access VBA may not work in rich text boxes and explain how to fix it by using HTML tags. We also cover issues with parsing driver's license barcodes, sorting months in modern Access charts, the two-gigabyte database file size limit, differences between refresh and repaint in Access, limitations with rich text box line counts, legacy 32-bit ActiveX dependencies, and answer various viewer questions on filtering subforms, many-to-many relationships, and other Access tips.Transcript Ever use VB New Line in Access VBA and wonder why your text still shows up as one long line in a rich text box? Today, we're going to discuss why and when that might happen.
Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today, we're going to talk about those VBA line breaks in your rich text fields and what you can do to fix them. We're also going to talk about modern chart limitations, the two-gigabyte database myth, refresh versus repaint, old 32-bit ActiveX dependencies, and some other viewer questions from YouTube, my forums, and other places. Let's jump in.
Leading off today, we have a question from Tom in the forums on my website. Tom said he's scanning driver's licenses with a barcode scanner into a Microsoft Access database, and his code worked on one license but not another because the characters seemed to shift a few positions. He suspected there might be hidden characters in the scanned data. He wondered if there was a better way to read the birth date from the barcode.
Here's the data that he's getting. You can see that sometimes there are extra characters in there somewhere. It looks like he's got a cool little database set up to read the date and tell whether the person's legal or not.
Raymond pointed him to my scanning barcodes video, and I've got the barcode seminar and Access Developer 27. I cover barcodes in a lot of different places.
This is a good example of why you don't want to parse barcode data using fixed character positions. Driver's licenses in the US follow a particular standard, and the fields can move around depending on the state or the version of the license. Tom was looking for characters in specific positions, so when the layout changed, everything shifted.
Donald, one of my platinum members and a great help in the forums, suggested a better approach. Instead of relying on a specific position, search for the field identifier. In this case, it's DBB for the birth date. So, you find where that exists, get the MID that will point you to the correct location, and then you just grab the eight characters that follow. That way, it works no matter where the field appears in the barcode.
Bill, another one of my platinum members, also pointed out that these barcodes follow a standard format and the data itself isn't hidden or encrypted. What you're seeing when those characters shift is usually just formatting or separators that might be coming from the scanner, not secret characters inside the license.
The takeaway here is simple. When you're parsing structured barcode data, always look for field identifiers instead of assuming fixed positions.
If you want to learn more, I'll put a link to this thread down below, as well as links to the resources Raymond pointed out. Again, special thanks to all my awesome members for their help in the forums. These guys do a great job.
Next up, we've got William, one of my 15-year students. It's hard to believe I've been doing this so long; I've got students who have been with me for 15 years. My moderator, Kevin, has been with me for 20 years, and Alex even longer. I've been doing this for a few years.
He was building up a long text memo field using new line characters: CHR(10), CHR(13), or VBCRLF, CRLF. Carriage return, line feed, or new line - basically the same thing. There are differences depending on your platform, but we're not getting into that today. Everything kept showing up as one giant line.
Kevin pointed out that when you're using rich text, you have to use HTML tags. He had to put little periods in there; otherwise, the website would actually convert that over to an actual line break. If you've got a regular text box - let me show you. This guy is called status box. If I just come in here and say:
status box = "Hi" & VB New Line & "Richard"
What do you get? Well, you get that: "Hi," blank, "Richard." If you change this guy to a rich text box, let's see what you get now. You get "Hi Richard," all in one line. That's what was fooling him.
What you have to do when you're dealing with rich text is use HTML characters. So this change becomes something like:
status box = "Hi<br>Richard"
Use a `<br>` line break. Familiarize yourself with HTML. Access doesn't support full HTML; it's a subset. You can put colors, bold, and other formatting using this small set of HTML codes. Thanks to Kevin for the solution; that was a good one.
I have a video where I cover rich text. I also have an HTML 101 class that's really old - one of the first classes I did. The audio is horrible (it's from about 2002). If you want to learn the basics of HTML, hyperlinks, and formatting, it's all still valid. HTML hasn't changed much at the core - just added stuff. Access doesn't support all of HTML, but the basics, like font size, bold, italics, hyperlinks, all that stuff, are covered. It's free and on my website, with a link down below if you want to watch it.
Next up is another one from Donald. This is not so much a question as he's sharing a tip. Instead of using DoCmd.GoToRecord, which I use often, you can use the SelTop property to jump directly to a record. It can be simpler and sometimes cleaner because you don't have to deal with all those commas in the GoToRecord command.
Donald posted an example. This is one of those old-school Access properties; it's been around forever, but it doesn't get talked about much. SelTop lets you control which row in a continuous form or a datasheet is at the top of the selection. If you set SelTop to 5, it'll jump to the fifth record. If you pair that with SelHeight, you can select multiple rows starting at that position.
Most of the time, you use DoCmd.GoToRecord because it works everywhere and people are familiar with it. SelTop can be nice if you're working with those other types of forms and want to select multiple records. Set SelTop = 3 and SelHeight = 3, and it'll select those three records. Not a lot of use for it, but it's just one of those cool little commands that's in there - another one to put in your toolbox.
If you want to see a full video on this, let me know. The only thing I remember using it for was a database where I wanted to select groups of five records at a time. I believe there's also a SelLeft and Right function or something; it's all in Microsoft documentation. There's SelTop, Left, Height, and Width, so you can select columns in a datasheet, which I don't think I've ever done before. Have fun, play with it, and see if you like it.
Next up, we've got Brent, one of my gold members, who is working with the newer modern charts in Access. He had a clustered column chart comparing revenue by month for two different years. The problem was that to keep the months sorted correctly, he had to include the month number under the month name on the X axis. What he really wanted was just to show January, February, March, and so on, but keep them in the proper calendar order.
Kevin provided some assistance. He put together a chart, and you can see it's set correctly. This is one of the frustrations with the newer modern charts: in order to sort months correctly, you usually need a numeric month field like MonthNum or something in the data source. Otherwise, Access will sort the month names alphabetically, which is not what you want.
Unfortunately, the modern chart control doesn't let you use numeric fields for sorting and hide it on the axis label. The typical workaround is what Brent did: keep the month number in the data so the chart sorts correctly, even if it shows up on the label.
Another approach is to restructure your dataset so the chart only sees the month name for the label but uses the underlying date or month for sorting in the query. Sometimes that works depending on how the chart is bound, but honestly, the chart tools in Access are still a bit limited. That's why many of us export charts to Excel for more control. Access is fantastic for storing data and building the data set, but Excel still has much more powerful charting tools.
Hopefully, Microsoft continues to improve the modern charts in Access, because right now they're still a little rough around the edges. That's one of the reasons I've intentionally held off on making more videos on modern charts, since the Access team is still working on them. They're getting better, adding new features, but Excel is still far better.
Raymond asked if you could use Excel charting with VBA and still display it in Access. You can use Excel's charting engine from Access with VBA, but the easier way is to automate Excel from Access - open an instance of Excel in the background, push data to it, and then use Excel's chart objects. To display that back in Access, you can save the chart as an image out of Excel and display it in Access. I've done that before, but it's not as easy as it should be. There should be a clean way to create an Excel chart and show it in Access, but it's not quite there yet.
I've done quite a bit of work automating Excel charts from Access VBA. If you're really interested in that, let me know, and I can put together some lessons on it. It will be developer-level content; it's not easy, but it is doable.
Next up, going back to rich text boxes. Another Gold member, Gordon, asked how you limit the number of lines in a rich text box. Unfortunately, you can't really do that. Access doesn't give you a reliable way to limit the number of visible lines in a text box, because you have word wrapping involved. Access decides where those line breaks occur based on the control's width, font, font size, and rich text formatting. The exact number of lines that appear can change even if the text stays the same.
Alex noted that you can count characters, but you can't count lines. The only time you can actually count lines is if the user presses Enter and inserts a line break. In that case, you can count the lines based on those breaks. For normal word wrapping, Access handles that automatically, and there's no dependable way to say "only allow five lines," unless you use a monospace font and know exactly how wide the field is. If you use a monospace font like Courier New where each letter is the same width, you can size the box to be exactly, for example, 40 characters wide, and then count the number of characters per line, looking for line breaks.
There are ways to handle this if you're dealing with a monospace font, which might not look the best, but gives you that old school DOS prompt vibe. Remember WordPerfect 5.1 for DOS? I loved that application. You needed the little keyboard cutout to remember all the function control shift keys. That was before the help pop-up. Anyway, I digress. I'm old.
After all that, Gordon decided he was going to move the rich text box into a report or use grow and shrink. I've done a video on how you can use a report inside of a form if all you want to do is view content. If it's only for display, you can do some cool stuff with formatting to make it look good, providing users with a better editing experience.
Quick interruption: If you're enjoying my videos, make sure you're subscribed. Hit that subscribe button and give me a like, even if you watch all my videos. Hitting the thumbs-up button helps YouTube know that you like my videos and pushes them to more people. It helps keep these videos free, and I appreciate it.
Let's head over to YouTube. Robert asked a good follow-up on the subform filtering video. He said he usually changes the record source of the subform with a filtering SQL statement, and wondered if using Form.Filter instead would be better or more efficient.
Both approaches are valid and which one you use depends on what you're trying to accomplish. If you change the record source of the subform, you're rebuilding the data set - telling Access exactly what records to load, and the user only sees that data. They can't easily turn the filter off or modify it unless you give them the tools. This approach is good when you want tight control over what the user can see.
Using Form.Filter like I showed in the video works differently. The form retains the full record source, and the filter just limits what records are displayed. Users can interact with the filter - they can remove or modify it and apply additional filters if allowed by the interface.
It's not about efficiency; it's about behavior. If you want strict control, modify the record source. For flexibility and user interaction, use Form.Filter. Both are useful, and you'll use both in different situations.
The point of these TechHelp videos is for me to show you the different tools available to you. If you want to set up a subform and give the user the ability to filter further, they can. Whereas changing the record source gives you strict control over what users see. The only time it makes a big difference is if you're working with SQL Server, as then you can tell the server to update the record source and send fewer records down the line, which can be more efficient. For a local Access database, it's not a big deal.
Next, Carl left a comment on the subform filtering video saying he prefers using a list box instead of a subform in those situations. That's actually a common design preference if you're building navigation or selection tools. The subform lets you manipulate data, make changes, and change the filter, so there's more flexibility compared to a list box. It's all about what you want to see.
If you want to use a list box instead of a combo box to do filtering, there are some differences: with a combo box, you can type into it; with a list box, you have to scroll. However, a list box offers multi-select, allowing users to choose several items at once if you know how to code for it. It requires VBA to loop through selections. I cover multi-select list boxes in the extended cut for this video and also in Access Developer 15, Lesson 3, where I show how to pick multiple states and filter based on them.
Next up, ShadowDragon left a comment on the SQL Server for Access users video, asking if I could show how to set up SQL Server on a Linux server instead of Windows, which I mentioned is possible. It's a fair question. SQL Server does run on Linux now. The main reason to choose this route is cost - no Windows licensing fees. For most Access developers, especially in small businesses, a Windows machine is usually simpler. SQL Server installs easily on Windows, and the management tools just work out of the box. The whole ecosystem is designed around Windows.
Running SQL Server on Linux makes more sense in larger environments where there's already a Linux infrastructure and Linux-experienced admins. For the typical Access frontend with SQL Server backend, Windows is usually the path of least resistance (and my area of expertise). That said, if enough people are interested, I could put together a video on installing SQL Server on Linux. However, I haven't worked with Linux regularly in about 10-15 years, so I'd have to refresh those skills first. If that's something you'd like to see, let me know in the comments - if there's enough demand, I'll add it to the list.
If you've only got one machine to run SQL Server, just use a Windows 11 workstation - a $200 laptop can handle ten or fifteen users as a database server. If you're managing 50 servers, then running Linux can save a lot on Windows server licensing costs. But it brings more setup and administration headaches unless you're familiar with Linux. I'm good at learning new things, so give me a book and a few days and I'll figure it out.
Next, Pat left a nice comment on the many-to-many relationships video. He said he noticed that I manually built the tables in the example but didn't explicitly create relationships or queries that tie everything together, which is where a lot of real complexity usually arises. That's a good observation - the relationships and queries are where many-to-many setups really come together.
In my TechHelp videos, I usually focus on one specific concept and try to keep the lesson short and targeted. In that video, the goal was just to explain the structure of a many-to-many relationship and the idea of a junction table between two tables. The deeper stuff, like building related queries, enforcing relationships, and handling the interface, is covered more in my full courses.
Also, as a matter of design style, I don't always create system-level relationships in the relationships window unless they're really necessary. Often, I prefer handling joins directly in queries and controlling the workflow through the front end. System relationships can be useful for enforcing things like referential integrity, but they can also introduce constraints that don't always fit every application. Sometimes I keep the table flexible and manage behavior through forms, queries, and VBA.
But you're correct: once you move beyond the simple example, the queries and relationships are where the real work happens. If there's something specific you'd like to see, let me know with a comment below.
Next up, we've got Nigel, who commented that Microsoft really needs to increase the maximum database size in Access beyond the current two-gigabyte limit. He also mentions that he prefers to keep AI far away from his data.
The two-gigabyte limit has been around a long time and isn't likely to change soon. Access is built on the Jet and Ace database engine, which was designed as a file-based system - everything in a single file. The architecture was built with that size boundary in mind, so increasing it would require major changes to the storage engine.
The important thing people often miss is, although that's the limit per file, it's not the limit for your entire Access application. Each ACCDB file can be up to two gigabytes. So, if you have large datasets, split the tables across multiple backends and link them together. Access handles linked tables well, so the user sees it as one system. I have databases where orders, customers, and contacts are in separate backend files - each can be two gigabytes.
In my experience, people only hit the two-gig limit when storing things like embedded images, attachments, OLE objects, or other binary files - two gigabytes of actual text is a huge amount of information. In 30 years, I've only seen the limit hit when stuffing non-text data into the database. I had a client storing MP3 files as attachments - he ran out of space quickly. Don't store MP3 files inside an Access database.
As for AI, that's personal preference. Some people avoid it, and that's fine. Personally, I use it for analyzing text, organizing information, and prioritizing tasks. Modern AI tools can understand context and meaning. For example, if I'm short on time, I can ask the AI to look at my task list and find something high impact that will take less than ten minutes.
You don't have to give AI control of your database, although that's something I'm working on as well. Just reading and processing the data is helpful. I use AI for customer service too - about 80 percent of questions I get by email have been answered before, so AI drafts replies for common issues, saving me time.
I'm considering a full course on using Microsoft Access with AI because there's so much you can do - generating images, analyzing data, and more. Let me know if you'd like to see a full course on that; I've done a few videos so far, but a full course may be on the way if there's enough interest.
Next, Jim asked about changing a form's control special effect in VBA, based on whether a number is positive, negative, or zero. He's got the code working, but the special effect doesn't update right away - it only updates after moving away from the record and returning. Usually, Me.Refresh isn't the right solution there. Refresh loads data from the table; it doesn't update the form's appearance. For something like changing special effects, you want Me.Repaint, which tells Access to redraw the form, so visual changes like borders, highlighting, and special effects show up immediately.
Also make sure the code is running in the right event - if you're changing special effect based on a field value, put the logic in the control's AfterUpdate event and also in the Form's OnCurrent event, so it updates properly when users move through records. Refresh is for data; repaint is for the screen. Conditional formatting would be nicer if it had more options than just color and font.
Next, Xavier commented on the continuous forms totals video, mentioning that sometimes he uses something like =Sum([Credit]*2) instead of creating a calculated field in the query. That approach can work sometimes. Access will allow you to sum an expression directly in the form footer if the calculation is simple and based on existing fields. For example, =Sum([Price]*[Quantity]) will work if those fields exist in the data. This breaks down with more complex calculations or if you depend on values that exist only as form-level calculated controls. Once you're referencing controls instead of fields in the recordset, Access can't reliably aggregate them in the footer.
That's why the safest, most reliable approach is to move the calculation into an underlying query. Once that calculated field exists there, Access treats it like any other field and can total, group, sort, or reuse it in forms or reports without errors. So try summing an expression directly - if it works, great; if not, move it to the query.
Next, RTAT1 commented on the 64-bit Access video. He said he normally agrees with moving to 64-bit, but has one machine running 32-bit Office because it relies on an old ActiveX control, MSCOM32.ocx, for barcode scanners. This is a common reason to stick with 32-bit Office. Many older Access systems relied on legacy ActiveX controls for serial communication, and these were never updated for 64-bit Office. Sometimes the original developers are long gone. I even know people who keep a Windows 95 machine in the office (with software that only runs on Windows 95) rather than pay $30,000 to upgrade machine software.
If you have a system depending on a legacy control, keep it on 32-bit Office until you're ready to replace that component. Just be sure you've got good backups in case you need to restore it. The long-term fix is replacing dependency on the old OCX. Many barcode scanners now act as keyboard input devices and need no special controls, as shown in my videos. There are also .NET libraries and third-party components that handle serial ports and work in 64-bit environments, but that usually means rewriting part of the application.
You're doing the right thing: if that one system needs a legacy control, keep it on 32-bit Office until you can modernize. I've fought upgrading to 64-bit myself, especially as Microsoft initially said 32-bit was fine unless you needed more; eventually, their opinion changed to recommend 64-bit for everyone. The jump happened eventually, and most equipment transitioned. Still, clients keep one old machine around for old software.
Once again, if you haven't liked and subscribed, hit that button now and make sure you get on my mailing list so you get notified about new videos.
It's almost here: in two weeks, March 27th, I'll be in the Redmond, Washington area. I've got my plane tickets, hotel, and rental car all set. There are some cool presentations, and you can meet some of the Access team at Microsoft. I'm attending, not presenting, but I went last year and it was a lot of fun.
Check out my website to see what's new - I post thoughts in my Captain's Log (not many this week due to being under the weather). I also wrote an article about why I hate talking on the phone - don't try to call me! Stop by the merch store, pick up a hat or T-shirt, and wear it to Access Day. I'll shake your hand.
I always wear my Access Learning Zone shirt or hat when I travel. So far, no one has ever said "hey, you're that Access guy" in the airport - still waiting. Grab a copy of my book on Amazon. I'm adding my SQL Server Beginner Level 1 book soon, too - just have to write it.
Today we learned that if you're inserting line breaks into a rich text field with VBA, don't use VB New Line. It won't work well; use HTML for line breaks. We also talked about using repaint instead of refresh to redraw the form, why the two-gigabyte Access limit usually isn't a real problem, and a few other tips from your questions.
Post a comment below and let me know how you liked today's video - and how you're using these tricks in your database. Also, send any questions you'd like to see covered in next week's Quick Queries.
That's your Quick Queries video for today, brought to you by AccessLearningZone.com. I hope you learned something. Live long and prosper, my friends. I'll see you next time and enjoy your weekend.Quiz Q1. Why do VB New Line characters like VBCRLF or CHR(13) not create line breaks in Access rich text controls?
A. Because rich text controls require HTML line break tags like <br>
B. Because Access does not support line breaks at all
C. Because VB New Line only works in reports
D. Because rich text boxes are limited to plain text only
Q2. When parsing barcode data from driver's licenses in Access, what is the recommended approach?
A. Use fixed character positions to extract data
B. Search for field identifiers such as DBB and parse after them
C. Convert the data to a number and parse digits
D. Assume every state uses the same format
Q3. What is a common issue when trying to limit the number of visible lines in an Access rich text box?
A. You cannot set font color
B. Word wrapping makes line counts unreliable
C. Access does not allow limiting lines at all
D. It always shows only one line regardless of content
Q4. What should you use to immediately update the appearance of a form when changing special effects in VBA?
A. Me.Refresh
B. Me.Update
C. Me.Commit
D. Me.Repaint
Q5. What is the key difference between using Form.Filter and changing the Record Source for filtering subforms?
A. Record Source allows user interaction with data, Filter does not
B. Filter removes data from the database
C. Filter lets users modify or remove it; Record Source restricts what users can see
D. Both work identically in all contexts
Q6. Why do modern Access chart controls typically require a numeric field such as MonthNum for sorting months on the X axis?
A. So charts appear in alphabetical order
B. Access doesn't sort months by name in calendar order without it
C. Numeric fields are required for all chart axes
D. Month names are not supported on Access charts
Q7. What is the main takeaway when working with the Access database size limit of two gigabytes per file?
A. You can never exceed two gigabytes for your entire application
B. Split tables across multiple backend files to work around the limit
C. Only text data counts toward the limit
D. The limit can be changed in Access settings
Q8. If Access sums in a form footer are not working as expected with calculated values, what is the best troubleshooting step?
A. Use Me.Repaint after each calculation
B. Move calculations into the underlying query
C. Add a filter to the form
D. Change the form to datasheet view
Q9. When would you typically maintain a 32-bit version of Office in your environment according to the video?
A. When you have modern hardware
B. When using old ActiveX controls like MSCOM32.ocx not supported in 64-bit
C. For improved database speed
D. If you want to use larger databases
Q10. What is the SelTop property in Access used for?
A. To repaint the form
B. To jump directly to and select a specific row in a datasheet or continuous form
C. To filter data by top records only
D. To set a form's font size
Q11. What is a reason for exporting charts from Access to Excel, as discussed in the video?
A. Access does not support charts at all
B. Excel offers more powerful and flexible charting tools
C. Access charts are always read-only
D. Excel is required for any data visualization
Q12. In Access, is it possible to count visible lines in a rich text box reliably?
A. Yes, by counting all characters
B. Yes, Access tracks visible lines exactly
C. Only if user-entered line breaks are present and using a monospace font
D. No, it cannot be done even with custom VBA
Q13. What is the best practice for integrating AI with Access, according to the video?
A. Avoid AI entirely
B. Use AI for text analysis, prioritizing tasks, and customer service
C. Let AI fully control your data and database updates
D. Only use AI on backend servers
Q14. Why does the instructor prefer using queries for enforcing many-to-many relationships rather than always relying on relationships in the relationships window?
A. Queries are faster than tables
B. System-level relationships can introduce constraints and are not always necessary
C. The relationships window is obsolete
D. Queries prevent users from entering invalid data
Q15. What is the typical design difference between using a list box and a subform for navigation or selection?
A. List boxes allow multi-selection and subforms allow data manipulation
B. Subforms do not display data
C. List boxes cannot be used for filtering
D. Subforms do not support any formatting
Answers: 1-A; 2-B; 3-B; 4-D; 5-C; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-C; 13-B; 14-B; 15-A
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 In today's Quick Queries video from AccessLearningZone.com, I want to address several questions and tips that have come from students and viewers about Microsoft Access, especially focusing on common issues with VBA line breaks in rich text controls. Along the way, I cover modern chart limitations, common database myths, record navigation methods, ActiveX dependencies, and more. As always, these topics often come straight from your questions in the forums, YouTube comments, and emails.
Let's start with a problem Tom brought up on the forums. Tom uses a barcode scanner to input driver's license details into an Access database. Everything worked fine until he noticed that scanning one license shifted the data differently than another. He guessed he might be dealing with hidden characters in the barcode data, which made extracting the birth date unreliable when using fixed character positions.
The key takeaway here is that parsing barcode data based on fixed positions is risky due to variations in how the data is formatted. Driver's license barcodes use standard formats, but fields can appear in different positions based on the state or license version. Instead of extracting the birth date by a position in the string, you should locate the field identifier (for example, "DBB" for the birth date) within the scanned data. Once you find "DBB," you can reliably grab the eight characters that should follow, no matter where it appears in the barcode. This approach makes your code more adaptable and immune to minor changes in barcode layouts.
Donald, another active member in the community, expanded on this solution by pointing out that the barcode data is not encrypted or hidden; discrepancies are usually due to formatting set by the scanner or the way fields are separated. Remember, always identify and search for structured field tags rather than assuming a particular position.
Moving on, William, a long-time student, asked about building up a long text (memo) field with VBA by inserting line breaks using the usual newline characters like Chr(10), Chr(13), or VBCRLF. He found that when using a rich text box, all his text appeared in a single line, instead of being separated as expected.
The answer lies in the distinction between plain and rich text controls. When working with rich text, standard newline characters are ignored. Rich text boxes in Access interpret text as HTML, so you need to use HTML line break tags, like "<br>," instead of VB's newline characters. That way, your line breaks will appear correctly. This is a great example of how understanding the formatting expectations of different control types is essential when building forms.
Access supports a subset of HTML tags for rich text fields, letting you apply some formatting such as bold or color, though not the full spectrum of HTML. If you're new to HTML, I recommend checking out my HTML 101 tutorial. Though the audio is a bit dated, the core concepts remain relevant because the basics of HTML haven't changed.
Donald also contributed a tip about navigating records. Instead of always using DoCmd.GoToRecord, you can use the SelTop property to move the selection directly to a certain record in a form view. This method lets you, for example, automatically scroll the form so a specific record is at the top, or even select a block of records using SelTop along with SelHeight. For specialized navigation tasks, especially with continuous or datasheet forms, this can be a handy tool.
Brent had a question about the modern charts feature in Access, particularly how to keep months in calendar order rather than alphabetically on a chart's axis. The crux of the problem is that Access tends to sort text fields alphabetically unless you provide a numeric sort field, like MonthNum. However, the charting tool typically displays both the name and number, which isn't always desirable visually. You can sometimes work around this by structuring your data source so that sorting happens by the numeric field but labels only show the month name. However, the current chart controls in Access lack the flexibility and advanced features you get in Excel. For more complex charting needs, I often automate Excel from Access, pushing data into Excel and generating charts there, then displaying them as images back in Access - though it's not a seamless experience yet.
Gordon, another Gold member, asked whether it's possible to limit the number of lines in a rich text box. The answer is not straightforward, since word wrapping is managed by Access and depends on control dimensions and font settings. You can count and limit lines only when users manually insert line breaks. If you really must enforce such a limit, using a monospace font to calculate line width and break points can help, but that's rarely an ideal solution. For display-only scenarios, moving the text content into a report with controlled sizing may be more effective.
Switching over to some YouTube discussions, Robert asked about the difference between filtering a subform by changing its record source versus using the Form.Filter property. Changing the record source essentially redefines which records are loaded into the subform, giving you strict control over visibility. Using the Filter property, on the other hand, temporarily restricts what records are displayed, and allows the filter to be changed or removed by the user with the right interface. Both tools have their place, and which to use depends on your needs - tight security or user flexibility. For local Access tables, efficiency isn't much different between the two, but if you're working with a SQL Server backend, modifying the record source can be more efficient since it limits data transmission.
Carl commented that he sometimes prefers using a list box over a subform when filtering, especially for navigation purposes. List boxes allow for multiple selections, but require a bit of VBA to build multi-select logic for filtering. I've covered this both in the extended cut of my subform filtering lessons and in my full Access Developer course. Combo boxes provide typing and quick selection while list boxes offer multi-select - choose whichever suits your application's needs best.
ShadowDragon asked about running SQL Server on Linux instead of Windows. SQL Server is available on Linux now, which can save on licensing fees and is useful if you're managing many servers in an environment already centered around Linux. For small businesses or individual developers, Windows is often easier, thanks to its out-of-the-box management tools and simpler setup. If there's enough interest, I can cover installing SQL Server on Linux in a future lesson, although it's not my regular working environment.
Pat observed that in my many-to-many relationship video, I focused on building tables but didn't spend as much time showing queries and relationships. That's deliberate - I keep TechHelp videos focused on one main concept, leaving deeper workflow and query design for full course materials. Often, I manage referential integrity and joins directly in queries and forms, rather than using system-level relationships, to keep things flexible for more complex front-end apps.
Nigel mentioned that Access's two-gigabyte database file limit seems restrictive. This limitation is deeply rooted in Access's underlying engine, and probably won't change soon. However, there's no reason your overall system needs to stop at two gigabytes - just distribute your data across multiple backend files and link them. The limit becomes a real problem only when storing unnecessary binary data like attachments or images directly in the database. For text data, reaching the cap is rare. Manage your data structure wisely, and this limitation shouldn't hold you back.
On the subject of AI, Nigel prefers not to use it with his data, while I find AI useful for automating routine communication and analyzing information. If there's interest, I may offer a course on integrating AI with Access for data processing and workflow enhancements.
Jim asked why changing a control's special effect in VBA doesn't always update immediately. The difference comes down to knowing when to use Me.Refresh (which reloads data from the underlying table) versus Me.Repaint (which simply redraws the screen). For visual changes like special effects, use Repaint and ensure your code is running in appropriate events like AfterUpdate and OnCurrent.
Xavier pointed out that it's sometimes possible to sum calculations directly in the form footer using expressions like =Sum([Credit]*2), but this only works if all the referenced fields are in the data source. For more complex calculations or if referencing a calculated control, it's best to create an explicit calculated field in the underlying query for consistency and reliability.
RTAT1 shared that he keeps a version of Office 32-bit running specifically because an old barcode scanner relies on an obsolete ActiveX control. There are plenty of legacy systems out there where specific old controls or software just won't work in 64-bit. In those cases, hold off on upgrading until you can transition away from those dependencies. Newer scanners often work as simple keyboard input without extra drivers, and modern code libraries are available, but upgrading does sometimes require rewriting application code.
As a brief aside, I'll be at Access Day in Redmond, Washington at the end of March. It's always a good event if you're in the area and want to meet other Access developers or some of the Microsoft team. Also, be sure to check my website for new articles, merchandise, and upcoming content.
To sum up, when working with VBA line breaks in rich text controls, remember not to use standard VB newline codes - use HTML tags for line breaks. We also covered issues like using Me.Repaint to update form visuals, the nuances of Access's size limitation, subform filtering strategies, and alternatives for charting and legacy control dependencies.
If today's topics were helpful, leave a comment with your thoughts or any new questions you'd like to see addressed next week. 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 Parsing barcode data using field identifiers
Handling hidden or extra characters in barcode scans
Difference between fixed position and field identifier parsing
Using HTML line breaks in Access rich text boxes
Building multi-line text in Access VBA for plain and rich text boxes
Access support for a subset of HTML in rich text controls
Using SelTop and SelHeight for record navigation and selection
Modern chart sorting limitations in Access
Sorting months chronologically in Access charts
Automating Excel chart creation from Access VBA
Limitations in restricting visible lines in rich text boxes
Counting lines and characters in Access text boxes with word wrapping
Comparing subform filtering by record source versus Form.Filter
List box versus subform for record filtering
Multi-select functionality in Access list boxes
SQL Server backend considerations for Access applications
Splitting Access data across multiple back-end files for size limits
Differences between Refresh and Repaint in Access forms
Using Me.Refresh and Me.Repaint for data and UI updates
Conditional formatting limitations for controls in Access
Summing calculated expressions in form footers
Reliability of calculated fields in queries versus form controls
ActiveX control compatibility between 32-bit and 64-bit Office
Transitioning barcode scanner integrations from legacy ActiveX controlsArticle If you have ever tried to insert line breaks into a Microsoft Access Rich Text Box using things like VB New Line, CHR(10), CHR(13), or VBCRLF in your VBA code, you may have noticed that your text still appears as one long line instead of breaking where you expected. This is a common point of confusion, and understanding why it happens can save you a lot of frustration when working with text formatting in Access.
The key reason is that a rich text box in Access expects line breaks according to HTML, not the traditional plain text line break characters. In a standard text box, when you use code like:
statusbox = "Hi" & vbNewLine & "Richard"
the result displays correctly with "Hi" on one line and "Richard" on the next. This happens because the plain text box recognizes VB's line break characters.
However, if you change this control to a Rich Text box and use the same code, you will see "Hi Richard" shown all on one line. The rich text box is built to handle a subset of HTML, so it does not interpret CHR(10), CHR(13), or VBCRLF as an instruction to start a new line. Instead, it relies on HTML tags for formatting.
To correctly insert a line break in a rich text box via VBA, you need to use the HTML break tag like this:
statusbox = "Hi<br>Richard"
This will display "Hi" and "Richard" on separate lines within the rich text box, as intended. If you are going to create more complex text with formatting - adding colors, bold, font size, or hyperlinks - you will need to become familiar with the subset of HTML that Access supports. While it does not implement all HTML features, it allows basic tags for line breaks, bold, italics, and hyperlinks.
For example, if you wanted to display text with a bold heading and two lines below it, you would write:
statusbox = "<b>Header</b><br>This is line one.<br>This is line two."
Remember, regular line break characters do not work for breaking lines in rich text controls. Always use the correct HTML tags for formatting within these controls.
If you want to count or limit the number of lines in a rich text box, unfortunately, Access doesn't give you a reliable way to do this due to word wrapping and formatting. Variations in control width, font, and HTML tags affect how many visible lines there are. You can count line breaks only if the user enters them manually (for example, by pressing Enter), but you cannot count visual lines produced just by word wrapping.
Another question comes up frequently about modern chart controls in Access: keeping months in calendar order instead of alphabetical order. The usual approach is to add a numeric field representing the month number (for example, 1 for January, 2 for February) in your data source. You can use this field for sorting while using the month name for labels. Unfortunately, modern charts still require workarounds like this because they lack advanced sorting and labeling features found in Excel charts.
Speaking of charts, you can use Excel's charting engine via VBA from Access. The challenge is displaying Excel charts inside Access forms. The common solution is to automate Excel from Access, generate the chart in Excel, export it as an image file, and then display that image within Access. It works but isn't as seamless as using Access-native controls.
When filtering a subform in Access, you can change its record source or use the Form.Filter property. Changing the record source gives you strict control over which records are loaded and is good for security or restricting data. Using the filter property is more flexible, letting users remove, change, or add filters at runtime, but it still loads all data based on the original source before filtering is applied. Performance-wise, if you are working with SQL Server backends, modifying the record source may be more efficient, as it reduces the data transferred across the network.
If you are dealing with old ActiveX controls that only work in 32-bit Access, such as for use with barcode scanners, it's common to stick with 32-bit Office for that machine. Many legacy controls are not available in 64-bit Access. The recommended approach is to keep those systems on 32-bit until you can migrate to modern code or hardware, and if possible, replace the old controls with newer, supported approaches.
Another useful tip is about using the SelTop and SelHeight properties to programmatically select records in a datasheet or continuous form view. Instead of DoCmd.GoToRecord, which moves the cursor to a specific record, setting SelTop allows you to control which record is at the top of the selection, and SelHeight determines how many records are included in the selection. For example, this code:
Me.SelTop = 5
Me.SelHeight = 3
will select records 5 through 7 in the current form.
When it comes to the two-gigabyte database size limit in Access, remember that it applies per ACCDB file. By splitting your data into multiple backend files - for example, storing customers, orders, and contacts in separate databases - you can work around this limit. In practice, most users only reach the size limit by embedding large attachments or images directly into the database. For text data, the limit is quite generous.
If you want to update the appearance of a control immediately in VBA, such as changing the special effect property when a value is positive, negative, or zero, make sure to use Me.Repaint rather than Me.Refresh. The Repaint method redraws the form and its controls, immediately reflecting visual changes, whereas Refresh reloads data from the underlying table but doesn't force a visual update.
For more advanced features like multi-select list boxes, you can use VBA to loop through the selected items and filter your records based on user choices. This technique is covered in depth in resources about Access VBA and control programming.
Lastly, if you are interested in using AI tools with Access, there is growing potential for automating analysis, generating reports, or assisting with repetitive tasks. While Access itself is not AI-enabled out of the box, you can integrate with external AI services or use VBA scripts to automate interactions with other tools.
In summary, when working with rich text boxes in Access, always use HTML tags such as <br> for line breaks - plain text line break codes will not work as expected. For other Access development tasks, understand the limitations and capabilities of the controls and techniques you are using. Keep your application architecture flexible, be aware of size and compatibility limitations, and look for modern approaches where possible. If you continue to learn new techniques and best practices, you will build more reliable and maintainable Access applications. |
