can access 2003 combo box continue to display SEVERAL dropdown columns AFTER a row is selected?

In drop down view my combo box is 14 rows high and 6 inches wide (from the "List Width" property).  When a row is selected, it shrinks to 1 row high and 4 inches wide (from the "Width" Property)


The dropdown view has 6 columns each about 1 inch wide. (employee name, employee Number, city,  zip,  telephone, cell phone)

After I select a row (e.g. "Joe Doe") the 14 rows are replaced by a single row with 4 inches devoted to the first data column (employee name).  

Instead of 4 inches devoted to name, I would like to show as many of the 6 columns as will fit.  

I think this is impossible to accomplish with just a drop down box.  I would have to create a text box or subform which was aligned on top of the combo box which is so much work, that I have never bothered to do it.

I wonder if there is an easier way to do this?

rberke

P.S.  I have always been surprised that Combo Boxes do not give better control over what gets displayed.  I have seen other people ask why they can't chose to display the second, third or fourth column and the answer is usually "just because".  Maybe some future version of Access will support a simple property like DisplayColumn = nn. Today's request would need something slightly more flexible  like DisplayColumns={FromDropDown|nn}.  
LVL 5
rberkeConsultantAsked:
Who is Participating?
 
als315Connect With a Mentor Commented:
You can also combine all columns to one string and show it
0
 
als315Commented:
You can use few textboxes with value set to:
=Me.MyComboBox.Column(n), where n - column number in combo box, starting from 0
0
 
rberkeConsultantAuthor Commented:
Those boxes you are suggesting will take up more room on the form unless I align them on top of the combobox.  If I align them on top of the listbox, I have to hide them when someone clicks on the dropdown arrow.  They should then become unhidden when the combobox loses focus.

Plus, i have to put code to populate them (probably in the lost focus event). Also, if future modifications rearrange or resize the combobox rowsource, I have to rearrange the textboxes etc.  

By a "lot of work", I don't mean days, but I could see it taking at least an hour or two to make it look nice, plus extra work in future maintenance projects.


I hope Access 2010 has a feature like  DisplayColumns=FromDropDown, but I'll bet it does not.

Bob
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
"After" a value is selected, a combobox will only display One Field (Typically the first visible field.)

What als315 posted is one popular way of showing multiple fields.

Another way is to simply create a textbox for every other value you want to display.
set the control sources to each of the columns as follows:

=YourCombo.Column(1)
=YourCombo.Column(2)
...etc

JeffCoachman

0
 
peter57rCommented:
No points here...
You can only display the first non-zero-width column in a combo, once a value has been selected.  Anything more requires the solution posted by als315.  Access 2010 is no different to earlier versions in this respect.
0
 
rberkeConsultantAuthor Commented:
The single string approach seems to be the best. I'll explain my results shortly.

Also, JeffCoachman's suggestion of using column(1) gives an error message.  I actually will not use this method because I like the single string better, but I thought the error message was worth commenting:

The error repeats if I make any design change to the row source.  For instance a "dummy" change of (1)  to (1) it gives the same error.

Another irrelevant point is that the syntax you gave was slightly wrong, the correct syntax is =[cboPickEmpNo].[column](1).

But the good news is that the error message only occurs during design.  When the form is running it seems to work fine.  


Here is the error message

"The method you tried to invoke on an object failed.

* you may have specified too many or to few arguments for a property or method of an object.  Check the component’s documentation for information on the properties.
and methods it makes available for Automation operations.                                                          
* There may not be enough memory to run the procedure. Close unneeded programs and try to run the procedure again.
For more information on freeing memory, search the Microsoft Windows Help index for 'memory, troubleshooting'.      
0
 
rberkeConsultantAuthor Commented:
I can't seem to align the text box "on top" of the combo box.  Access always hides the textbox underneath the combo box.  I'll play some more.

0
 
Jeffrey CoachmanMIS LiasonCommented:
<the syntax you gave was slightly wrong, the correct syntax is =[cboPickEmpNo].[column](1).>
Technically that is the "Refined" Syntax that Access modifies this to.
The square brackets allow for names with spaces...
;-)

The syntax has worked fine for me for years.
Can you clearly state how you tried doing this, because I really don't know what you mean by:
<the error message only occurs during design.  When the form is running it seems to work fine. >

Again, probably all irrelevant, if the concatenated method is what you needed is what you wanted...


