Using Combobox bound column

Hello Experts,

I have a combobox with 2 colums (Col. Widths = 0", 2").

It's Controlsource is a Query with 1 table.  The table has 2 text fields:
fldID
fldNames
and the Query has both of the fields.(ID is the first field in both the Table and the Query)

I want to be able to type in the ID of the person in the Combobox and have the name show up in the field when I hit <Enter>, but not show the ID.

So, if the ID of Person1 is L, I want to type L and hit <Enter> and have "Person1" show up in the Combobox

I could tell you about what I've tried so far, but since it didn't work, I'll just suffice it to say I don't know how to do it.

Thanks,

Mac
LVL 4
MacRenaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peroveCommented:
You are asking for something that cannot be achived. What I do in this situation is to create textbox that are linked to the combo, so that whenever the combo are updated the textbox are to.

If you set the colwith to 1;4 (id, name) and create a textbox where controlsource are:
 =[MyComboBox].[Column](1)

let me know
perove
RRRCommented:
Hi, MacRena.
Try to put on your form second combobox.
Maybe user want to select from combo by ID number or by Names (so, combo1 is ID number and combo2 is name).
Set this comboboxes to your existing query - in combo1 boundcolumn = 1 and columnswidth = 2;0
and in combo2 boundcolumn = 2 and columnswidth = 0;2

On AfterUpdate combo1 paste :
Me.Combo2=Me.Combo1

and on AfterUpdate combo2 paste :
Me.Combo1=Me.Combo2

It will show you both ID and Name.

Good Luck
RRR.
JimMorganCommented:
Mac:

You can trick the form into doing what you want it to do.  This will work with a single form or a continuous form.

Add your combo box.  It should have two fields, the ID and the Name.  Format the box to have 2 columns and set them to the size that you want.  Both should be visible in order for this to work.  The bound column in the ID field.  Make the background transparent.

Add a text box to the form.  Make the text box the same size as the combo box minus the size of the drop down arrow.  Make its border hairline and transparent.  Make its background normal.  Give it the same color as the combo box.  Place it directly over the combo box.  Remove its label.  The control source of the text box is the combo box.Column(1).  The value of the name.

After the user enters L in the combo box either by typing it in or using the drop down, the associated name will appear instead of the ID as soon as the combo box loses focus, at which time the text box is filled.

What happens is when you type or use the drop down, you are seeing the combo box.  When you leave, the combo box becomes transparent and you see only the text box value.

It makes a neat trick and looks exactly like you want.  The only problem which might occur is if the user tries to type in the name instead of the ID.  The combo box will complain.  You can let them use the drop down or you can take the value entered and do a lookup and replace the ID in the combo box.  I'll leave it up to you to play with it further.

Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

brewdogCommented:
I confess I'm a little confused, MacRena. If you're hiding the ID column, why would users type in the ID? Why not just show both columns in the combo box, or have the combo box for ID and then a related text box (which could be filled in any number of ways) for the name?
MacRenaAuthor Commented:
Hi Guys,

The nurses want to type in a letter or number and have the doctor's name magically appear, but they don't want to see the ID.  If I can do it, that's what the customer ordered.   I thought of other ways of doing it, but I thought that this should be do-able without too much overhead.  Maybe I'm wrong.

If I just have 1 column, they begin typing the name and as soon as Access recognises the name, it fills in the rest.  I want to go one better and have a field that does that with 1 or 2 characters, then shows the content of the second field.

Thanks for the help.

Mac
brewdogCommented:
Sounds like you're going to have some strange (a) data entry people or (b) ID numbers. Or both.

Do the users want the name to appear immediately upon typing an "L" or do they want to leave the field and then have it appear? If the situation is the former, then Jim has a nice solution. I had thought about storing the character they typed, doing a search and then filling the combo box with the first matching name, which would work, too. If you like this idea, let me know and I'll flesh it out a little more.
BrianWrenCommented:
In the NotInList event of the combo box, use the NewData argument to look for the record that you are after, (create and search a local recordset), and if you find it, use DataErrContinue after setting the combo box equal to the ID.

