Vadim Rapp
asked on
Rowsource - lookup by other-than-bound column
Expanding on https://www.experts-exchange.com/questions/28220250/Update-the-rowsource-property-of-a-second-control.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?
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?
<asside>
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","Y ourFiled=" & YourVariable)
To look up text you will need something like this:
Dlookup("Field","Table","Y ourFiled=" & "'" & YourVariable & "'")
(When building SQL stings in code, this gets even more complicated)
;-)
Jeff
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","Y
To look up text you will need something like this:
Dlookup("Field","Table","Y
(When building SQL stings in code, this gets even more complicated)
;-)
Jeff
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
"...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! ....
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! ....
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
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
ASKER
datAdrenaline get the prize for the solution with no coding, and using eval :-)
Woo hoo! ... Thanks vadimrapp1
Second Column: YourCombobox.Column(1)
These values are "Zero Based"