Solved

Access Combo box deleting original data

Posted on 2009-04-11
7
1,611 Views
Last Modified: 2013-11-29
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?
0
Comment
Question by:tty5
  • 4
  • 2
7 Comments
 
LVL 84
ID: 24125722
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24132009
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24132046
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tty5
ID: 24175710
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!
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24177115
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
0
 

Author Closing Comment

by:tty5
ID: 31573043
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 ;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24200304
<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
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now