Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    In this post Post 689866, 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 ].

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    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,

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Jules. Only just got round to testing it. I'm getting a compile error: Expected, and the "In" bit is highlighted.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans. It's a long list, so I'll use the second method. Thanks again.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='robm' post='778352' date='05-Jun-2009 00:38']Thanks Hans. It's a long list, so I'll use the second method. Thanks again.[/quote]
    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.

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Patt. Also an excellent idea. We may want to change them, so I'll look into that method. Thanks.

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='778434' date='05-Jun-2009 03:35']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.[/quote]

    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 Folders\Quality Department Shared\Drawings 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]

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Cancel = True makes no sense in the On Click event.

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

  10. #10
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does this version do what you want?

    Code:
    Private Sub Print_CofC_Click()
      Const strPath = "U:\Common Folders\Quality Department Shared\Drawings 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •