Solved

Viewing 2 columns in a Access 2003 combo box from a look up table

Posted on 2014-11-05
26
206 Views
Last Modified: 2015-04-09
I want to display a 2 column look up table as a combo box in a form.  

I've created the table.

I've gone to the form and tried to add it as a combo box but can't get both columns to be visible.

What am I doing wrong?
0
Comment
Question by:Angelia1
[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
  • 11
  • 4
  • 4
  • +4
26 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 40424318
Do you have it specified as a SQL query?  Are both fields in your select: SELECT field1, field2 FROM table...

And did you set the column width property with the appropriate widths? e.g. 1";1.5"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40424319
set the column count property of the combo box to 2
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40424320
1. Click on the combo box and Go to Properties (F4).
2. Change Column Count to 2
3. Change Column Widths to (e.g.) 3cm;8cm
4. Go to Row Source, click on the ..., and ensure that there is more than 1 column visible in the Query Builder.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40424326
5. Change Bound Column to whichever column you want bound.
6. If you want another text box to show the second column (once it has been clicked), change its Control Source to
=[myfield].[Column](1)

Where myfield is the name of your field, and (1) indicates the second column.
0
 

Author Comment

by:Angelia1
ID: 40424403
Thank you all for answering so quickly.  (sometimes my thought process isn't as clear as it is at other times so tell me if this sounds like the right path)

I have a table called physicians

I have a qry w these fields in it that I make all of my forms off of but, in this case i was going to use the tbl, physicians, directly as a look up table by making a combo box in my form.  This would list the physician their phone, fax and specialty (ie cardio, oncology and so on.)

I do have section in my db that Microsoft set up yrs ago using the same table where we choose the referring physician and it auto populates address phone/fax etc.  There are event proc attached to this.  Would it be better to try and recreate this scenario  by trying to follow what was done in the properties and copying the event procedures, changing the names of the records somehow to match specialist list now in place of a referring phy list as it was originally used?  Or should I stick w the combo box idea?

SideNote: I don't know how to write code or create event procedures. Doesn't mean I can't learn)   below is the event procedure as it is written for the referring phy box. (May be biting off more than I can chew but I thought I'd ask your advice first)

Private Sub cboMD_AfterUpdate()
Me.strRefMd = Me.cboMD.Column(2) & " " & Me.cboMD.Column(1)
Me.strRefMDAddress = Me.cboMD.Column(4) & " " & Me.cboMD.Column(5)
If Me.cboMD.Column(6) = "" Then
Else
Me.strRefMDCity = Me.cboMD.Column(6)
End If
If Me.cboMD.Column(7) = "" Then
Else
Me.strRefMDState = Me.cboMD.Column(7)
End If
If Me.cboMD.Column(8) = "" Then
Else
    Me.strRefMDZip = Me.cboMD.Column(8)
End If
If Me.cboMD.Column(9) = "" Then
Else
    Me.strRefMDPhone = Me.cboMD.Column(9)
End If
If Me.cboMD.Column(10) = "" Then
Else
    Me.stsrRefMDFax = Me.cboMD.Column(10)
End If
If Me.cboMD.Column(12) = "" Then
Else
Me.strMdType = Me.cboMD.Column(12)
End If
Me.strMDLName = Me.cboMD.Column(1)
Me.strRefMDID = Me.cboMD.Column(0)
Me.strMDFName = Me.cboMD.Column(2)
End Sub

Thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40424433
in the ddesign view of the form, select the combo box and hit F4
select the Event tab
look for the event  On Click
click the Down arrow and select [Event Procedure] and click on (...) to get to the VB editor
0
 

Author Comment

by:Angelia1
ID: 40424462
Ok, now what?
I'm at a box w 3 options
Expression Builder
Maco Builder
Code Builder
0
 

Author Comment

by:Angelia1
ID: 40424464
Right now the combo box I created is only showing the first name of the phy.  I chose multiple fields to be displayed but it only shows first name.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40424490
are you reading the suggestions?
0
 

Author Comment

by:Angelia1
ID: 40424496
I am and trying to follow them w my limited experience.
0
 

Author Comment

by:Angelia1
ID: 40424514
for example where does this go?   =[myfield].[Column](1)     If I put it in the event procedure section after clicking the (...) it gives me a compile error
0
 

Author Comment

by:Angelia1
ID: 40424606
What do I do after going to the VB Editor please?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40424674
sorry, you should look for the After update event, not On click

Private Sub cboMD_AfterUpdate()

'you will place here the codes that will be run/process when the after update event for 'the combo box is triggered

End Sub
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40424709
I created a sample database a few years ago that should explain how to do what you need to do.  One thing to keep in mind is that in a properly defined relational database, we do NOT duplicate data.  So for example, we don't keep the customer name in the order table.  We keep the CustomerID.  When we want to show the customer info on the order form, we use a query that joins tblOrder to tblCustomer and pull fields from both tables.
Picture of sample form showing three techniquesFillFormFields130127.zip
0
 

Expert Comment

by:sharris_glascol
ID: 40424919
my problem is that I am updating fields into a table and when I select a different position then the other fields courses go away.  It is happening because of the after update on the position drop down.  Any idea on how to fix this?
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40424936
If you are using a continuous subform, this example will explain the problem and offer a solution.
FixCascadingCombos130128.zip
0
 

Expert Comment

by:sharris_glascol
ID: 40424976
I am confused on changing the subform to a query.  will this not update the table.
0
 

Expert Comment

by:sharris_glascol
ID: 40425032
I fixed it Instead of storing the ComboID I stored the Comboname
0
 

Author Comment

by:Angelia1
ID: 40425054
Hi, back to my original question.  How do I view more than one column on a form using a table as a look up in a combo box or is this the best solution to that?
0
 

Author Comment

by:Angelia1
ID: 40425060
I do not have experience in writing code or creating event procedures.  

Currently, even though I have several fields chosen only the MD's first name appears in the box on my form.  I want to be able to view the entire name as well as the phone and fax numbers
0
 

Author Comment

by:Angelia1
ID: 40425099
So by looking at what Pat has sent, the BEST case does not use code but could you be more specific on how that is set up please?  This looks like exactly what I need but I don't understand how to make it happen.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40425163
If you just want to see multiple columns when the combo is closed, you have to concatenate them in the query.  Otherwise, once you select an item, you only see the first visible column.

Select EmpID, FirstName & " " & LastName as EmpName
From tblEmployee
Order by FirstName & " " & LastName;

EmpID is the bound column and it is what will be saved.  But by concatenating first and last names, you will see both in the control when the employee is selected.

To implement the BEST solution, you use a query that joins the main table to the lookup table.  For example, if you want to see the customer name and phone number on the order form, join tblOrder and tblCustomer and select columns from both tables.  Since the form is for entering an order, columns from tblCustomer that you bind to controls should have their locked property set to Yes because you don't want someone accidentally changing a customer name on the order since that will change the customer name in tblCustomer and so change the customer name for ALL orders.
0
 

Author Comment

by:Angelia1
ID: 40432610
Pat,
I am drawing the fields from a qry.  All the info does show up when I hit the drop down but when I close it it only shows the MDs first name.
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40432925
I know.  Please read my previous response again.  I told you how to concatenate multiple columns so you could see them all in the same box.
0
 

Author Comment

by:Angelia1
ID: 40440716
Ok, I take another look
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40715515
Hey Angelia1 -  How's it going?  Looks like you've received multiple answers to your original question, so please consider closing this out by accepting the answer(s) that helped you.
0

Featured Post

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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