Two VBA Problems

[cialdea9]

VBA Noob, trying to do a couple quick things. Any help would be great. I'm working in a software that interfaces with Access. Here is the code:

First problem:


dim temp
dim getOut
getOut = 0
dim counter
counter = 1

if (.InitialTest_A8 < .InitialTest_B8) then
if (.InitialTest_A8 < .InitialTest_C8) then
if (.InitialTest_B8 > (.InitialTest_A8 * 1.5)) then
do
temp = ".Deficiency_" & counter
if .Deficiency_1 = "" then
getOut = 1
.Deficiency_1 = "print message here"
end if
counter = counter + 1
loop until getOut = 1
end if

Somehow this results in an infinite loop. But, I have printed the counter instead of the message and it reads 1...

Second problem:

if (.InitialTest_C8 > (.InitialTest_A8 * 1.5)) then

temp = ".Deficiency_" & counter
if eval(temp) = "" then
.test1 = 1
eval(temp) = "print message here"
end if
end if

This is actually going to fit in the first part at some point. But, the main issue is that the second eval(temp) statement is not working. I don't understand why because I use it the first time and the .test1 = 1 statement shows up fine, so I know it is entering the if statement being tested as a string. Any help you guys can give would be awesome!

[zugzwang]

when checking for null try using if isnull(whatever) then

I had a similar problem and isnull fixed it. VBA evaluates = "" differently than ISNULL

[cialdea9]

the problem isn't with the test statement. That goes through fine. It is with eval(temp) = "print message here"
I have combined a string with a integer to create temp and now I want to use it as a variable. I have tried putting cstr(temp) a line before this statement as well with no success.

[hansup]

do
temp = ".Deficiency_" & counter
if .Deficiency_1 = "" then
getOut = 1
.Deficiency_1 = "print message here"
end if
counter = counter + 1
loop until getOut = 1

Somehow this results in an infinite loop.

Each time through that loop, you're checking whether .Deficiency_1 = "". And if it is, you set getOut to 1 ... which exits the loop.

The problem is that if .Deficiency_1 starts out not equal to "", it will never become equal to "" within that loop. Therefore getOut will never be set to 1, and the loop will continue looping forever.

You should get familiar with the debugging features of the code editor. Set a breakpoint, run the procedure, and step through it one line at a time. You can set watch variables, or simply Debug.Print their values in the Immediate Window.

[cialdea9]

Each time through that loop, you're checking whether .Deficiency_1 = "". And if it is, you set getOut to 1 ... which exits the loop.

The problem is that if .Deficiency_1 starts out not equal to "", it will never become equal to "" within that loop. Therefore getOut will never be set to 1, and the loop will continue looping forever.

You should get familiar with the debugging features of the code editor. Set a breakpoint, run the procedure, and step through it one line at a time. You can set watch variables, or simply Debug.Print their values in the Immediate Window.

I'm not sure I completely understand why that is true. I think it does enter the if statement because the message does print once the code times out.

I am familiar with debugging in C (I'm an electrical and computer engineer), but this software has no debugging features at all. It is really a rough development environment. Is there an environment that I can copy this into for development that you suggest?

[hansup]

I'm not sure I completely understand why that is true. I think it does enter the if statement because the message does print once the code times out.

Then there is something about your external software which causes it to operate differently than Access VBA. A loop such as yours would not time out. This one for example:

Dim getOut
Dim counter
Dim MyStringValue As String
getOut = 0
counter = 1
MyStringValue = "foo"
Do
If MyStringValue = "" Then
' or If Len(MyStringValue) = 0 Then
getOut = 1
MyStringValue = "print message here"
End If
counter = counter + 1
Loop Until getOut = 1

When run from within an Access session, that code will loop forever ... no time out ... until you manually interrupt it.


I am familiar with debugging in C (I'm an electrical and computer engineer), but this software has no debugging features at all. It is really a rough development environment. Is there an environment that I can copy this into for development that you suggest?

I don't know now. I was thinking develop the VBA in an Access standard module, and test it within an Access session. However based on what you reported about the time out, I doubt that approach would be suitable.

Maybe someone can offer a useful suggestion if you tell us more about your external software environment which you're using to interface with Access.