In any event see frmCust in the attached sample
Database64.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I can't seem to align the text box "on top" of the combo box.  Access always hides the textbox underneath the combo box.>

In Design View, you have to select the Field you want "In front" (on top)
Then click: Format-->Bring To Front
0
 
rberkeConsultantAuthor Commented:
That does not work.  Access really want to keep the combo box on top of a text box.

In design view text box clearly shows on top.   But in form view it clearly shows underneath. I'll bet if you try it on access 2003 you will see the same thing.

Anyway I resorted to some trickery to accomplish the same thing.

I made a "fake" combo box.  It looks like a single combo box, but the left hand side is actually a text box and the drop down arrow is a "real combo" but very thin combo box.

Here are the details:

I manually "shrunk" the cbo by dragging the left edge until only the dropdown arrow showed.  

I then moved the text box to the left of the arrow, so the two controls together looked like a single combo box.

Then the following code changes alters the real combo box at appropriate times:  
When the user clicks on the dropdown it real combo become bigger and overlaps the textbox.  
When the user selects a new combo item, the combo shrinks back to being thin so the text box displays.

Like I predicted this took over 2 hours and was more trouble than it was worth. I'll award points soon.

cbo so that it only showd

0
 
rberkeConsultantAuthor Commented:
boag2000: you .mdb won't open with access 2003 or access 2007.



 
0
 
rberkeConsultantAuthor Commented:
boag2000: I apologize about the syntax. It gave that error message, so I originally thought the syntax was wrong.

It was quite a while later that I realized it was the error message that was wrong. It appears to be a bogus error message.  

When I repaired the database the error went away.
0
 
rberkeConsultantAuthor Commented:
weird things happening. I will post a database soon
0
 
rberkeConsultantAuthor Commented:
I am trying a simpler approach that does not require resize the combo box.

There were two reasons why combo box was always "on top" despite my design attempts to put it lower.

Both reasons were because the combo box has focus. As long as it has focus it will be on top of inactive controls.

fix #1 When I opened the form the combo box was active because its tab order was lower than the text box. I changed the tab order, and that caused the text box to be on top after form opened.  

But it isn't a complete fix because selecting a dropdown entry leaves the combo box with focus. So, I tried to setfocus on the Text box  and got an error.



Can anybody suggest how to fix that.  Here is a sample database that demonstrates the problem.  (Please note, I have intentionally left the two controls slightly misaligned so it easier to see what is going on. When I am done, I will align them completely.)
eeQuest.mdb
0
 
rberkeConsultantAuthor Commented:
and, if anybody is interested, I forgot to post  the code for "resizing" the combo box.  But I don't like it and expect a much simpler solution soon
Private Sub cboPickEMPNUM_Change()
Debug.Print "ok"
With [cboPickEMPNUM]
Dim i, str
' Stop
For i = 0 To .ColumnCount - 1
str = str & " | " & .Column(i)
Next i
                             [Text48].VALUE = Mid(str, 4)
End With
activecontrol.Left = 8100 ' 1440 per inch
activecontrol.Width = 166
End Sub


Private Sub cboPickEMPNUM_LostFocus()
Call cboPickEMPNUM_Change
End Sub

Private Sub cboPickEMPNUM_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If activecontrol.Left = 0 Then Exit Sub
activecontrol.Left = 0
activecontrol.Width = 8266 ' 1440 per inch
End Sub

Open in new window

0
 
rberkeConsultantAuthor Commented:
I got my posted database to work.  I just had to move the code to the afterUpdate event.  Here is the code. The next time it will be a lot easier.

I'll leave this open for a while then post points.
Private Sub Combo0_AfterUpdate()
With [Combo0]
Dim i, str
For i = 0 To .ColumnCount - 1
str = str & " | " & .Column(i)
Next i
[Text2].Value = Mid(str, 4)
[Text2].SetFocus
End With
End Sub

Open in new window

eeQuest.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
I think you misunderstood the "Single String" (concatenation) approach first suggested by als315..........

Simply do this:
(See Form2 in the attached file)

Much simpler than going through all of the "hidden/fake control" machinations
No code whatsoever is required...

Points to als315....
;-)

JeffCoachman
eeQuest2.mdb
0
 
rberkeConsultantAuthor Commented:
Yes, I did misunderstand.  Your approach is nice I will play with it over the weekend.  I have to add a second column but I think it will be fine.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Remember als315b actually suggested this approach first, I only created a sample based on that technique.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.