I keep getting the same true or false result when using if statement

[dduffy]

I keep getting the same true or false result when using if statement with listbox.

I have a form, on the form are 3 listbox controls. 1st 2 return result from a query initiated on subforms. The 3rd is to be used for validation. I run a query over the results of Lstcont1 & Lstcont2 to get the result in Lst Control3.

I then have the following code to validate Lstcont3 and prevent exiting if it equals 0.

Private Sub closemanualrec_Click()
On Error GoTo closemanualrec_Click_Err

If (Lstcont3 <> 0) Then
Beep
MsgBox &quot;test&quot;, vbOKOnly, &quot;test1&quot;
End
End If
DoCmd.SetWarnings False

However it seems I get the same answer regardless of the value showing in Lstcont3.

Any ideas on syntax? Or should I be looking at this a different way?

Cheers

 

[KornGeek]

Is it a numeric field that you're actually testing against the value 0? If you're trying to compare it to the string &quot;0&quot;, see if it's Null, or count the number of entries, then your test won't work.

Try putting up a message box to see what the value of Lstcont3 is just before the if statement.

Hope this helps.

 

[Rolliee]

I would use the nz(...) function to change nulls to &quot;&quot; or 0. If you are comparing a textbox to a numeric, I would use a val(...). Sometimes you can get by with sloppy but not always. The text box is just that 'text.'


rollie@bwsys.net

 

[dduffy]

I have checked properties from the query which are definately values, I can't find any such propertires for the listbox but I have also tried it with different examples and changing the code to = to (say) 47.15 and then also as text &quot;47.15&quot; with no change to the results.

I have also looked at populating a table with this validation figure and then running it from there but I can't get that to work either.

Am I going about this the wrong way?

Cheers

 

[SgtJarrow]

Looking back at your original post:

I then have the following code to validate Lstcont3 and prevent exiting if it equals 0.

Private Sub closemanualrec_Click()
On Error GoTo closemanualrec_Click_Err

If (Lstcont3 <> 0) Then
Beep
MsgBox &quot;test&quot;, vbOKOnly, &quot;test1&quot;
End
End If
DoCmd.SetWarnings False

You code above is checking to see it Lstcont3 is NOT equal to zero.....

Here would be my suggestion. First compare for zero or null, and if successful, cancel close. Also, you may want to explicity declare your controls....sometimes helps.

Private Sub closemanualrec_Click()
On Error GoTo closemanualrec_Click_Err

If (Me![Lstcont3] = 0) Or IsNull(Me![Lstcont3]) Then
Beep
MsgBox &quot;test&quot;, vbOKOnly, &quot;test1&quot;
End
' Exit Sub
End If
DoCmd.SetWarnings False





The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[dduffy]

Sorry that was a typo, meant to be if it does not equal 0.

I have tried your code both with =0 and <>0 but same thing occurs

i.e. if the code is set to =0 and the result in the lstbox is 0 I get the message box (this is correct), so I then select another record in the sunform which causes the list box to requery and the result is now -47.95 I once again (this time incorrectly) get the message box appearing.

This leads me to believe the list box result is simply being ignore or IsNull regardless of what it actually returns. Or syntax is messed up???

I have tried using various other controls but with no luck I have also tried addressing the query directly but my VBA skills are not really upto it.

Any other ideas?

Cheers

 

[SgtJarrow]

Ok....if I follow you correctly, you want to prevent closing of the form if the Lstcont3 = 0.

So I would again go with the compare for 0 and exit if = 0 instead of not equal to zero continue.

Running in my little mind here is something that kind of troubles me.....You said that Lstcont3 is a result of a query against Lstcont1 and Lstcont2.

List boxes return a string value, not a numeric. I did a quick test: I created two tables with numeric values only....I create a list box for each on a form and used a command button to &quot;add&quot; them and put the result in a text box.

Using: List1 + List2 'Where List1 = 23.00 and List2 = 12.00
I got: 2312

Using: CDbl(List1) + CDbl(List2)
I got: 25

Perhaps this is your problem.....check to make sure you numbers are actually &quot;numbers&quot;. It is possible they are becoming string values inside the list boxes.....thereby they will never be equal to 0......

Don't know if this will help but it keeps nagging me......darn little voices in my head!

Also, if you are able, and can zip the file to under 1MB, feel free to shoot me an e-mail at the address below with the database and I will take a look. Always happy to help out. The secret to creativity is knowing how to hide your sources. - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[dduffy]

Cheers I will sort it out in a few minutes - just getting rid of security etc etc.

Cheers

 

[jebry]

Hi!

Another possibility: if the list box is set to multiselect then the value will always be null.

hth
Jeff Bridgham
bridgham@purdue.edu