LookupEdit controls with multiple restrictions

Further under the heading of things which are too easy to forget (or be unable to find when you need them) is this, from Ryan Farley:

A question that surfaces fairly often deals with LookupEdit controls and how to do more with the LookupRestriction properties, such as add multiple conditions to the restriction. Although it is undocumented, you can do that with the existing LookupEdit—you just have to put things in the wrong place. Let’s say you want to add a restriction to a LookupEdit for all contacts with an areacode of “623” AND that have a title of “Developer.”

LookupRestrictField = "WORKPHONE"

LookupRestrictOp = " LIKE '623%' AND " _
    & "TITLE = 'Developer' AND "1" = "

LookupRestrictValue = "1"

Or you could do it like this:

LookupRestrictField = "CONTACTID"

LookupRestrictOp = " IN (SELECT CONTACTID " _
    & "FROM CONTACT WHERE WORKPHONE LIKE '623%' " _
    & "AND TITLE = 'Developer') AND "1" = "

LookupRestrictValue = "1"

A few things to point out, make sure you notice the extra space at the start of the operator (ie: before “IN” or “LIKE” etc). This is needed because when the final query is constructed it just appends the three values together without spaces (so you’d get something like “CONTACTIDIN…” without the space). Also, the reason why you have to put all that in the RestrictOp property is because when the final query is constructed the RestrictValue gets enclosed in single-quotes. That is also the reason for the AND “1” = at the end.

And this, from RJ Samp:

Take a close look at the LookupEdit control……

If it’s LookupMode USER (or Owner, or…..) then it doesn’t use the ‘normal’
properties of a Lookup…..it ignores the TABLE for the lookup (it’s most
often CONTACT even if you are looking up Userinfo.USERNAME for example)….
And it used It’s OWN lookup view (DIALOG control).

Also it display’s data based on the databound TEXT field ….but displays
the UserName in the Text field as well (Format Type property = ftUSER)…..
So strUSERID = lueUSER.TEXT…..unless they just popped up the form…then
you can use strUSERID = lueUSER.LOOKUPID….

So Lookup Restrict stuff DOESN’T WORK.

You have to reconstruct the LookupEdit control as a USERINFO table
lookup…..base it on a USERINFO Table Lookup……

Return the USERNAME in the TEXT property……USERID in the LOOKUPID
property….and Lookup by LAST NAME or whatever…..

No need for a custom form……although you can go that route.

Most developers aren’t highly aware of this ‘feature’ functionality of the
LookupEdit Control……and be aware of the difference between Lookup Mode
OWNER and a lookup of the SECCODE table……