Need help with some VB code

[soupisgood84]

I get results but not the kind I want.
I have a form with a checkbox in it. If the box is checked, I want to go to another form; if not, I want the code to continue. I naturally used a If Then Else loop, but I must have overlooked something. Please Help. Thanks in advance.

Private Sub cmdSubmit_Click()
    
    'This prevents the user to continue unless all fields are completed
    DoCmd.GoToRecord , , acNext
    
    Dim stDocName As String
    stDocName = "frmReceiving(PrintOut)"
    Dim stCheckforLabel As Integer
    stCheckforLabel = [Forms]![frmExpense-Receiving]![CheckforLabel]
    
    If stCheckforLabel = Null Then
    GoTo subform1
    Else
    DoCmd.OpenForm stDocName
    Exit Sub
    End If
    

subform1:
    Dim LResponse As Integer
    LResponse = MsgBox("     Data Entered Successfully     ", vbOKOnly, "Notice")
    Exit Sub

 

[gwinn7]

What results are you getting?

The first problem I see is how you are checking for a null in your expression....

stCheckforLabel = Null

Why not check the control directly instead of making a silly needless variable assignment? Try this instead...

isnull(Me.CheckforLabel) = true

Btw, being nit picky here: If Else is not a loop.

Gary
gwinn7

 

[gwinn7]

Even better, to check for a "false" condition, rather than just a null...

nz(me.checkforlabel, false) = false

Gary

 

[Remou]

I find that it is very unusual for a checkbox to be null, you would need to set the Triple State property, for example.

A common way to refer to boolean fields is:

[tt]If [blue]Not[/blue] stCheckforLabel Then
'stCheckforLabel is false[/tt]

 

[gwinn7]

Remou,

In my experience, I have found that not explicitly checking for a true/false condition can cause run-time issues, which is why I did not make the suggestion you did. There is documentation on this problem out there about it.

Perhaps this problem was fixed in a latter version of VBA/Access, but I am not aware of it. In VBA, its just better to be explicit though it may work 97% of the time.

Gary
gwinn7

 

[gwinn7]

I realize my last statement may not be clear. I was saying that being explicit will have a lower failure rate than implicit when putting expressions in VBA.

Gary

 

[Remou]

Don't you think that boolean fields are a little different? According to Microsoft, a boolean field can only have a true or false value. So

[tt]If Not CheckBox[/tt]
Translates to
[tt]If Not True[/tt]

Which is quite explicit, yesno? It is also the format that I have noticed the Microsoft MVPs are using, for example, Doug Steele, Microsoft Access MVP, in these threads:

http://groups.google.ie/group/micro...read/thread/400c486afe1e6351/04349bd883a9f4dd

http://groups.google.ie/group/micro...read/thread/e081bbd87e2c6f5b/fe2c8feac8fa5906

I also seem to recall someone mentioning that using "= false" in queries can cause problems. However, you mention documentation, I would be very interested in reading it, please.

 

[PHV]

Remou, a YesNo FIELD has only 2 states but a CheckBox CONTROL may have TriState (True,False,Null).

bottom line:
If CheckBox Then
' True
Else
' False or Null
End If

If Not CheckBox Then
' False
Else
' True or Null
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

PHV, yes I am aware of that, and mentioned it in my post dated 25 May 07 4:55. The debate is between the formats:

If Not CheckBox Then

and

If Checkbox=False

I am inclined to use the first, whereas gwinn7 recommends the second.

 

[PHV]

My emphasis was on the Else part ...

 

[gwinn7]

Remou,

Good for the MVPs. I don't have the time to track down that article (sorry!), it was a couple of years back when I found it.

It was not indicated whether the check box was definitely representing a Boolean. It is a general rule of mine to be explicit because of my past run-in with this bug that crops up.

This...

"If not me.checkbox then " is not an explicit expression.

I am referring to " If not me.checkbox = false then" as explicit.

Once upon a time I had similar code and when I added the "= <myvalue" it worked without incident and never again had that problem.

Sorry, I am just trying to be helpful. I don't assert to be 100% of the time. I am only citing experience to the best of my recollection.

Gary
gwinn7

 

[Remou]

But a debate is fun, yesno?

 

[PHV]

But a debate is fun, yesno?
Unless it's null ;-)

 

[gwinn7]

Lol! Yes.

Gary
gwinn7

 

[soupisgood84]

BTW...thank you for your help.