Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IF statement not working correctly 4

Status
Not open for further replies.
SeadnaS

SeadnaS

Programmer
May 30, 2011
214
I've done something wrong here it seems. The following code is giving me all "FAIL" results even when it should be "PASS". I want my subform to display pass/fail results in the column next to the x1 number but this code is only showing fail. What could be causing this?

Private Sub x1_Change()
If Me.Parent.TESTS.Value = 1 Then
If x1 >= Me.Parent.SPEC1.Value Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 2 Then
If x1 <= 20 Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 3 Then
Me.passfail.Value = "N/A"
End If
If Me.Parent.TESTS.Value = 4 Then
If (x1 <= Me.Parent.bodmax And x1 >= Me.Parent.bodmin) Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 5 Then
If (x1 <= Me.Parent.bodqmax And x1 >= Me.Parent.bodqmin) Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 6 Then
If x1 >= 5 Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 7 Then
If x1 >= 5 Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 8 Then
If x1 >= 5 Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 9 Then
If x1 >= 10 Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

If Me.Parent.TESTS.Value = 10 Then
If x1 = "Pass" Then
Me.passfail.Value = "PASS"
Else: Me.passfail.Value = "FAIL"

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
 
Sort by date Sort by votes
That program looks great, but I can't have any software installed on this PC without filling out a form and sending it to HR and waiting for 2+ weeks!
 
Upvote 0 Downvote
First off, IMO values like 1, 20, PASS, FAIL, N/A, ... shouldn't be hard-coded. I typically like to store all of these values in tables, not in code.

Second, you should consider using "Select Case" rather than tons of "If". I think your code would be much more readable with something like:

Code: 
Dim intTests as Integer, dblSpec1 as Double, dblX1 as Double
intTests = Me.Parent.TESTS.Value
dblSpec1 = Me.Parent.SPEC1.Value
dblX1 = Me.x1 'only a guess here

Select Case True
  Case intTest = 1 AND dblX1 >= dblSpec1
     Me.Passfail = "PASS"
  Case ...

  Case Else

End Select

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
That looks good dhookom, i'll try that when i'm back in the morning. I'm not too good with select case statements, but i'll give it a shot! Thanks!
 
Upvote 0 Downvote
Well, here the indented version of your code:
Code: 
Private Sub x1_Change()
If Me.Parent.TESTS.Value = 1 Then
  If x1 >= Me.Parent.SPEC1.Value Then
    Me.passfail.Value = "PASS"
  Else: Me.passfail.Value = "FAIL"
    If Me.Parent.TESTS.Value = 2 Then
      If x1 <= 20 Then
        Me.passfail.Value = "PASS"
      Else: Me.passfail.Value = "FAIL"
        If Me.Parent.TESTS.Value = 3 Then
          Me.passfail.Value = "N/A"
        End If
        If Me.Parent.TESTS.Value = 4 Then
          If (x1 <= Me.Parent.bodmax And x1 >= Me.Parent.bodmin) Then
            Me.passfail.Value = "PASS"
          Else: Me.passfail.Value = "FAIL"
            If Me.Parent.TESTS.Value = 5 Then
              If (x1 <= Me.Parent.bodqmax And x1 >= Me.Parent.bodqmin) Then
                Me.passfail.Value = "PASS"
              Else: Me.passfail.Value = "FAIL"
                If Me.Parent.TESTS.Value = 6 Then
                  If x1 >= 5 Then
                    Me.passfail.Value = "PASS"
                  Else: Me.passfail.Value = "FAIL"
                    If Me.Parent.TESTS.Value = 7 Then
                      If x1 >= 5 Then
                        Me.passfail.Value = "PASS"
                      Else: Me.passfail.Value = "FAIL"
                        If Me.Parent.TESTS.Value = 8 Then
                          If x1 >= 5 Then
                            Me.passfail.Value = "PASS"
                          Else: Me.passfail.Value = "FAIL"
                            If Me.Parent.TESTS.Value = 9 Then
                              If x1 >= 10 Then
                                Me.passfail.Value = "PASS"
                              Else: Me.passfail.Value = "FAIL"
                                If Me.Parent.TESTS.Value = 10 Then
                                  If x1 = "Pass" Then
                                    Me.passfail.Value = "PASS"
                                  Else: Me.passfail.Value = "FAIL"
                                  End If
                                End If
                              End If
                            End If
                          End If
                        End If
                      End If
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      End If
    End If
  End If
End If
End Sub[/i]

