Rowsource - lookup by other-than-bound column

Expanding on http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28220250.html:

what if the 1st combobox has more than one column, and we want to look up by the 2nd one, which is not the bound column?
LVL 40
Vadim RappAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
First Column:  YourCombobox.Column(0)
Second Column: YourCombobox.Column(1)

These values are "Zero Based"
0
Jeffrey CoachmanMIS LiasonCommented:
<asside>
what if the 1st combobox has more than one column, and we want to look up by the 2nd one, which is not the bound column?

Just note that in a typical combobox the first column will be the Primary key and will be hidden.

The second column will be the "human readable" value/text
Example: CustomerID, CustomerName

Therefore there may be several companies named: "Acme Plumbing"
But each will have a different CustID (why we search on the ID, not the name)

Hence doing a looking up on "Acme Plumbing" will return the *First* match (perhaps not the record you wanted)

Also not that some combobox code has trouble with apostrophes.
ex.: Mark's, Sally's

Finally note that it is always easier to work with numeric data in general (in VBA)

To lookup Number data you only need:
Dlookup("Field","Table","YourFiled=" & YourVariable)

To look up text you will need something like this:
Dlookup("Field","Table","YourFiled=" &  "'" & YourVariable & "'")

(When building SQL stings in code, this gets even more complicated)

;-)

Jeff
0
datAdrenalineCommented:
Jeff,

When working with literal string building for criteria, I encourage the rule --- "Double up your delimiter of choice"

So .. if you use a single quote (apostrophe) as your delimiter ...

Dlookup("Field","Table","YourFiled='" & Replace(YourVariable,"'","''") & "'")

Open in new window


Or ... if you use a double quote as your delimiter ...
Dlookup("Field","Table","YourFiled=""" & Replace(YourVariable,"""","""""") & """")

Open in new window


vadimrapp1,

In a RowSource SQL as we encouraged in the referenced question, you can use the following syntax:

SELECT someTable.someField
FROM someTable
WHERE someField =  Eval("[Forms]![someFormName]![someComboOrListControl].Column(1)")

Open in new window


Where the number you specify is the index of the column you want.

As a side note: If I have a Query object that requires a Form based parameter, I will wrap the parameter with the Eval() as I have shown in order to give me the flexibility of using that Query object with DAO.Database object methods like .OpenRecordset or .Execute.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
datAdrenaline,
Yes, I use that, I was simply pointing out to the OP that some combobox code will throw an error if there is an apostrophe ...
...I did not want to distract too much form the original q...

;-)

Jeff
0
datAdrenalineCommented:
"...I did not want to distract too much form the original q..."

I kinda figured you knew all that, but .... since the topic got opened up ... I went ahead and got distracted ...
... ooooOOOOOoooo, whats that ... its shiney! ....
0
Jeffrey CoachmanMIS LiasonCommented:
detA...,

Your post did provide a lot more additional info...

I'm like you, the more info available to the OP, ...the better...


Simple threads like this show how dedicated some of the Experts are to answering even the most basic questions, with robust explanations...

;-)

Jeff
0
Vadim RappAuthor Commented:
datAdrenaline get the prize for the solution with no coding, and using eval :-)
0
datAdrenalineCommented:
Woo hoo! ... Thanks vadimrapp1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.