Dlookup for multiple conditions 1

[meagain]

Hi,

A form's open event has run the following code successfully for years:

Private Sub Form_Open(Cancel As Integer)
If DLookup("Level", "USERS", "[USER ID] = '" & CurrentUser & "'") = 31 Then
DoCmd.RunMacro "Blah Blah Filter"
Else: DoCmd.RunMacro "Dog"
End If
End Sub

However, we now need to add a second level of 66 to run the macro "Blah Blah Filter"

We've tried the following, but it runs "Dog" for both levels, and any others:

If DLookup("Level", "USERS", "[USER ID] = '" & CurrentUser & "'") = 31 Then
DoCmd.RunMacro "Blah Blah Filter"
End If

If DLookup("Level", "USERS", "[USER ID] = '" & CurrentUser & "'") = 66 Then
DoCmd.RunMacro "Blah Blah Filter"

Else: DoCmd.RunMacro "Dog"
End If
End Sub

We've also tried changing = 31 to = (31,66) which doesn't work.

Your solution is greatly appreciated.

Thanks you!!

 

[MajP]

Private Sub Form_Open(Cancel As Integer)
 dim level as integer
 dim strWhere as string
 strWhere = "[USER ID] = '" & CurrentUser & "'"
 debug.print "Where: " &  strWhere
 'post back what this shows
 level = nz(DLookup("Level", "USERS", strWhere),0)
 debug.print "level " & level
 'post back what this shows
 If level = 31 or level = 66 Then
   DoCmd.RunMacro "Blah Blah Filter"
 Else
   DoCmd.RunMacro "Dog"
 End If
End Sub

 

[meagain]

Worked like a charm MajP.

Thanks so much!!

 

[kjv1611]

Did it really work? If it worked well, don't forget to mark it as a great post, so others can know the question was answered or at least there is useful information in the thread.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[meagain]

Hi KJV1611,

I did mark it as a Great Post 3 days ago, at the same time I commented that MajP's code worked like a charm That is why the pink star appears beside the thread in the forum list.

 

[kjv1611]

Hmm, must've been short a Mtn Dew when I looked, then. Thanks for setting me straight.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57