I am learning so any suggestions would be appreciated on a level that a beginner can understand. I am trying hard.
OK, lets talk about declarations and assignments and references. 'scuse me while I put on the "teacher hat."
1. When plog says "you don't declare" he is telling you that a particular variable has no visible
declaration. A declaration is most often a DIM or PUBLIC or PRIVATE statement followed by a variable name followed by AS and finally followed by a valid data type. A couple of other keywords exist, but those are the "big three." That declaration allocates space for a variable of the given type.
If you do not use
Option Explicit at the head of each module and DO reference a variable that has not been declared (see section 3 for "references"), that variable comes into existence anyway, as a data type of VARIANT - which is the only data type that can be NULL in value. When you have a declared variable, you have TWO pieces of information - its data type and a value that will be consistent with that data type. When you have an undeclared variable and no Option Explicit, you have a name but no data type. That data type is incredibly important.
If you declare something, there are three ways to make it have a value. The 2nd way is if you call a subroutine with an argument passed by reference, where the subroutine could internally assign a value (see 2nd section) and then pass back that value. The 3rd way is described later in the 2nd section. The 1st way to give it a value is to include an initialization value. Here is a simple example of declaration with an initial value:
PUBLIC PI AS DOUBLE = 3.141592653589793
That creates a public variable named PI as a scientific 64-bit number and initialized it with a 16-digit value. The reason I'm jumping on this point is that if you don't use Option Explicit and then perform an undeclared variable creation, you get a variable which is a variant that, depending on how it is used, will have a value of NULL, 0, "" (zero-length string), or NOTHING (an unassigned object). How do you know what type you will get? See section 3.
2. When plog says "you don't set" something, he is talking about value
assignment. An assignment is simply a statement involving a variable, an equals sign, and any expression that can be evaluated to produce a value - a single value (not data type SINGLE, but single meaning what in math is called a SCALAR, or single-valued, expression). When the expression's evaluation is complete, the statement copies, or assigns, that value to the variable, allowing for the possibility that it might have to change the data type if the expression produces the wrong data type. This type conversion is why declarations matter.
If your expression computed a scientific number but you wanted a counting number (BYTE INTEGER, INTEGER, or LONG INTEGER), VBA will convert the number to the right data type if it can. (But SINGLE and DOUBLE can exceed the range allowed for any of those integers.) If you wanted a scientific number (SINGLE, DOUBLE) but got a counting number, VBA will convert the number to the right data type. (Conversions of any INTEGER to any SCIENTIFIC value always work but CAN lose precision sometimes.)
If you wanted a number but generated a text string, Access will know (by comparing data types) to warn you that something is wrong because text strings are not numbers and this will lead to a run-time error. This is why you ALWAYS want to declare variables with data types. The run-time system can tell if you screwed the pooch on an expression. What's worse is if you created an undeclared variable, that VARIANT will change SILENTLY to whatever data type is required for the expression in which it is used, but it will still have no value - and no warnings.
3. When plog says "you don't use" something, he is saying that you make no
reference to it. This can be based on a declaration of a variable that appears in
neither side of an assignment statement. Technically, an assignment IS a reference to the variable on the left-hand side of the equals sign. But a reference can also occur on the right-hand side of the equals sign. It can also occur in function or subroutine arguments. In each case, you take the value of the variable and feed it into whatever processing is called for.
An unreferenced variable is indicative of having wasted your time computing something you don't use. Unlike undeclared variables and unassigned variables, an unreferenced variable is unlikely to even get a minor error call-out. Access treats such things as "who cares" cases - because obviously if YOU didn't use it, YOU didn't care either. But the most common cases of these are spelling errors or a tricky memory where you called something by some name ... until you forgot and called it something else, thus failing to reference the REAL value you had set aside. So you get the wrong inputs into an expression.
4. Now, that furor over your 10-step CASE statement (actually, 8-step)... If you declare a variable but never assign it, its value is whatever is the default for that data type. Your cases 4 and 9 in your subroutine do not have assignments, so you are using whatever values are currently in the UnitAvailable variable, which is not declared and not passed in as a formal parameter to the subroutine. This leads to what is called a SIDE EFFECT - where an externally imposed value can linger. IF it happens that UnitAvailable comes from a prior declaration outside the subroutine, then if cases 4 or 9 ever pop up, you have leftovers that could lead to taking that UnitAvailable flag from a prior incarnation of that routine - since its value and placeholder were NOT in that routine. You DON'T KNOW in that case where their values originated. I might GUESS that the value you want comes in as FALSE by default - but it would be a GUESS and that is not usually a good programming technique. Guessing is for gamblers.
5. How do you stop this problem of ambiguous or uncertain values in variables? Assure that you ALWAYS use Option Explicit in EVERY module, usually as the very first line of that module. Then the compiler will gig you when you have an undeclared variable. And in the process of cleaning up those undeclared variables, you will discover things that need to have values assigned.
This is where you need to learn how to use one of the most powerful features of Access VBA - debugging. You discover what your code is doing by setting breakpoints and single-stepping through your code and examining values as you go. Tedious - but incredibly important. But that's the subject of a whole different discussion.
<<"teacher hat" comes off>>
