Change field to combo box w/out data loss (1 Viewer)

[davea300]

Hi

Using access 2003 is it possible to change the type of a field from text to combo/picklist without data loss? Whenever i use the Lookup wizard it warns me that all the data will be lost when I try to change it.

I have a field in which the user is typing the same 4 values over again so it makes sense to change the field to a picklist. There are 400 records so I'd rather avoid having to go through each record and re enter the data.

 

[EssexRich]

Hi there.
Add a new table with two fields. The first field will be an ID Autonumber, Primary Key field. The second field will then contain the item you want in your combo box.

For example: CountyID and CountyName or whatever you want.

Next up, Add a field to your original table called CountyID (or somethign appropriate to your database) make it a long integer number field.

Add a one-to-many relationship from the new table to the new field in your source table.

Next, add a record in the new table for each of the four values you want in the list. Make sure these match exactly with what's been entered into the field in the source table as we're going to run a query to update the 400 records.

Create an Update Query using the two tables.

You want to update the main table's countyID (or whatever it's called) to equal the ID in your new table where your main table's text field equals CountyName (or whatever its called).

Once this is run, you then need to look at the original table to see if any records have a blank ID field, if they have, then this is because someone originally typed the value in wrong. Manually fix this by looking up the correct ID number and typing it in the field.

Once youve done this and checked it, you should be able to delete the original field. and do a compact on the database.

Hope this helps!

Rich

 

[Mr. B]

If the values you need for populating the field will never be more than the four values, you can change the text box to a combo box and then change the Row Source Type property to Value List. Then in the Row Source property, just type in the four values you want, separating each value with a semi colon. Also, you should set the "Limit To List" property to Yes.

That's it. You would not have to change anything about your table, just the control on your from and some properties of the combo box control.

Using this method, the values stored in the field would remain text type data but the user would only be able to select from the the four options you provided.

 

[davea300]

Thanks for the replies.

I adjusted the form field so that it is now a combo and left the table as it is. Works perfectly with no data loss!

 

[EssexRich]

Cool. Good work!

 

[melissa_may1]

Hi!

Good that you've got this one already figured out!

I've got another method to add to the great responses you already received, for anybody else that may have a similar situation.

Whenever I'm faced with something like this, I make a quick copy of the database, then use the copy to try out my ideas.

If they work, great!

If not, I can go back to the original, make another copy, and try again.

Yes, I do end up making a lot of copies as I develop, but I've never lost anything by doing it this way, and I've learned a lot!

Good luck!