I highly doubt this is what you wanted ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Ok, tried the case select statement. It is doing the exact same thing. When i open the form the pass/fail column on the subform is empty until i enter a new record. Then, everything is a fail. Even the record where I havn't entered a number yet is a fail. On the subform i enter values in the column x1 (x1 means nothing i just couldnt think of a better name) and the the other columns automatically fill in themselves. The pass/fail one is the one with the problem. And even the last row where I havnt entered anything yet is a fail.

I have linked a screenshot. The form is unfinished so please ignore the messy combo boxes n stuff.
 
 http://www.mediafire.com/?apjgkhwoqcluo1n
Upvote 0 Downvote
Thanks dhookom, I've read that.

Here's the select case statement I've created. Have I done it wrong?

Code: 
Dim intTests As Integer, dblSpec1 As Double, dblX1 As String
intTests = Me.Parent.TESTS.Value
dblSpec1 = Me.Parent.SPEC1.Value
dblX1 = Me.x1
dblBODMAX = Me.Parent.bodmax.Value
dblBODMIN = Me.Parent.bodmin.Value
dblBODQMIN = Me.Parent.bodqmin.Value
dblBODQMAX = Me.Parent.bodqmax.Value

Select Case True
Case intTests = 1 And dblX1 >= dblSpec1
    Me.passfail = "PASS"
    
Case intTests = 2 And dblX1 <= 20
    Me.passfail = "PASS"
    
Case intTests = 3
    Me.passfail = "N/A"
    
Case intTests = 4 And dblX1 <= dblBODMAX And dblX1 >= dblBODMIN
    Me.passfail = "PASS"
    
Case intTests = 5 And dblX1 <= dblBODQMAX And dblX1 >= dblBODQMIN
    Me.passfail = "PASS"
    
Case intTests = 6 And dblX1 >= 5
    Me.passfail = "PASS"
    
Case intTests = 7 And dblX1 >= 5
    Me.passfail = "PASS"
    
Case intTests = 8 And dblX1 >= 5
    Me.passfail = "PASS"
    
Case intTests = 9 And dblX1 >= 10
    Me.passfail = "PASS"
    
Case intTests = 10 And dblX1 = "PASS" Or dblX1 = "Pass" Or dblX1 = "pass"
    Me.passfail = "PASS"
    
Case Else
    Me.passfail = "FAIL"

End Select
 
Upvote 0 Downvote
SeadnaS,
We can't see your data or the results of stepping through your code.

Do you really want to use the On Change event since this is triggered with every key stroke in the control. Typically you would move this code to a separate Sub or Function and call it from the After Update of the x1 control.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote

I think you want something like this...
Code: 
Select Case Me.Parent.TESTS
    Case 1:
        If x1 >= Me.Parent.SPEC1 Then
            Me.passfail = "PASS"
        Else
            Me.passfail = "FAIL"
        End If
    Case 2:
        [green]insert code here[/green]
    Case 3:
        [green]insert code here[/green]
    Case 4:
        [green]insert code here[/green]
...
    Case Else
        Me.passfail = "FAIL"
End Select


Randy
 
Upvote 0 Downvote

I would follow Randy's lead but with a small modification:

Code: 
Me.Passfail = "Fail"
Select Case Me.Parents.Tests
  Case 1
    If dblX1 >= dblSpec1 Then Me.passfail = "PASS"
  Case 2
    If dblX1 <= 20 Then Me.passfail = "PASS"
  Case 3
    Me.Passfail = "N/A"
  Case 4
    If dblX1 <= dblBODMAX And dblX1 >= dblBODMIN Then Me.passfail = "PASS"
  Case 5
    If dblX1 <= dblBODQMAX And dblX1 >= dblBODQMIN Then Me.passfail = "PASS"
  Case 6,7,8
    If dblX1 >= 5 Then Me.passfail = "PASS"
  Case 9
    If dblX1 >= 10 Then Me.passfail = "PASS"
  Case 10
    ' This is where it breaks down... is dblX1 a number or a string? Don't know what to do with this.
End Select

As I have said before, and in agreement with a lot of very competent programmers here: Don't put values in code... put them in tables. This code would be a lot simpler, more compact, and easier to read and modify if the values were all in tables. AND, you would be less likely to have to modify it in the first place.
 
Upvote 0 Downvote

SeadnaS 26 Jul 11 9:35 said:
Dim intTests As Integer, dblSpec1 As Double, [red]dblX1 As String[/red]
Click to expand...
It would be nice if all variable's pre-fixes match the Types...

