Solved

Access Combo box deleting original data

Posted on 2009-04-11
7
1,688 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms access 2010 vba, copy table from db1 to db2 from db3 27 46
Normalization of a table 19 74
Display label on subreport when NO DATA on subreport 4 22
Access on Mouse move 5 34
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

773 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