(Another way to 'Fool' Access...)

Brian
MacRenaAuthor Commented:
Thanks, JimM.  That worked great!

That does EXACTLY what I want it to with very little overhead

Thanks to all you guys who contributed!

Mac
JimMorganCommented:
Mac:  You know me.  Always glad to comply.  I don't even know why you bother to throw these things out on the open forum.  Just ask me!  :-)

Jim
MacRenaAuthor Commented:
Jim,

I am always very careful not to take advantage of very generous friends.

It's bad enough that I only have 50 points to offer.

I'm furious busy these days, but I'll get in touch with you.

Mac
JimMorganCommented:
You know what I have to say about points... :-(

Jim
MacRenaAuthor Commented:
Hey JimM,

How about instead of a Dr's name I wanted to have a Hyperlink field behind the 2nd column with URLs in them, then when the ID is selected, the URL appears in the Textbox.  If I put a "Go" button next to it, do you have any idea what command I could put in the cmdGo.Click event to execute the URL?

Thanks,

Mac
JimMorganCommented:
Mac:  There may be a couple of commands that you could use.  When you press the command button, you would have to set focus on the combo box and then use one of these.  I'm sure one of them will provide you with what you want.

   Me.ComboBox.SetFocus

   DoCmdRunCommand acCmdOpenNewHyperlink  (Opens hyperlink in a new window)

   DoCmdRunCommand acCmdOpenHyperlink

or

   DoCmdRunCommand acCmdOpenURL

Jim

P.S.  Did you know that you can buy another 200 asking points for $20.00?
MacRenaAuthor Commented:
Jim,

(Sorry for the delay, I'm working on this at home and I haven't gotten to work nights lately)

Setting the focus to the Combobox (which has it's ControlSource to the Name field of the tblURL) and then
DoCmd.RunCommand acCmdOpenNewHyperlink  gave an error "The command or action OpenNewHyperlink is not available now...The type of object the action applies to isn't currently selected."

When I hit Debug it shows the DoCmd. line, then I hit End, it opens the table and highlights the Name in the name field of the table: or in your words >>"The control source of the text box is the combo box.Column(1).  The value of the name. "<<)

The same error occurs when I use the
   DoCmd.RunCommand acCmdOpenHyperlink command.

It seems like the code is trying to execute the command on a representation of a URL, but does not have the URL in it's direct focus yet.  After the failure, it then procedes to set the focus to the table, but to the wrong field (the fldName field, not the fldURL field).   I tried setting the combobox to the URL field, but the same thing happens, only the URL is highlighted, but doesn't execute.

Hitting <Enter> executes the URL.   So it's like I need to set the focus on the URL better before using the Command.

Then there's your third prescription, the DoCmd.RunCommand acCmdOpenURL just opens an Input box prompting the user to type in a URL.

So, it looks very much like your medicine is very close.

I want it to Not show the opening of the table, but execute the URL that is in the textbox (with it's control source being the combobox).

I thought setting the focus to the textbox might help (since it is showing the URL) but the same error occures.

Any further ideas? (I would start a new Q but I'll just give you points later)
JimMorganCommented:
I'm overwhelmed with stuff on EE at the moment.  Can you send me the DB and let me look at it over the weekend?  I think that this will give me a better handle where to go next.

Jim
MacRenaAuthor Commented:
Hi Jim,

I couldn't figure out why it always opens the table and highlights the value, but doesn't execute the URL.

So I scrapped the whole thing, made it so the URL textbox looks at the table, rather than the Combobox and it works fine

Thanks for the offer.

We got 8 inches of snow last week and another 8 inches last night, so my back is Not Good!  (39 with a history in the Foundry industry)

Mac
JimMorganCommented:
What ever works?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.