10 Replies - 2147 Views - Last Post: 13 May 2014 - 02:33 PM

#1 burrina  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 31
  • Joined: 09-November 12

DLookup Matching Criteria

Posted 13 May 2014 - 10:59 AM

I am trying to make it so if a Employee is a Department Head then they can Login
DeptID is a Yes/No field in tblUserSecurity_Sec.

If IsNull(DLookup("[UserID]", "tblUserSecurity_Sec", "[UserID]='" & Me.txtUserID.Value & "' And [pw]='" & Me.txtPW.Value & "'")) Then



Thanks,
Is This A Good Question/Topic? 0
  • +

Replies To: DLookup Matching Criteria

#2 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 387
  • Posts: 1,409
  • Joined: 15-January 14

Re: DLookup Matching Criteria

Posted 13 May 2014 - 11:20 AM

You could add the department head field to that query to make sure it is set to what you want.
Was This Post Helpful? 0
  • +
  • -

#3 burrina  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 31
  • Joined: 09-November 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 11:46 AM

View PostArtificialSoldier, on 13 May 2014 - 12:20 PM, said:

You could add the department head field to that query to make sure it is set to what you want.



Here is my latest attempt:
If ("[tblUserSecurity_Sec].UserID, [tblUserSecurirt_Sec].pw, [tblUserSecurity_Sec].DeptID, [tblUserSecurity_Sec]" _
           & "WHERE [tblUserSecurity_Sec].UserID = '" & txtUserID & "' AND [tblUserSecurity_Sec].pw = '" & txtPW & "'") Then
            
     DoCmd.OpenForm "frmEmployees", acNormal, , "DeptID='" & ("DeptID.Value") & "'"
     
    Else
   Call MsgBox("You do NOT have Department Head Clearance," _
               & vbCrLf & "Please Contact your System Administrator for Help." _
               , vbExclamation, Application.Name)
               
                Exit Sub
                    End If


It compiles with no errors but nothing happens.
Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 387
  • Posts: 1,409
  • Joined: 15-January 14

Re: DLookup Matching Criteria

Posted 13 May 2014 - 12:58 PM

That looks like the query is going to contain "[tblUserSecurity_Sec]WHERE".
Was This Post Helpful? 0
  • +
  • -

#5 burrina  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 31
  • Joined: 09-November 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 01:01 PM

View PostArtificialSoldier, on 13 May 2014 - 01:58 PM, said:

That looks like the query is going to contain "[tblUserSecurity_Sec]WHERE".


Yes, maybe so, but what I need help with is the correct syntax for the complete statement.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,869
  • Joined: 12-December 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 01:05 PM

That if-condition is just a string, it is not using DLookup (or anything else).
Was This Post Helpful? 0
  • +
  • -

#7 burrina  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 31
  • Joined: 09-November 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 01:40 PM

If IsNull(DLookup("[UserID]", "tblUserSecurity_Sec", "[UserID]='" & Me.txtUserID.Value & "[DeptID]" = 1)) Then
'(ABOVE WORKS)

DoCmd.OpenForm "frmEmployees", acNormal, , "DeptID='" & ("DeptID.Value") & "'" '(NOT WORKING?)

It asks me for the DeptID parameter
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,869
  • Joined: 12-December 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 01:53 PM

DoCmd.OpenForm "frmEmployees", acNormal, , "DeptID='" & ("DeptID.Value") & "'" '(NOT WORKING?)

If DeptID is a number then you don't need to surround it with apostrophes.

But you are supplying the text "DeptID.Value". If DeptID is a TextBox then you don't need to use 'Value' either:

DoCmd.OpenForm "frmEmployees", acNormal, , "DeptID=" & DeptID '(NOT WORKING?)

If it is, in fact, a TextBox then you should rename it, to make it explicit that you are referring to this control on a form, and not a field (or variable) of this name.
Was This Post Helpful? 0
  • +
  • -

#9 burrina  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 31
  • Joined: 09-November 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 01:58 PM

It is a Yes/No Field in tblUserSecurity_Sec
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,869
  • Joined: 12-December 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 02:09 PM

I do not know how this relates to a DeptID.
Was This Post Helpful? 0
  • +
  • -

#11 burrina  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 31
  • Joined: 09-November 12

Re: DLookup Matching Criteria

Posted 13 May 2014 - 02:33 PM

It does not relate to the actual Login but relates to the frmEmployees ONLY showing Employees assigned to the same DeptID as the Person who LoggedIn.
DptID in tblEmployees (Number)
DeptID in tblUserSecurity_Sec (Yes/No)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1