• Test for range in code

    • This topic has 10 replies, 4 voices, and was last updated 16 years ago.
    Author
    Topic
    #459995

    In this post [post=”689866″]Post 689866[/post], you explained how I test for a range. This time I want to test for something that’s not in the range. Basically, I want to check if a tick box is not checked AND the part number is not in the range. So I have :-

    If Me.Label_Checked = 0 And [Me.Stock_Code_No_1] Not In (“1Z51”, “1Z52″,”1Z53”) Then

    This isn’t right I know, but I don’t know what should go after the last ].

    Viewing 4 reply threads
    Author
    Replies
    • #1161556

      Rob,
      try:

      If Me.Label_Checked = 0 And Not ([Me.Stock_Code_No_1] In (“1Z51”, “1Z52″,”1Z53”) ) Then

      The idea being that you check if the item IS in the list, then reverse the true/false

      Or, since you’re no longer in a text box but in VBA, split the test into two:

      If Me.Label_Checked = 0 then

      if ([Me.Stock_Code_No_1] In (“1Z51”, “1Z52″,”1Z53”) ) then

      else

      end if

      end if

      (apologies for not using code tags, I’m in a rush)

      Cheers,

    • #1163455

      Thanks Jules. Only just got round to testing it. I’m getting a compile error: Expected:), and the “In” bit is highlighted.

      • #1163457

        The construct In (…, …) or Not In (…, …) is valid in SQL but not in VBA.

        If you have a short list of values, you can use

        If Me.Label_Checked = 0 And Me.Stock_Code_No_1 “1Z51” And Me.Stock_Code_No_1 “1Z52” And Me.Stock_Code_No_1 “1Z53” Then

        An alternative that works better if you have a long list of values:

        Code:
        If Me.Label_Checked = 0 Then
          Select Case Me.Stock_Code_No_1
        	Case "1Z51", "1Z52","1Z53"
        	  ' Do nothing
        	Case Else
        	  ' Your code here
        	  ...
          End Select
        End If
    • #1163458

      Thanks Hans. It’s a long list, so I’ll use the second method. Thanks again.

      • #1163528

        Thanks Hans. It’s a long list, so I’ll use the second method. Thanks again.

        What you could also do is to populate a table with these codes you dont want and use that table in the query.

        In this way you can add or delete from the table at will and not have to change code to do it.

        • #1164028

          What you could also do is to populate a table with these codes you dont want and use that table in the query.

          In this way you can add or delete from the table at will and not have to change code to do it.

          It’s not working, but it was last Thursday. Once the box IS checked, I want to print the report. This is the code I’ve got :-

          [codebox]Private Sub Print_CofC_Click()
          On Error GoTo Err_Print_CofC_Click

          Dim stDocName As String
          stDocName = “Certificate of Conformity (new)”

          If Me.Label_Checked = 0 Then
          Select Case Trim([Stock_Code_No_1])
          Case “1Z51”, “1Z52”, “1Z53”

          DoCmd.RunCommand acCmdSaveRecord
          DoCmd.OpenReport stDocName, acViewPreview

          ‘DoCmd.PrintOut , , , , 2
          Case Else
          MsgBox “You haven’t checked the label”, vbExclamation
          Cancel = True
          Me.Label_Checked.SetFocus
          End Select
          End If
          If Me.Stock_Code_No_1 Like “1M*” Then
          Const strPath = “U:Common FoldersQuality Department SharedDrawings Mathys”
          Dim strFile As String
          strFile = Dir(strPath & “*” & Trim(Me.Stock_Code_No_1) & ” – ” & Trim(Me.Customer_Order_No_1) & “.pdf”)

          If strFile = “” Then
          ‘ File not found, display warning
          MsgBox “No report found for this Drawing No.”, vbExclamation
          Else
          ‘ File found, open it in default PDF reader
          ‘Application.FollowHyperlink strPath & strFile
          Call PrintFile(strPath & strFile)
          End If
          Else: End If

          Exit_Print_CofC_Click:
          Exit Sub

          Err_Print_CofC_Click:
          MsgBox Err.Description
          Resume Exit_Print_CofC_Click

          End Sub[/codebox]

          • #1164029

            Cancel = True makes no sense in the On Click event.

            Could you try to explain clearly and in detail what you want to accomplish?

    • #1164022

      Thanks Patt. Also an excellent idea. We may want to change them, so I’ll look into that method. Thanks.

    • #1164056

      Basicly, the user must tick the Checked_Label box before being allowed to print the report, unless the part numbers are in the range 1Z51,1Z52,1Z53, in which case they don’t kneed to tick the box.

      • #1164060

        Does this version do what you want?

        Code:
        Private Sub Print_CofC_Click()
          Const strPath = "U:Common FoldersQuality Department SharedDrawings Mathys"
          Dim strFile As String
        
          On Error GoTo Err_Print_CofC_Click
        
          If Me.Label_Checked = False Then
        	Select Case Trim(Me.Stock_Code_No_1)
        	  Case "1Z51", "1Z52", "1Z53"
        		DoCmd.RunCommand acCmdSaveRecord
        		DoCmd.OpenReport "Certificate of Conformity (new)", acViewPreview
        	  Case Else
        		MsgBox "You haven't checked the label", vbExclamation
        		Me.Label_Checked.SetFocus
        	End Select
          ElseIf Me.Stock_Code_No_1 Like "1M*" Then
        	strFile = Dir(strPath & "*" & Trim(Me.Stock_Code_No_1) & " - " & _
        	  Trim(Me.Customer_Order_No_1) & ".pdf")
        	If strFile = "" Then
        	  ' File not found, display warning
        	  MsgBox "No report found for this Drawing No.", vbExclamation
        	Else
        	  ' File found, open it in default PDF reader
        	  Call PrintFile(strPath & strFile)
        	End If
          End If
        
        Exit_Print_CofC_Click:
          Exit Sub
        
        Err_Print_CofC_Click:
          MsgBox Err.Description, vbExclamation
          Resume Exit_Print_CofC_Click
        End Sub
    Viewing 4 reply threads
    Reply To: Test for range in code

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: