VBA Access Loop Code not working

Question

Can anyone help??

This code isn’t starting when it should and isn't stopping when it should and I can’t figure out why.

Private Sub Condition1_Click()

Dim Condition1 As Boolean

Do

DoCmd.OpenQuery "qGBX2b"
DoCmd.OpenQuery "qGBX2c"
DoCmd.OpenQuery "qGBX2d"
CurrentDb.Execute "ALTER TABLE GBX2Temp ALTER COLUMN Line COUNTER(1,1)"
DoCmd.Requery

Loop Until Condition1 = True

MsgBox "Done", vbDefaultButton1, "Done"
End Sub

It is run from a form, where a field is counting down and Condition1 is a field that changes to TRUE once it reaches a certain value.

As the code is above, it runs once and does not loop whilst the value of Condition1 is FALSE. If I change the Loop criteria to “Loop Until Condition1 = False”, it runs whilst the value is FALSE, but does not stop when it changes to True.

Where am I going wrong?

Where are you changing Condition1 ? You have a local variable with that name in the subroutine. If it's a field then you need a form reference. It just looks like the variable name in the sub, which is not changing. — nicomp
– nicomp, Commented Nov 27, 2015 at 17:36
Yes, thanks Delmer - Condition1 is a field. What do I change in the code to reference the form field rather than local variable? — Simon Metson
– Simon Metson, Commented Nov 27, 2015 at 17:43

Brian Pressler · Accepted Answer · 2015-11-27 17:51:46Z

1

You seem to be experiencing a problem with scope. When you issue the DIM statement, that variable name overrides all other field or control names that might exist unless you specifically designate the scope (such was with me.Condition1)

If you have a field or textbox named Condition1, you can simply remove the dim statement and it will use the value of that control or field instead. I recommend that you rename textboxes to something other than the field name... simply for the fact that it's completely unambiguous which value you are referring to. So if your Field name is Condition1, then name your text boxes something like txtCondition1.

edited Nov 27, 2015 at 17:51

answered Nov 27, 2015 at 17:48

Brian Pressler

6,7232 gold badges21 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Access Loop Code not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related