Changing a text box to a combo box

[ScottishFencer]

Hi there,

I have a database application (Access 2003) where, on a form, there is a text box that is used to enter the name of an "assessor". I've received a request to change this to a dropdown. This form has been used for some time now and the database is already populated with a lot of records. How do I go about replacing the textbox with the combobox? At present the users enter the assessors name by hand, but they've grown tired of that. This appears to be a decision taken by a previous incumbent of this office (who can say why). In any case: I would also like the combo box to display the value of the assessor field as it currently is.

 

[Remou]

Would it be correct to say that you have a table populated with lots of assessors names and that you now wish to list these names in a combo box?
You could add a combo box to the form, set the Row Source to something like:
[tt]SELECT DISTINCT tblTable.AssessorNameField
FROM tblTable;[/tt]
and the Control Source to AssessorNameField, which would be quick and easy (Access 2000 allows Change To on the Format Menu). Alternatively, you could create a table from the existing Assessor names, which would allow you to tidy the names, and set the Row Source to this table. A little code for Not in List would be needed, if you use a table. Is this the kind of thing you mean?

 

[ScottishFencer]

Well I know how to insert the combo box - that's not a problem - the real issue is how to set it so that, by default, if there is an entry already in the table then the combo box displays that. I want to avoid any data loss or users having to re enter data. I'm not sure if it's just the new year. I have this nagging feeling that this is pretty easy and I am just being thick.

 

[Remou]

I know the feeling, trying to kick the brain (what?) back into gear.
The first idea above is certainly quite simple and when I have used it, it has not led to data-loss, but back-ups never hurt. The second idea is more 'normal'; it should not cause any data-loss problems, either.