MS Access - return value of two columns from combobox added
Howdy,
I've searched high and low, and maybe it's my lack of terminology but basically here is the problem.
I have a dropdown combobox in a database that displays two columns from a different mdb file. In the combobox Row Source field I have - "Select OtherMDB.Surname, OtherMDB,FirstName FROM OtherMDB;"
What I want to do is select the data and for it to return in one field in the new MDB database "Surname, FirstName"
So basically add the two columns together as the result. Any ideas?
I've searched high and low, and maybe it's my lack of terminology but basically here is the problem.
I have a dropdown combobox in a database that displays two columns from a different mdb file. In the combobox Row Source field I have - "Select OtherMDB.Surname, OtherMDB,FirstName FROM OtherMDB;"
What I want to do is select the data and for it to return in one field in the new MDB database "Surname, FirstName"
So basically add the two columns together as the result. Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
peter57r
Based on your stated rowsource, you should use indexes 0 and 1 rather than 1 and 2 as Bill indicated.
ASKER
Thanks a mill.
ASKER
Just trying to clarify, I'm very rusty on SQL and dont use ACCESS that much
Can I get it to state the return "Surname, Firstname" in the same combobox once it's selected?
Say the combobox is named "ComboFirstSecond" I take it I throw that into the "After Update" of the combobox - ComboFirstSecond = ComboFirstSecond.Column(0)
Can I get it to state the return "Surname, Firstname" in the same combobox once it's selected?
Say the combobox is named "ComboFirstSecond" I take it I throw that into the "After Update" of the combobox - ComboFirstSecond = ComboFirstSecond.Column(0)
Hi,
That's correct. In the AfterUpdate event of the combo box put the code:
yourtextbox = ComboFirstSecond.Column(0)
Where yourtextbox is the name of the desired target text box.
Regards,
Bill
That's correct. In the AfterUpdate event of the combo box put the code:
yourtextbox = ComboFirstSecond.Column(0)
Where yourtextbox is the name of the desired target text box.
Regards,
Bill
ASKER
Thanks Bill. I take it I can't get the combo box to be the target text box? I'll just update it so that when it's updated the combo box is made invisible and the target text box is made visible in its place.
Hi,
You can only display a single column but...
You could create a computed column in the combobox's rowsource query and display it that way. You can display the computed column and bind the combo box to a different column in the property sheet.
Regards,
Bill
You can only display a single column but...
You could create a computed column in the combobox's rowsource query and display it that way. You can display the computed column and bind the combo box to a different column in the property sheet.
Regards,
Bill
+1
For that idea... ideally display concatenated first and last name in one column and set the bound column to a primary key/id field.
If you set the width of the ID column to zero, your 'full name' will automatically be what gets displayed in the text portion of your combo.
For that idea... ideally display concatenated first and last name in one column and set the bound column to a primary key/id field.
If you set the width of the ID column to zero, your 'full name' will automatically be what gets displayed in the text portion of your combo.
Actually, I like my people combo boxes to have only two columns (an ID, and a Name), but I display the name both ways (First Last and Last, First). That way, regardless of how the user starts typing the name, they will find it. My SQL usually looks like:
SELECT ID, FullName
FROM (
SELECT ID, [Last] & ", " & [First] as FullName, 0 as SortOrder
FROM myTable
UNION
SELECT ID, [First] & " " & [Last], 1 as SortOrder) as Temp
ORDER BY Temp.SortOrder, FullName
This would present the user with:
2, "Jones, Steve"
1, "Smith, John"
1, "John Smith"
2, "Steve Jones"
Note that the ID is not unique, so if you set the combo to a particular ID value, it will display the first value that matches the ID.
SELECT ID, FullName
FROM (
SELECT ID, [Last] & ", " & [First] as FullName, 0 as SortOrder
FROM myTable
UNION
SELECT ID, [First] & " " & [Last], 1 as SortOrder) as Temp
ORDER BY Temp.SortOrder, FullName
This would present the user with:
2, "Jones, Steve"
1, "Smith, John"
1, "John Smith"
2, "Steve Jones"
Note that the ID is not unique, so if you set the combo to a particular ID value, it will display the first value that matches the ID.
ASKER
Brilliant! Fantastic work, I should be able to get that to work!