Access Combo box deleting original data

tty5
tty5 used Ask the Experts™
on
I have imported an address list from Excel into a Access 2007 table.  When I add a combo box to the State field that links to a State Table for future data entry Access deletes the original entries.  Not good.

How do I prevent the new Combo box definition from deleting the existing information?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Assuming you're referring to a typical bound combo, then that's what it does - it replaces the data in the table with the data chosen by the user.

Or perhaps you're referring to something else.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
tty5,

This question, in various forms, shows up every year or so here.

Typically LSM's post is the explanation.

The source of the confusion is the way the combobox works.

A combobox can Display existing values in a table, and it can also "Insert" values into a table.

A combobox has a "Controls Source" that controls what it displays and where selected data is stored.

It can also have a "RowSource" which is an external list of values that can be selected and stored in the ControlSource.
Confused yet?
;-)

Don't worry it gets worse.
LOL!.

So in the most basic sense, if you use a combobox to display and select value, it will "Change" the value,
not technically "Delete" it.

For example if you use a combobox to change the Country from Spain to Germany, the value "Spain" is not "Deleted" it is simply changed/replaces. (Because theoretically, you should be able to re-select "Spain")
Make sense?

Now, ...with all of this in mind,  ...what is your specific issue with the combobox?

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Sorry, I clicked "Submit" to fast.

A combobox will not, on its own, change an existing value.
So you will have to explain to us exactly how your combobox is configured.
And post any associated code.

JeffCoachman
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Sorry for the delay I had stepped away from the project last week.

The data in the table was a contact listing originally created in Excel and imported into a table in Access (Owners).  So in the imported records the State field already contains the correct information for that record.

I created a separate States table to use with a combo box I created in the Owners table using the Lookup Wizard.  After I create the combo box in the existing field and go to save the table I get this message:

Some data may be lost.  The setting for the FieldSize Property of one or more fields has been changed to a shorter size.  If data is lost, validation rules may be violated as a result.  Do you want to continue anyway?  I choose Yes.

Then I get ...

Access encountered errors while converting the data.  The contents of fields in 131 records were deleted.  Do you want to proceed anyway?

Here's where the problem lies.  If I say yes then the original data in the State field is wiped out and I have to "re-enter"  using the combo box.  The combo box is something I would like to use for future record entry, but I am hoping there is a workaround for creating a CB within a table with existing data.

Jeff, I see the Row Source that was generated by the Lookup Wizard, and yes the Combo Box functions properly.  I am unable to locate the Control Source unless you are perhaps referring to the Display control?

Here is my Row Source:
SELECT [States].[ID], [States].[StateAbbreviation] FROM [States] ORDER BY [StateAbbreviation];

I have not changed any of the default setting in the Combo Box Propeties.

Thanks!
MIS Liason
Most Valuable Expert 2012
Commented:
tty5,

OK, here is the deal.

I will presume that in Excel, you did not have a "StateID" field, just the StateAbbreviation

So when you moved the data to Access and created your States table, the Primary Key in the States table was StateID (numerical), while the value in the Owners table was still Textual (StateAbbreviation)
So, when you used the combobox to select the state (again depending on what the default setting were), you may have been tryng to insert  Text Values into Numeric Fields or Vice versa.
Or even if you inadvertently spelled one States with more characters than the corresponding entry in the other table, you could have generated errors

So,...
When you brought the data over from Excel you should have:
1. In the Owners table, set the StateAbbreviation Field size property to a specific size.
(Access will make text fields from Excel the Max size of 255 characters.)

2. Created your States Table from this existing table using a query like this:
SELECT DISTINCT tblOwners.StateAbbreviation INTO tblStates
FROM tblOwners;

3. Then added in an AutoNumber "StateID" field to the States Table. (try to avoid naming your Key Fields simply "ID")

4. Then run a query like this to change the Text StateAbbreviation (in the Owners Table) to its Numerical Equivalent:
UPDATE tblOwners
INNER JOIN tblStates
ON tblOwners.StateAbbreviation = tblStates.StateAbbreviation
SET tblOwners.StateAbbreviation = [StateID];

5. NOT created your Lookup Field in the table.
(see here: http://www.mvps.org/access/lookupfields.htm)
Instead, create your lookup combobox in the FORM.

So hopefully you have a back of everything, and can start again.
;-)


So, to answer your question directly, you must make sure the values that you combobox references, matches the values stored in the table.

Finally, whenever you see an alert that reads:
  "Access encountered errors while converting the data.  The contents of fields in 131 records were deleted.  Do you want to proceed anyway?"
...Click No or Cancel.


JeffCoachman

Author

Commented:
Awesome.  I knew there had to be a work around!  Thanks.  
FYI , just a little quirk as a new user... I repeated the steps that you listed to create a new table query and just could not figure out where I was messing up when it did not work!  Finally I made the connection that the "Enable this content" had not been chosen.  Once I change this setting your instructions worked like a charm.  
I also have a good understanding why NOT to create a lookup in a table after reading your link.  Why oh why then does it seem that all basic learn how to resources have a user create it using the lookup wizard when you create a table design?!  I now know better design thanks to this.

Also, yes, I had a backup ;-)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<Why oh why then does it seem that all basic learn how to resources have a user create it using the lookup wizard when you create a table design?!>

LOL!

In a nutshell, ...because it makes it easy for beginners.
Because tables are the building blocks of a database, it is easy to show beginners how to do it there.

This is because many beginners will forget to put this fuctionality in the Form.

The key here is that Lookup Fields, in and of themselves, are not "Evil" as the link states.
Only that they are inconvieient in "Tables", and should only be used in Forms.

To add one more point:
If you use Lookups in the table you cannot copy and paste Text OR Numerical date to the field.

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial