• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2023
  • Last Modified:

Access Combo box deleting original data

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
tty5
Asked:
tty5
  • 4
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tty5Author 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!
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
tty5Author 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 ;-)
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now