Do Loop does not work

[billheath]

I am a newbie and can't decide what I'm doing wrong.
Code:
refsql = "select Quantity_owned, Current_Val, symbol, Dont_record from [Stock_Data]" 'Fields(0) = Quanity, Fields(1)= Current Value, field(2) = symbol
Set rstStkD = CurrentDb.OpenRecordset(refsql, dbOpenDynaset)
C = 0
cv = 0 'Current Value
Tot = 0 'Total
s = "S" 'Symbol
rstStkD.MoveFirst

Do While Not rstStkD.EOF
'If (rstStkD.Fields(3)) = -1 Then

If IsNull(rstStkD.Fields(1)) Then
If rstStkD.Fields(3) = "false" Then

'Debug.Print rstStkD.Fields(3)
msg = "The current sock value is missing for " & rstStkD.Fields(2) & ". Make sure closed stock positions indicate a current value and are not deleted."
X = MsgBox(msg, vbOKOnly)
Exit Sub
End If
End If
q = rstStkD.Fields(0) 'Quanity
cv = rstStkD.Fields(1)
Tot = Tot + q * cv
s = rstStkD.Fields(2)
rstStkD.MoveNext
Loop

I wrote this as past od a DB to record activities in my portfolio. I won't to display a message when I forgot to include a particular stock. However, there are some I don't won't to display. Field three is a check box. When checked I don't want Access to record the information. However, if the name is missing and the box is checked, Access kicks me out of the loop with out continuing to record the rest of the data. Everything seems to work until it reaches the "Tot = Tot + q * cv" statement. Again, this only happens when the box is checked and the name is missing. Any other combination seems to work!
Thanks for your help. You people are great!!

Bill

 

[SkipVought]

Hi,

Are you sure that field(3) is data type String and not Boolean?

You've been around Tek-Tips for some seventeen years. So why aren't you using TGML codes to display your code like...

If rstStkD.Fields(3) = "false" Then
'...

Also you won't want to assign variables like q, cv, tot and s without using them in some useful manner.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

>Everything seems to work until it reaches the "Tot = Tot + q * cv" statement

And what happens at that point?

 

[billheath]

Thanks for the responses.
Field(3) is a check box on a form. I have tried to express the statement as "If rstStkD.Fields(3) = -1 Then". I get the same results. And "q, cv, tot and s" are used later in the logic.

The program jumps out of the loop and exits to the calling statements. The is a subroutine called "Get_Tot". When this happens, the program jumps to the statement after "get_tot".

The whole routine works properly when either of the "if" statements are false. is there some rule against nesting more then one If Statement in a Do Loop?

 

[SkipVought]

Regarding your CheckBox, it is a Boolean, either TRUE or FALSE (which is NOT "true" or "false").

So if you want the TRUE result...

If rstStkD.Fields(3) Then
'...

...or if you want the FALSE result...

If Not rstStkD.Fields(3) Then
'...

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[strongm]

>The program jumps out of the loop and exits to the calling statements

Which suggest that you've got an active error handler running, from the description On Error Resume Next

Now:
>If IsNull(rstStkD.Fields(1))

So we only go on if rstStkD.Fields(1) is Null. And then we assign that to cv. And then we try and multiply by cv ...

That raises an error. But since you have an error handler running, the error handler is triggered.

Disable the error handler (comment it out), and you should see the error that is actually occurring (which will probably be Error 94)

 

[billheath]

Thanks. I also had one other problem but I would have never found it if I didn't get that one!
Yes I have been a member for 17 years, but I am, as you can tell still a novice/ newbie! Don't get much of a chance to do programming and never had any training. That is why I really appreciate yours and the others help!
Thank you again! Bill