[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access Combo box deleting original data

Posted on 2009-04-11
7
Medium Priority
?
1,951 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 85
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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