Test for range in code

Topic

New Reply

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 ].

Reply | Quote

Replies

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,

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | 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.

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]

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote