UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Field Validation On Paste, Access 2016    
 
   
fionny
post Dec 18 2017, 07:22 AM
Post#1



Posts: 22
Joined: 31-March 13



Hi Folks,

Im putting together a small database and need some validation in the fields, to this end I have setup Combo boxes on the neccessary fields sourced from other tables, I have turned on Limit to List and Allow Value Lists Edit to "No".

This validation works fine if typing lines into the table however if someone pastes from Excel the validation is entirely ignored? Is this a bug in Access or is there something im missing?

I know I can write an additional validation rule into each field but I thought by taking the approach I did I was avoiding the need!

Any advice?

Thanks,
Go to the top of the page
 
GroverParkGeorge
post Dec 18 2017, 07:53 AM
Post#2


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


The problem here is the use of those despicable unfortunate Lookup fields. They are a problem in many ways, and confusing newer Access users is just one of them.

You need to enforce Referential Integrity, using the appropriate Relationships Window instead. Remove the Lookup fields from the tables. You'll need to adopt a different method of importing data from Excel because it is NOT set up the same way a relational database needs to be designed.

Attached File  ReferentialIntegrity.jpg ( 70.89K )Number of downloads: 1

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 18 2017, 07:56 AM
Post#3


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


You might also benefit from investing time in learning more about Relational Databases, using the process of Normalization to design and create appropriate relational tables.

--------------------
Go to the top of the page
 
ScottGem
post Dec 18 2017, 08:37 AM
Post#4


UtterAccess VIP / UA Clown
Posts: 32,199
Joined: 21-January 04
From: LI, NY


QUOTE
however if someone pastes from Excel the validation is entirely ignored?


Users should never have direct access to tables. That can be very dangerous. As George noted, lookups should be done on forms, not in tables. And data setup in Excel is often not in a proper format for pasting into relational tables. Better to link to the Excel data and use Append queries to properly add them to your tables.

--------------------
Scott <>
Scottgem's Blog
Microsoft Access MVP since 2007
Author: Microsoft Office Access 2007 VBA
Go to the top of the page
 
fionny
post Dec 18 2017, 08:43 AM
Post#5



Posts: 22
Joined: 31-March 13



Hey

Thanks a million for the reply, I think however this isnt suitable for what im attempting to do?

The purpose of this database is to literally track orders, along with the relevant data. However in order to ensure that users maintain some semblance of structure I want to force the fields to ONLY accept particular data... so an email:

Tbl-1

Username - Possible Entries (Fionn, George, Bob)
Status - Possible Entries (Confirmed, Cancelled, Pending)
Region - Possible Entries (EMEA, APJ, Americas)
Provider - Possible Entries 1 of 308 options.


So the way I set that up was a single Master Tbl with the above fields and under Username field say I have picked Display Control of Combo Box, Row Source = Table/Query and then the relevant source.

I dont need it to reference back and forth I simply want to prevent a user from entering anything thats not in the table of allowed users.

The paste or upload function from Excel (or CSV or anything else) Ignore this requirement and basically allows any data in.

Go to the top of the page
 
Jeff B.
post Dec 18 2017, 08:53 AM
Post#6


UtterAccess VIP
Posts: 9,958
Joined: 30-April 10
From: Pacific NorthWet


For what its worth, you (and Access) are not limited by what users want to see. You can develop a well-normalized, relational database structure in Access, import Excel data as-is, then use append queries to "load" the Access tables. Moreover, you will design forms and reports to reflect what users expect, without needing to design Access tables to reflect what they want to see.

... and I agree with others here, letting users muck about in your tables is a formula for disaster. I hope you're getting paid by the hour ... pullhair.gif

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Dec 18 2017, 09:26 AM
Post#7


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Actually, no. Importing raw data from a CSV or Excel worksheet is ONLY the first step. You are developing a relational database. That means YOU need to import the raw data into a temporary table and from that temp table, move the normalized data into the properly designed relational tables.

If you want users to maintain an "Excel-like experience", the better option would be, I think, to keep the entire experience in Excel.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 07:57 PM