Copying a record overrides combo box row source and limit to list

[kpieksma]

I have a table that has a text field with a look up function that looks up and limits entry from a specific list in another table. It works fine if the user is entering data in the specific field through normal data entry process. However, If I try and copy in a record, the combo box or list box allows the entry even if it is not in the list (limit to list properties are on) Not understanding why Access allows that record to be entered without questioning the entry in the field that does not match the list...Consequently, a record is accepted into the table that may have incorrect data.



Again, works great for data entry, does not work at all when copying a record to the table..Could use a little help...

[JeffChr]

How are you copying-in in a record ?

[kpieksma]

From Excel..

[Bob Fitz]

Not sure that answers the question of "how", just tells us where the data is.
Are you using code?
Are you using Copy/Paste?

[June7]

Copying in a record has nothing to do with selecting item from combobox list and since you aren't selecting item from list, the combobox RowSource is irrelevant.

If you want to prevent duplicate values in table, that is entirely unrelated to the configuration of combobox RowSource.

[kpieksma]

Not sure that answers the question of "how", just tells us where the data is.
Are you using code?
Are you using Copy/Paste?

Simply copy and paste..

[June7]

Comments in post 5 still apply.

This is why users should not work directly with tables and queries, only forms and reports. If allowed to work directly with tables and queries and do what they want, be aware of the consequences (bad data).

[kpieksma]

I am trying to have users use the correct naming convention for a text list... The field in question is a text field that can be entered several different ways because of the use of hyphens and spaces...But only one is correct. For example, the correct spelling of a particular item is SHY084-5003. I have had users enter as SHY 084-5003, and also SHY-084-5003. Only the first is correct, and if not entered correctly, then correct reporting later on is not possible because Access is looking at and linking on the name. So I made a combo box or a field list that only allowed users to enter the correct name based on a master list that the look up refers to...The problem is, there are thousands of records added each year, and so entering one record at a time is unpractical. So I want to use a mass upload copy and paste from Excel, where the data is collected, but the combo box/list box does not allow mass uploads and limit the name from a "Master List". It allows the paste to have a name not in the list. Hope this explains better....I cannot use a data entry template, as that would be the next logical suggestion, because the thousands of entries do not follow the same naming convention. Each somewhat stands on its own.

[June7]

Between a rock and a hard place ...

Validating the data before import and commitment to table would probably be complicated and involve VBA code. This validation could allow good records to commit and reveal the bad records which will require case-by-case review to fix. However, little can be done to prevent bad data commitment by users doing copy/paste. This is a flaw in the data management process.