By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,359 Members | 539 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,359 IT Pros & Developers. It's quick & easy.

Dlookup multi criteria

P: 2
Access 2003

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

table t41ContactMSP looks like this
Expand|Select|Wrap|Line Numbers
  1. MSPContactID(PK)  MSPTitleID  MSPContactUserID
  2.   1                      1              smitha
  3.   2                      3              smitha
  4.   3                      1              jonesb
  5.   4                      2              bakerc
  6.   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.

Expand|Select|Wrap|Line Numbers
  1. Me!fClosureApproval.Form.btnApprClose.Visible = DLookup("[MSPContactID]", "[t41ContactsMSP]", _
  2.     "[MSPContactUserID] = '" & Environ("username") & "'") = Me.ContactPMgrID _
  3.     & "' And [MSPTitleID] = '" & 1
Mar 3 '11 #1
Share this Question
Share on Google+
2 Replies

patjones
Expert 100+
P: 931
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:

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

Let me know how it works out.

Pat
Mar 3 '11 #2

NeoPa
Expert Mod 15k+
P: 31,770
As Pat says, you need to include all the Criteria for the lookup in the Criteria (third) parameter of the DLookup() call.
Mar 4 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.