Have fun.

---- Andy
 
Upvote 0 Downvote
How are ya SeadnaS , , ,

Changing specs in the middle of a thread is [purple]one of the worst things you can do![/purple] At least get your logic (origional intent) solved ... It will open the door to better things. As you have it... I've converted to the following:
Code: 
[blue]   Dim flg As Boolean
   
   If Me.Parent.TESTS = 1 Then
      flg = (x1 >= Me.Parent.SPEC1)
   ElseIf Me.Parent.TESTS = 2 Then
      flg = (x1 <= 20)
   ElseIf Me.Parent.TESTS = 3 Then
       flg = (Me.passfail = "N/A")
   ElseIf Me.Parent.TESTS = 4 Then
      flg = (x1 <= Me.Parent.bodmax And x1 >= Me.Parent.bodmin)
   ElseIf Me.Parent.TESTS = 5 Then
      flg = (x1 <= Me.Parent.bodqmax And x1 >= Me.Parent.bodqmin)
   ElseIf Me.Parent.TESTS = 6 Or _
          Me.Parent.TESTS = 7 Or _
          Me.Parent.TESTS = 8 Then
      flg = (x1 >= 5)
   ElseIf Me.Parent.TESTS = 9 Then
      flg = (x1 >= 10)
   ElseIf Me.Parent.TESTS = 10 Then
      flg = (x1 = "Pass")
   End If
   
   Me.passfail = Choose(flg + 2, "Pass", "Fail")[/blue]
Give it a shot ... I believe you'll be satisified ...

[blue]Your Thoughts? . . .[/blue]

Give it a shor? ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Thanks everyone. Those are working kind of. If one of the records on the subform fails it changes the whole passfail column to "FAIL" and "PASS" if there is no fails. So its still working the same way. Perhaps its not an issue with the code.

Gammachaser. I had to make dblx1 a string (never mind the "dbl" part) because the user will be selecting either "PASS" or "FAIL" from a combo box for this one.

Thanks for your help guys.
 
Upvote 0 Downvote
Aceman, same thing. Its doing the exact same thing as the case select I made, if one fails the whole column shows up as fail. Thanks for your help though!
 
Upvote 0 Downvote
Hey guys sorry for wasting your time... Well this is embarrassing! My passfail wasn't bound to my table. As soon as I bound it to the passfail field on my child table it worked perfectly. Once again sorry and thanks for your help. Still tho, i'm learnin a lot. Thanks for the different if statements and select cases. I've a lot to look back on for reference.
 
Upvote 0 Downvote
SeadnaS . . .

I was contemplating asking just that question. Glad you picked it up. I had also compacted my code even further with the following:
Code: 
[blue]   Dim flg As Boolean
   
   flg = Choose(Me.Parent.TESTS, (x1 >= Me.Parent.SPEC1), _
                                 (x1 <= 20), _
                                 (Me.passfail = "N/A"), _
                                 (x1 <= Me.Parent.bodmax And x1 >= Me.Parent.bodmin), _
                                 (x1 <= Me.Parent.bodqmax And x1 >= Me.Parent.bodqmin), _
                                 (x1 >= 5), _
                                 (x1 >= 5), _
                                 (x1 >= 5), _
                                 (x1 >= 10), _
                                 (x1 = "Pass"))
   Me.passfail = Choose(flg + 2, "Pass", "Fail")[/blue]
There are no tricks here! Just lookup the [blue]Choose[/blue] function to understand better. Also ... within the [blue]Choose[/blue] function [blue](logical expression)[/blue] returns [blue]True (-1)[/blue] or [blue]False (0)[/blue]. This is just to bring you closer to the power of VBA!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote

Aceman -- quite elegant considering where this started.

I have never used Choose before (got stuck in the Access97 world for a long time by my employers) but you can bet I will in the future. Thanks for that.

Have another star.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Locked
  • Helpful tip
Capture Shift Key if Pressed on A form
Replies
3
Views
340
strongm
strongm
Moss100
  • Locked
  • Question
Code not working 1
Replies
5
Views
224
Darrylles
Darrylles
Moss100
  • Locked
  • Question
Vba filter not working 3
Replies
7
Views
311
Cole Mendoza
Cole Mendoza
theConjurian
  • Locked
  • Question
DoCmd.Close not working
Replies
1
Views
169
kjv1611
kjv1611
Moss100
  • Locked
  • Question
Stop user changing size of Access Application (not form)
Replies
1
Views
489
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top