Dlookup multi criteria

[Deb Auer]

Access 2003

using dlookup to make a button visible.
FYI MSPContactUserID(same as Environ("username"))

table t41ContactMSP looks like this

MSPContactID(PK)  MSPTitleID  MSPContactUserID
  1                      1              smitha
  2                      3              smitha
  3                      1              jonesb
  4                      2              bakerc
  5                      2              smitha

smitha has many titles.

Need to verify the Environ("username") has the same MSPContactID as the ContactPMgrID (ContactPMgrID is field on form) then verify that it is the correct MSPTitleID (1). How can I make it meet all criteria?

Here is the issue. it finds the first instance of the MSPContactUserID and stops there, it does not take into account that the MSPTitleID must me 1.

Me!fClosureApproval.Form.btnApprClose.Visible = DLookup("[MSPContactID]", "[t41ContactsMSP]", _
    "[MSPContactUserID] = '" & Environ("username") & "'") = Me.ContactPMgrID _
    & "' And [MSPTitleID] = '" & 1

[patjones]

Hi Deb,

The problem here is with the check for [MSPTitleID] at the end of the line. VBA does not know that you are referring to a column in a table in your database. That check is outside the context of the DLookup call.

I would suggest trying this instead:

Me!fClosureApproval.Form.btnApprClose.Visible = (DLookup("[MSPContactID]", "[t41ContactsMSP]", "[MSPContactUserID] = '" & Environ("username") & "' AND [MSPTitleID] = 1) = Me.ContactPMgrID)

Let me know how it works out.

Pat

[NeoPa]

As Pat says, you need to include all the Criteria for the lookup in the Criteria (third) parameter of the DLookup() call.