Now that you can see your code and the line that might be causing the problem, it's time to investigate. The error "Object variable or With block variable not set" is a clue, but it doesn't tell you exactly what the problem is. The first step is to check the current values of the variables near the line that caused the error. Remember, your code is suspended, so all your variables are intact and able to report their values.
The quickest and easiest way to determine the value of a variable is to hover your mouse pointer over the variable name in the code window when your code is suspended. However, if the variable is part of a longer phrase, hovering may not work. For example, the variable Me.BusinessKey is simple enough to be hoverable (see Figure 8-6).
Since BusinessKey has a reasonable value, it doesn't seem to be the problem. In order to check variables or objects that are part of a more complex statement, you need to highlight the portion you are interested
before you hover over it. In our example, just hovering over the object name "db" doesn't display anything, but after selecting it the hovering will give you a value, as shown in Figure 8-7.
By checking the value of db, you can see that it is currently set to Nothing. This is Access's way of telling you that the db object reference hasn't been set to any value yet. Sure enough, when you look at your code, you can see that although you defined db using the line Dim db as Database, you forgot to include the line Set db = CurrentDB. Adding this line before the OpenRecordset line will resolve the problem.
Was this article helpful?