Combo Box Text box combination

On my MS Access  unbound  form I  display info about Product.
Values for form fields are coming from saved values in table tblProduct.  One of the field in table Product is field  named “ Colour”.
Scenario is next:
Where form is open I have search box for product search I find product and display info about Product on the form fields.
Now I want to edit value for Colour.
I have Colour values in tblColours and for now I have five values = White, Green, Black, Red, Yellow.
For editing value in txtColour I want combo box option.
I want combo box cboColours to select value and then selected value to put in text box txtColour.
However I do not want to see both text and Combo box on form. It should look like just as one drop down box old the time.
Need something as hiding one behind another and showing selection or changed value .
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried setting the LimitToList value of the Colour combo to No? This would allow users to either (a) select a value from the list or (b) enter their own value.

This method would NOT add the user-entered value to tblColour, however. If you want to do that, you'll have to involve the NotInList event.
TarasAuthor Commented:

Combo box has set up limtToList is set to Yes.
However, that is not point here.

Combo box  source is tblColour and it has preset values that are updated  on separate form  and through data maintenance part.

Point hire is that I do not want  user to see two controls.  I want to give user an illusion that it is one control. He should see just one control that looks like combo box.

I do not want a form  layout where I have at the same time two visible controls:  One  control  text box txtColour to display a value and another control   combo box cboColour  to select  another-existing  value in order to edit value in text box txtColour.

I was thinking about option hiding one showing another or overlapping them something like that.
I see  txtColour as place – container  where I will capture incoming  value from tblProduct and  kind out coming  value from combo box cboColour  to be saved in tblProduct.
Combo box will  be used to just to select preset colour .

It means when record is brought on the form value of colour field  from table tblProduct will go to hided text box  txtColour.
Combo box will be visible all the time and at that  moment I am not sure,  should have something like : CboColours. default value =  txtColour.value
then on After update event of cboColour I should  switch - put value from combo box to text  box and be ready for save event.
Something like that but not sure is that possible and good design or another way is possible.I would appreciate some example.
Read Scott's answer again.  He was not suggesting two controls.  He was suggesting a change to the combo so that it could be bound to the color field and still accept values that were NOT in the list.  That way, regardless of whether you picked an option from the list or typed it in, it would be saved in the color field.

Personally, I don't recommend that you allow people to enter values into a field controlled by a combo because even employing the NotInList event so that you can update the RowSource, it encourages people to simply type in their mis-spellings.  At least if they have to go to another form or ask a supervisor to do it, they have a second chance to reconsider.  I realize that sometimes you just have to let them enter values on the fly but avoid it if you can.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TarasAuthor Commented:
My English probably is making confusion.  Sorry for that.
I did not say that Scott suggesting two controls.
I said that I do not want for solution of problem that I described to have two visible controls on form cboColours and txtColour at the same time.
Of Course, there will be two  controls, but not two visible at the same time I was thinking about that scenario if someone can help me with more details and suggestions, example
Why do you think you need 2 controls?  Why do you not want to do what Scott suggested which works with 1 control?
TarasAuthor Commented:
You need to explain it to me in more detail.
I do not understand .
How you will show value of the field "Colour"  from table  tblProduct  on the form and then on the same form in the same control allow user to  change that value with another value that are preset  in another table,  table tblColour and after that save that to tblProduct back.
The combo (listbox works the same way) has 2 bound objects.  It is bound to the form's RecordSource via the ControlSource.  This is the field in the record where you want to save the data or retrieve it from.  The combo is also bound to a RowSource and you specify which column of that rowsource the combo is bound to.  This is usually a table or query that contains the list of values to display.  RowSource can also be a hard coded list of values that is stored in the combo itself rather than in a table.

So, the RowSource contains - Red, Yellow, Blue, Green, White.  Once you choose a value, that value is saved in the ControlSource which in your form is bound to txtColour.  

