Converting a Text Box to a Combo Box

[lockjaw4829]

I'm using Access 2003 and on an existing form I'm trying to convet a text box to a combo box.

I used the "Convert To" function by right clicking on the text box and selecting combo box. The control changed and looks fine.

I left the Control Source as it was and created a SQL in the Row Source with only one field. The SQL works fine and displays the list of values I want the combo box to display.

The bound column is 1, pointing to the only field in the SQL. The number of columns property is set to 1, as well. The Column Width property is set to the same with as the combo box.

Now for the part that's making be crazy. When I put the form into Form View the value from the Table displays, but if I open the combo box (dropdown) there is nothing in the list (the list area drops down, but is totally blank). I tried this with adding a new record with the same result.

I've created hundreds of combo boxes and never had this happen. I've compared the settings with working combo boxes on the same form and can find no relevant differences.

I am totally stumped. I know this has to be something stupid I'm overlooking, but I've got a total brain freeze about this problem.

If anyone can give me a suggestion of something I might have overlooked or something else to try (I've already tried creating a totally new combo box and got the exact same result (and this was even using the combo box wizard)).

TIA to anyone who responds with any helpful suggestions.

George

 

[Uncle Gizmo]

Hi George, I can't really see that you've done anything wrong, however I thought if you had a look through this set of videos quickly you might spot something, if not then maybe post a cut down version of your database so that someone else can have a look.

Videos here: MS access hints and tips

 

[lockjaw4829]

Thank you for at least letting me know that I do know what I'm doing (most of the time, that is).

Yep, did the steps just like in the videos, but my result was as described in my original post.

Trying to post the form, queries and tables even in a cut down form would be extremely time consuming as this is a very busy form which includes a subform as well.

The thing that bugs me the most is that every thing else on the form works perfectly. This combo box thing was a request thrown at me at the last minute and I, foolishly, said "No, problem." Guess it serves me right. Murphy's law and all that.

I do wish to thank you for your quick reply and if I do discover the source of the problem, I'll make sure to post it.

George

 

[RainLover]

Try changing the sort order to decending.

Just a crazy thought.

 

[Uncle Gizmo]

>>>The bound column is 1<<<

Try changing this 1 to 0 (Zero)

I would have responded earlier, I have been reading the posts on my phone using windows mobile, however it wouldn't let me create a reply? Somehow the forum does not marry in with a PDA.

 

[stopher]

I would have responded earlier, I have been reading the posts on my phone using windows mobile, however it wouldn't let me create a reply? Somehow the forum does not marry in with a PDA.

Which browser are you using? I'm replying here using Opera Mini
Chris
(sorry for off-topic reply)

 

[stopher]

Trying to post the form, queries and tables even in a cut down form would be extremely time consuming as this is a very busy form which includes a subform as well.

Even when compressed and zipped? How about some screen shots?
- the SQL
- the properties of the drop down.

Chris

 

[Uncle Gizmo]

Which browser are you using? I'm replying here using Opera Mini
Chris
(sorry for off-topic reply)

Hi Chris,
Thank you for your response, you are right it's getting off topic so I have moved it to a new thread here:

 

[lockjaw4829]

Try changing the sort order to decending.

Just a crazy thought.

Crazy or not I tried your suggestion. The SQL does indeed reorder itself, yet the combo box on the form remains as before.

Thanks for your suggestion, though.

 

[lockjaw4829]

>>>The bound column is 1<<<

Try changing this 1 to 0 (Zero)

I would have responded earlier, I have been reading the posts on my phone using windows mobile, however it wouldn't let me create a reply? Somehow the forum does not marry in with a PDA.

Tried this, although I was under the impression that setting the bound column to zero caused the ListIndex to be utilized for the data value of the combo box.

Didn't work and as a kicker the value for existing records does not display at all in the combo box.

Thanks for the thought, I had high hopes since this sounded like something I might have overlooked, but it did not solve the problem.

If you have any further thoughts, I'd appreciate them.

I will try to post the settings for the combo box as one person asked. Maybe this will help.

 

[gemma-the-husky]

its not something silly like binding a numeric value to a combo box with text values is it?

what values DO you have in the combo box, and what is the number of columns/column width set to?

if all else fails, delete the control , and recreate it with the combo box wizard

 

[lockjaw4829]

Ok. first here are the combo box properties from the Data Tab:

Control Source: fldCMCaseType
Input Mask: >&&&&CCCCCCCCCCCCCCCC;1;" "
Row Source Type: Table/Query
Row Source: SELECT qryCaseTypes.fldCaseType FROM qryCaseTypes ORDER BY qryCaseTypes.fldCaseType;

(Which produces the following when run in the query builder)

Query1Case TypeFA-16FA-20FA-27FA-29FA-31SNCA-IND-16SNCA-IND-20SNCA-IND-41SNCA-IND-64SNCA-IND-7SNNC-FAM-16SNNC-FAM-20SNNC-FAM-29SNNC-IND-16SNNC-IND-17SNNC-IND-20SNNC-IND-46SNNC-IND-63SNNC-IND-7


Bound Column: 1
Limit to List: No
Auto Expand: Yes
Default Value:
Validation Rule:
Validation Text:
Enabled: Yes
Locked: No
Smart Tags:

The relevant info from the Format Tab:
Column Count: 1
Column Heads: No
Column Widths: 2.125"
Visible: Yes
Display When: Always
Width: 2.125"

From the Other Tab:
Name: cboCaseTypes

I have attached a screen shot of the form showing the non-working combo box "Case Type."

If you would like any further detail, I'll try to supply them.

 

[lockjaw4829]

its not something silly like binding a numeric value to a combo box with text values is it?

what values DO you have in the combo box, and what is the number of columns/column width set to?

if all else fails, delete the control , and recreate it with the combo box wizard

Gemma,

All of the values are Text (the control field, the SQL field backing the combo box and the combo box itself).

I have posted the relevant data in a recent post including a screen shot showing the offending control.

I have tried totally removing the control and recreating it with the Combo Box Wizard and unfortunately am getting the same result.

BTW, all the tables are Linked Tables. This shouldn't really matter, since the other combo boxes on the form use Linked Tables also and are working just fine, but I thought I mention it just in case.

Please check out the data I've posted and see if you find anything screwy. Once you've revewed what I posted if you have any questions, comments and/or suggestions, I'd love to hear them.

 

[lockjaw4829]

Even when compressed and zipped? How about some screen shots?
- the SQL
- the properties of the drop down.

Chris

Chris -

I've posted what I believe to be the relevant data and a screen shot of the form showing the dropped down empty combo box in a seperate post to this thread.

If you'd like anything else, please ask and I'll try to provide it.

Any suggestions would be greatly appreciated.

George

 

[DevastatioN]

Hello,

Why is there an input mask if it's a combobox lookup?

Also, since you've deleted the control and tried to re-add a new combobox using the wizard, try a few of the following:

1. Create a combobox with "I will type in what I want" and link it to the correct control, and tell me if that is working.

2. Try a lookup using a different field and or table, and see if that is working.

3. Try a blank brand new form with the identical combobox you're trying to make, and see if that is working (Tells us if something else in that form is messing with it somehow).

This might bring you closer to finding out what is exactly wrong, I've looked at your settings and can't seem to find anything weird except that input mask, which shouldn't cause the problem anyway.

 

[DevastatioN]

Try taking off the all caps in the format in the table input mask etc. (The >)

Someone else just had the exact same problem, and it appears to be caused by the uppercase formatting of the field in the table. Although I haven't been able to duplicate the error on my version of Access (Access 2003).

 

[lockjaw4829]

Hello,

Why is there an input mask if it's a combobox lookup?

Also, since you've deleted the control and tried to re-add a new combobox using the wizard, try a few of the following:

1. Create a combobox with "I will type in what I want" and link it to the correct control, and tell me if that is working.

2. Try a lookup using a different field and or table, and see if that is working.

3. Try a blank brand new form with the identical combobox you're trying to make, and see if that is working (Tells us if something else in that form is messing with it somehow).

This might bring you closer to finding out what is exactly wrong, I've looked at your settings and can't seem to find anything weird except that input mask, which shouldn't cause the problem anyway.

Ok DevastatioN,

Here goes.

First remember this was origninally a simple text box and the input mask was just a carryove (I do want to retain if in case a user wants to add a new Case Type to the list, but that is for after I get the basic lookup function of the combo box working). Anyway, the input mask shouldn't be causing this problem. As an experiment I removed it and nothing changed.

Next, I create a combobox with "I will type in what I want" and linked it to the correct control, and the value typed in is correctly stored in the proper field in the underlying table.

A combo box using a different field with a different backing table works correctly.

Lastly, I created a brand new form using the original query that drives the Basic Client Data form and created two combo boxes. One to a field I was not having problems with and a second for the Case Types which are giving me trouble. The first combo box worked perfectly while the Case Types one exhibited the exact same behavior as the similar one on the original form did.

Perhaps this will clarify things for you. I am still stumped, except for the realization that the problem probably has something to do with something hinky with either the table or the SQL I'm using to drive the lookup values in the Case Types combo box.

If any of this lights the "Eureka" lamp for you, please let me know as I'm about to junk the table and start over by writing a table maintenance form to recreate the table and add a combo box lookup just to see if that might straighten things out.

BTW, thanks for the suggestions.

George

 

[DevastatioN]

Here is a link to the other thread in which the guy was having the exact same problem as you:

http://www.access-programmers.co.uk/forums/showthread.php?t=163451

Apparently having formatting (You removed the input mask, but what about "Formatting" in the table itself from that field?) can have an effect if you are using SP3 and not using the SP3 hotfix.

If you have SP3 installed, try installing the SP3 hotfix and maybe this will solve all your problems.

(The things you did definitely narrowed it down to a problem with that field in the table itself, and nothing outside of the table in my opinion).

 

[RainLover]

As a golden rule.

Do not use formatting or anything similar in your tables.

Just my opinion.

 

[gemma-the-husky]

1) do the other drop downs show data to pick from?

2) remove the input mask, and show the column head, just so its clear what is going on,