Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access - return value of two columns from combobox added

Posted on 2013-01-29
11
Medium Priority
?
520 Views
Last Modified: 2013-01-30
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?
0
Comment
Question by:AGOIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 14

Accepted Solution

by:
Bill Ross earned 600 total points
ID: 38831943
Hi,

newtextbox = combobox.column(1) & ", " & combobox.column(2)

will concantenate the text from the 1st and 2nd columns in the combo box and place the result into newtextbox.

Regards,

Bill
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 200 total points
ID: 38831955
BillDenver has your answer - however Combo and Listbox column counts are zero based.  So the first column in your rowsource query is actually column 0 then 1,2,3 etc
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38831959
Based on your stated rowsource, you should use indexes 0 and 1 rather than 1 and 2 as Bill indicated.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Closing Comment

by:AGOIT
ID: 38831965
Thanks a mill.
0
 

Author Comment

by:AGOIT
ID: 38832046
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) & ", " & ComboFirstSecond.Column(1)
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 38832158
Hi,

That's correct.  In the AfterUpdate event of the combo box put the code:

yourtextbox = ComboFirstSecond.Column(0) & ", " & ComboFirstSecond.Column(1)

Where yourtextbox is the name of the desired target text box.

Regards,

Bill
0
 

Author Comment

by:AGOIT
ID: 38832192
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.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 38832343
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38832389
+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.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38832482
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.
0
 

Author Comment

by:AGOIT
ID: 38834254
Brilliant!  Fantastic work, I should be able to get that to work!
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question