OR statement for checking field value 3

[jaywb]

I already have some code which checks the value of a field and enters text in another field accordingly. The code only checks for one value however I need to check for more than one and enter the default text into the other field. I have pasted my working code below and an example of how I think the revised code should work.

Private Sub Delivery_Address_Enter()
If Company_Name = "Company1" Then
Special_Instructions.Value = "Please deliver to gate 1"
End If
End Sub

Private Sub Delivery_Address_Enter()
If Company_Name = "Company1" or "Company2" or "Company7" Then
Special_Instructions.Value = "Please deliver to gate 1"
End If
End Sub


Thanks in advance.

 

[SkipVought]

Hi,

Ditch the IF statement. Rather...

Select Case Company_Name 
  Case "Company1", "Company2", "Company7" 
    'do something
    Special_Instructions.Value = "Please deliver to gate 1"
  Case Else
    'do something else

End Select

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[JoeAtWork]

I would use the Select statement as Skip suggests.

However, just so you know, the correct way to do the If statement would be:

Private Sub Delivery_Address_Enter()
If Company_Name = "Company1" or Company_Name = "Company2" or Company_Name = "Company7" Then
Special_Instructions.Value = "Please deliver to gate 1"
End If
End Sub

 

[jaywb]

Would it be possible to check a table for the values instead? I'm thinking of making it easier for users to add new companies.

 

[JerryKlmns]

Either use the DLookup function

If DLookup("Company_Name", "tblCompanies", "Company_Name='"& strSearchCompany & "'") & "" <> "" Then
'Add the new company the way you know
Else
'Already there
End If

which is slow for large tables or an appropriate recordset object with its .Source property set to

"SELECT Company_Name FROM tblCompanies WHERE Company_Name='" & strSearchCompany & "';"

If that recordset when opened is empty, then add that new company using the Add method of the recordset