When the Limit To List property is set to Yes, Access will restrict the values to ONLY the ones in the RowSource.  When the Limit To List property is set to No, Access will let you choose a value from the RowSource OR type in a value that is not in the list.

This ONLY works if your RowSource's bound column is the first column.  Frequently when we use lists we have numeric identifiers as the key field and then a text field that we display.  So in reality, the table that contains the list may have two columns (or more).  The key and the text value.
1 Red
2 Yellow
3 Blue
4 Green
5 White
With this setup, when the user chooses "Blue", the bound value is 3 and that is what is stored in the ControlSource.  In that case, you CANNOT set the Not In List property to No because the combo would not work correctly.

So, to continue this discussion, is the color name the "key" or is there a numeric ID used as the "key" and the color name is just a text value?
TarasAuthor Commented:
Table that is row soruce for combo box cboColour  is table  tblColour. This table  has two fields:
ColourID -  Auto number (key)
Coloour - text.

So answer on your question colour name is not key. There is Numeric ID.
OK.  Since the name is not the key, the combobox can't do double duty as a combo and as a textbox.  Next question.  Why do you need to add options that are not in the list?  Are you sure you can't use the NotInList event to have the user enter them into the table so they will be available next time?  Maintaining a text field to hold the selection is ultimately going to cause problems.  One big one is that you loose control over the contents of the field.  Generally we use comboboxes because we want to control the contents of a field and not let users enter something that is not defined.  If the user can type anything he wants, what is the point of the combo at all?

I have one instance where the user enters notations on a client record and he frequently wants to enter the same note for more than one service.  For this case, I don't maintain a lookup table since the values are really not generic.  The RowSource for this combo selects all the existing values in the field for a particular client and presents those.  That lets the user choose a comment he has already entered or enter a new one.  So the list starts out empty.  After he enters the first note, it has one value.  Then if he enters a second value, the list has two values, etc.

A sample of the comments is.
Daughter, Susan will provide transportation if van is unavailable.
Neighbor, June will do grocery shopping weekly.
TarasAuthor Commented:
I said I do not want them to enter new value.
How do you get that I want option not in the list???

"Combo box has set up limitToList is set to Yes."
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I said I do not want them to enter new value.
I think there's some misunderstanding on our part (and yours, perhaps). Pat has done an excellent job explaining the way bound Combos work, so I won't go over that.

If you do NOT want them to enter a new value, but rather want them to be able to type in a value that is in the list, then be sure the AutoExpand value is set to Yes. This is, basically, "Auto Complete", so if the user types in a "b", the combo would show the first item that begins with a "b". As they type more letters, the combo will attempt to match up the list values with the values entered by the user.

However, if that does not do what you want, it might be best to upload a small sample database that shows the issue.
TarasAuthor Commented:
I really trading to explain what I want but its looks I do not have proper approach or I am sounding so unclear, probably this second.
Scott I said I do not want user to enter new value in combo box!!!
I said  that values for combo box cboColour are coming from “table”   tblColour not from list.
I said that Limit to List is set to “Yes”
In short again:

Point is to use something that “looks” for user like one control. And that one control should be combo box.
This combo box will be used for displaying data from one table(tblColour) and at the same time  user wants to use same control for changing value(with preset values from another table tblColour)  and later on to save this to tblProduct.
It sounds so simple.
For this scenario is it ok to use only Combo box?
If I use only combo box how to do it.
Or If I use combination Combo box and text box   and hiding text box.
How to do it in this case?

