?
Solved

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

Posted on 2011-05-12
19
Medium Priority
?
267 Views
Last Modified: 2012-06-27
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}.  
0
Comment
Question by:rberke
  • 11
  • 5
  • 2
  • +1
19 Comments
 
LVL 40

Expert Comment

by:als315
ID: 35748213
You can use few textboxes with value set to:
=Me.MyComboBox.Column(n), where n - column number in combo box, starting from 0
0
 
LVL 5

Author Comment

by:rberke
ID: 35748532
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
 
LVL 40

Accepted Solution

by:
als315 earned 1200 total points
ID: 35748867
You can also combine all columns to one string and show it
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 800 total points
ID: 35748946
"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
 
LVL 77

Expert Comment

by:peter57r
ID: 35748953
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
 
LVL 5

Author Comment

by:rberke
ID: 35750166
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
 
LVL 5

Author Comment

by:rberke
ID: 35750185
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35750304
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35750363
<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
 
LVL 5

Author Comment

by:rberke
ID: 35750825
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
 
LVL 5

Author Comment

by:rberke
ID: 35750890
boag2000: you .mdb won't open with access 2003 or access 2007.



 
0
 
LVL 5

Author Comment

by:rberke
ID: 35750904
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
 
LVL 5

Author Comment

by:rberke
ID: 35750981
weird things happening. I will post a database soon
0
 
LVL 5

Author Comment

by:rberke
ID: 35751098
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
 
LVL 5

Author Comment

by:rberke
ID: 35751103
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
 
LVL 5

Author Comment

by:rberke
ID: 35751140
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35751770
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
 
LVL 5

Author Comment

by:rberke
ID: 35754770
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35758465
Remember als315b actually suggested this approach first, I only created a sample based on that technique.


0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

839 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