Fixing Append Only Fields, Using TempVars

Welcome

Today's class is going to focus on two major topics. First we're going to learn about the Append Only property which is for long text fields and I'm going to teach you why you shouldn't use it. You may encounter databases as a developer where people have used it. It's quite popular. They're going to ask you how to get rid of it but still keep the column history that's in there. So I'm going to teach you how to do that. We're going to learn how to parse the individual records. We're going to do some string parsing and some Recordset work.

Then we're going to learn about TempVars. What is TempVars? Well, it's yet another tool in your box for storing values in the computer's memory. TempVars are, in my opinion, better than global variables for most cases in that they will actually survive an unhandled error, so that's good. Plus, they'll store data of any type. You can store numbers, dates, string values, anything you want in them. We're going to learn all about TempVars, what they are, pros and cons, and go through some sample use cases.

Resources

• Full Course Lessons Page
• Course Topic Outline

Topics Covered

In Lesson 1, we're going to review the Append Only property, why you should not use it, and what some better alternatives are.

In Lesson 2, we're going to continue with the Append Only property. Now that we know what the Append Only property is and why we shouldn't use it, we're going to deal with the situation where we get a database from someone else who has been using Append Only and we have to extract all of that data out of that field and put it in a normal relational table like our contact table where each one of those entries in the history should be its own record in the table. It's going to involve recordset loops, string parsing, and lots of cool stuff.

In Lesson 3, we're going to learn about TempVars, which is another way of storing data in the computer's memory. We're going to learn what TempVars are, why you want to use them, how to use them, and the pros and cons.

In Lesson 4, we're continuing with TempVars. We're going to learn how to set and get TempVars using VBA. We're going to see how errors in your VBA code, any unhandled errors, will clear a global variable, but it won't clear a TempVar. We'll learn how to read a TempVar from a text box using the .Value property. We'll learn how to use a TempVar in the control source of a field, in a property like default value, in a property like caption in source code, and as a query criteria using the bang (!) operator.

In Lesson 5, we're going to continue working with TempVars. We're going to learn different ways to add TempVars. There's TempVars.Add. There's the other methods we saw before. There's ways to remove TempVars, like when the user logs out you want to remove that value. We'll check to see if a TempVar is set. We will learn two different ways to loop through all of the TempVars that are in memory with a For loop and a For Each loop. We'll learn how to see what kind of variable data type it is. Is it a date? Is it a currency? We'll learn about IsDate and some other related functions.

Enroll Today

Enroll now so that you watch these lessons, learn with us, post questions, and more!

Questions?

Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.

Keywords

microsoft access, access 2016, access 2019, access 2021, access 365, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, Append Only, Application.ColumnHistory, Removing CHR 13, TempVars, .Value Property, TempVars in Queries, .Add, .Remove, .RemoveAll, List all TempVars, Loop thru TempVars, VarType Function, variable type