As I am saving in tblProduct both values Colour and ColourID I was thinking something as next scenario but I am not sure is this good:
Gave user option to search for the product, bring value on the un bound  form by recordset “rstProd” and display value for colour field  in combo box something as this:
Me.cboColour.defaultValue =“ColourID”)
As in combo box cboColour first field is hidden, user will see only second field – text field  - Colour.
So on this way I will display value from table in combo box and at the same time user have possibility to change that value to  another value if he/she wants to change existing.
Is this good approach for this scenario??
TarasAuthor Commented:
I made mistake:
“This combo box will be used for displaying data from one table(tblColour) and at the same…”
It should be:
This combo box will be used for displaying data from one table(tblProduct) and at the same....
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As I am saving in tblProduct both values Colour and ColourID
You should not be doing that. If you want to relate a record in tblProduct with a record in tblColour, then ONLY store the ColourID value, and NOT the Colour value. If you do that, then you can add a Combo on the "Products" form, and base that combo on tblColour. Set the following properties:

RecordSource: SELECT ColourID, Color FROM tblColour
ColumnCount: 2
ColumnWidth: 0;1

Now when the user selects a value in the combo, the value in the FIRST column (ColourID) would be stored in tblProducts, but the user would see the value in the SECOND columns (Colour) on the form. To show the color (like on a Report, for example) just JOIN tblProduct and tblColour on the relevant fields.

I note, however, that you mention Unbound forms. Are you using Bound forms (i.e. your Form has a Recordsource, and each Control has a ControlSource), or are you using Unbound forms?
TarasAuthor Commented:
Yes I am using unbound form.

You did not say nothing about my idea how to present value of colour field from tblProduct  when search is finished and record is presented on the product  form.
I mentioned option to use default value of combo box:  cboColour.defaultValue =   rstProd("colourID") ??
I'm not sure why you are using unbound forms.  Access is intended to be used with bound forms.  That is the whole point.  If you are not taking advantage of the RAD (rapid application development) features of Access, then there is no point in using Access at all.

We've been trying to explain to you how combo boxes work.  What you see displayed is not always what will be stored in the underlying record.  But of course since you are not using bound forms NOTHING will be saved that you don't write code to save.  The RowSource of the combo/listbox is a zero-based array.  If the query to populate it selects 2 columns then you reference them as Me.cboColor.Column(0) - to reference the first column and Me.cboColor.Column(1) to reference the second column selected by the RowSource query.  The default property of the combo/list box is the .value property and that is a shortcut way to reference .Column(0).  Therefore - Me.cboColor.Column(0), Me.cboColor.Value, and Me.cboColor all actually refer to the first column of the RowSource.  Remember the RowSource is what produces the list that drops down.  For a bound combo on a bound form, the ControlSource refers to the field in the form's RecordSource query that the control is bound to.  In a bound form, Access automatically takes care of populating and saving whatever is in the bound controls.  In your case, you need to write code in various form events to cause EVERY piece of data to be saved.  Once you get the RowSource query so that it selects the two columns you need and you get the ColumnWidth property defined correctly to HIDE the first one and show the second one, then you don't need to have two controls on the form.  One control handles everything.  Then even though the control is showing the text value of color, you would use the .Column property to refer to the column that you actually want to save.
TarasAuthor Commented:
Pat I agree with you that access Is good with bound forms, however from point of my small access and VBA experience I  know that there are number of situation when you will use access with un bound forms  on that way you get more freedom to handle flow of application end to follow user’s  desired way  of steps.
Not to mention using access as front end and SQL Server as  back end scenario. I know you will probably say that even in that case bound forms are better,  I will not argue that.
I will not go now in discussion about bound vs un bound access forms.  And con and pro it is another topic.
 That what you said is actually what I will use,  one combo box control and use its default value to display found field from tblProduct and after that use  RowSource query to give user choices to change value and save  it back in tblProduct in colour field.
Of course I removed field colour from tblProduct and keep only ColourID .
Thanks all for help and one time more, sorry for not being clear.
You obviously came to Access from another development environment but so did I.  You have all the control you need with bound forms once you understand how events work.  When you use a tool, it is always best to use that tool as it was intended to be used.  You can pound a nail into the wall with a screwdriver but a hammer is so much better suited to that task.
Good Luck
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.

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.