Limit To List and Copy/Paste

[kdodge]

Hi,

I have a form that my trainers use to enter their students. There are four fields: First Name, Last Name, Title and Branch.

We get a list of students from a supplier in Excel format and the trainers copy and paste from that list into a datasheet form. Unfortunately, when they paste a great chunk of info like that - the "Limit To List" setting on the Title and branch fields is bypassed.

I don't want to deprive my trainers of the ability to copy/paste - sometimes they have huge classes to enter. But how can I maintain the quility of my data if the limit to list setting isn't preventing errors?

 

[Guus2005]

If the limit-to-list event isn't triggered due to the large amount of data you paste, you could use a temporary table to where you paste the data. After that, you could press a button e.g. "Verify Data" to store the newly pasted data into the actual table.

If the input is always the same you could read the excel spreadsheet from Access!

HTH

 

[pl456]

Could you not get the people who send you the data to use a spreadsheet template that you have created. You could then use validation to regulate what they can enter.

 

[missinglinq]

I have to ask this; are you sure the Limit To List has been set to Yes in the Properties sheet? I ask because pasting in data not in the list does normally trigger the error, when you move off of the field.

Linq

 

[kdodge]

pl456 - that's what we used to do, however that's no longer possible.

Ling - if you paste one item in, then tab or enter away, yes, you get the error. But when you paste rows at a time, you don't have that "tab" or "enter" moment that does the check.

Anyway - I have a sort of solution - my form was connected right to its table. I've written a query that the form connects to that tries to display the region for each branch. When someone attempts to paste a branch that doesn't have a region (because it isn't a branch), the query throws an error, and drops the offending record into the Paste Errors table.