How do I use "Or" for 3 instances in VBA on a form

[naigy via AccessMonster.com]

Hi All,

I am using the below code in a database which we use to do repairs to devices.
This snipet of code is checking if the device requires a battery replacement
but we are increasing our products and I need to include an Or kind of
statement on the first Line so that it will check for "Device 1" Or "Device
2" Or "Device 3" however when I try to use the Or command for this purpose it
doesn't work and spits up an Runtime 13 error - Type Mismatch. Can someone
please advise how best to do this. I know I could have as part of my First
Else Then if Device 2 (repeat all code) then as part of that Else have If
Device 3 then (repeat all code again). Is a "GoTo" an option and if so how
should I set that up.

The reason I need a device filter is because this does not apply to all
products. Really only the first line of code I think is relevant but I
included the whole function just in case. From what I can see in the Access
Help "Or" can only be used for two conditions but I can't even get it to work
for that

Private Sub Serial_AfterUpdate()

If Me.DeviceRepaired.Value = "Device 1" Then
Me.SubBattMaintCheck.Form.Requery
If Me!SubBattMaintCheck.Form!MaxOfDateDue <= Date Then
Dim Msg, Style, Title, Response
Msg = "This unit requires battery replacement. Press Yes to Automatically
open battery maintenance form or No to ignore" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Battery Maintenance Required" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "BatteryLog"
stLinkCriteria = "[SerialNumber]=" & "'" & Me![Serial] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
Cancel = True ' Perform some action.
End If
End If
End If

 

[OssieMac]

If Me.DeviceRepaired.Value = "Device 1" or_
Me.DeviceRepaired.Value = "Device 2" or _
Me.DeviceRepaired.Value = "Device 3" Then

Note that the space and underscore is a line break in an otherwise single
line of code.

 

[John Spencer (MVP)]

Instead of an If statement I sometimes use a case statement since it is easy
to add to the list in the case statement

Select Case Me.DeviceRepaired
Case "Device 1", "Device 2", "Device 3"


End Select

Another option is to use Instr to test for the value. Note that I've appended
a beginning and ending space to the string searched and the string sought
arguments to ensure unique matches

IF Instr(1," DEVICE 1 Device 2 Device 3 "," " & Me.DeviceRepaired & " ",1)>0 Then

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[naigy via AccessMonster.com]

Thanks Ozzie, Your method has worked a treat. I didn't realise that you had
to define the control each time like this.

Thanks John for your input also. I will keep it in mind going forward.

If Me.DeviceRepaired.Value = "Device 1" or_
Me.DeviceRepaired.Value = "Device 2" or _
Me.DeviceRepaired.Value = "Device 3" Then

Note that the space and underscore is a line break in an otherwise single
line of code.

Hi All,

[quoted text clipped - 37 lines]

End If
End If