If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup Field Issues
I have a lookup field in a table that is "text" data type and
restricted to values in the list. It works properly when entering data, meaning it won't let the enterer remove the focus from the field if the text they've entered isn't in the list. But for some reason it will allow text values to be pasted into the field even if they aren't in the list. I want to use the field to check for consistency in preexisting data using copy and paste. In my experience this should create a "Paste Errors" table with all the records having a value for this field isn't in the lookup list. Any ideas why I'm not getting paste errors in this case? |
#2
|
|||
|
|||
Lookup Field Issues
Are you saying that your table has a field that is of type "lookup"? If so,
check through this newsgroup to get a sense of why folks here hold "lookup fields" (in tables) in low regard! Second point ... if you/your users are working directly in the tables, stop now! Access is a relational database. Access tables store data. Access FORMS display data. Even though they look like spreadsheets, Access tables ARE NOT SPREADSHEETS! Finally, if you use a form and combobox to do that lookup, you can set the LimitToList property to Yes, ensuring that only valid values are entered. Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "esn" wrote in message ... I have a lookup field in a table that is "text" data type and restricted to values in the list. It works properly when entering data, meaning it won't let the enterer remove the focus from the field if the text they've entered isn't in the list. But for some reason it will allow text values to be pasted into the field even if they aren't in the list. I want to use the field to check for consistency in preexisting data using copy and paste. In my experience this should create a "Paste Errors" table with all the records having a value for this field isn't in the lookup list. Any ideas why I'm not getting paste errors in this case? |
#3
|
|||
|
|||
Lookup Field Issues
On Wed, 13 Jan 2010 11:12:26 -0800 (PST), esn wrote:
I have a lookup field in a table that is "text" data type and restricted to values in the list. If it's a lookup field then it just *appears* to contain text. It doesn't. It contains a concealed numeric ID value. That simple fact is concealed from your view by the infuriating, misdesigned, misleading, and inefficient so-called "Lookup Wizard" misfeature. (Do you get the impression I don't like lookup fields? You're right). It's not just me: see http://www.mvps.org/access/lookupfields.htm for a critique. It works properly when entering data, meaning it won't let the enterer remove the focus from the field if the text they've entered isn't in the list. But for some reason it will allow text values to be pasted into the field even if they aren't in the list. Yep. One of the many, many limitations of using lookup fields in tables. I want to use the field to check for consistency in preexisting data using copy and paste. In my experience this should create a "Paste Errors" table with all the records having a value for this field isn't in the lookup list. Any ideas why I'm not getting paste errors in this case? Copy and paste should not be needed. Use a Form (not your table datasheet!!) for data entry, and leave the default Autocomplete set on the combo box on the form. The user would need to type only the first few characters of the text value; Access will jump right to the desired record and insert the numeric value corresponding to it. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Lookup Field Issues
I have to say those are pretty condescending responses. I understand
the value of forms for data entry and that really doesn't address my issue. Since I've been involved with this project all data entry has utilized forms in Access, but there is a mountain of preexisting data to deal with. The data is already entered and was imported from various sources - some of it was entered in excel and some of it was in a preexisting database that was quite a mess. Needless to say, this data is rife with errors thanks to my predecessors NOT grasping the value of forms. So I can A: import the data using an import function, which will exclude all records that don't fit my rules and leave no simple way of knowing which records were excluded, B: use an append query that will do exactly the same thing, or C: copy and paste the records so that I get a paste errors table, which I can then correct as needed. As for the primer on lookup fields, the info you've provided (If it's a lookup field then it just *appears* to contain text. It doesn't.)is incorrect. If the primary key of the row source is a text field, the data is stored as text. This is beneficial in my case because queries written on the data table can use the actual text as criteria without requiring a join to the lookup table. Since I'm building this database for multiple users with various levels of Access expertise this would be preferable to training them all in the finer points of multiple table queries (or doing all their work for them). And as far as critiques of lookup fields in general, if anyone has a better suggestion for how to limit values in a field to a list of 11,000 allowable values let me know. Beats typing "value1" or "value2" or "value3" ... 11,000 times in a validation rule. I tend to agree more with the response posted he http://improvingsoftware.com/2009/10...cess-are-evil/ than with most of the arguements against lookup fields in the link you've provided. It seems to me I've found a glitch in Access that there is no way to correct. If the primary key referenced in a lookup field has a text data type, the database will not check values against the lookup table when records are pasted (even if limit to list is set to yes). I tried everything I could think of to no avail, and eventually had to return the primary key to the ID field to get the behavior I'm looking for. Which means I get to do everyone's work for them. |
#5
|
|||
|
|||
Lookup Field Issues
Esn,
Using look-up fields has the problem of not being to Limit to List but if you append all the unique values to a table and add that table as the RowSource of your field the Limit to List will work on a form. I do not know if this technique will work in a table because I do not use look-up fields in a table. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "esn" wrote in message ... I have to say those are pretty condescending responses. I understand the value of forms for data entry and that really doesn't address my issue. Since I've been involved with this project all data entry has utilized forms in Access, but there is a mountain of preexisting data to deal with. The data is already entered and was imported from various sources - some of it was entered in excel and some of it was in a preexisting database that was quite a mess. Needless to say, this data is rife with errors thanks to my predecessors NOT grasping the value of forms. So I can A: import the data using an import function, which will exclude all records that don't fit my rules and leave no simple way of knowing which records were excluded, B: use an append query that will do exactly the same thing, or C: copy and paste the records so that I get a paste errors table, which I can then correct as needed. As for the primer on lookup fields, the info you've provided (If it's a lookup field then it just *appears* to contain text. It doesn't.)is incorrect. If the primary key of the row source is a text field, the data is stored as text. This is beneficial in my case because queries written on the data table can use the actual text as criteria without requiring a join to the lookup table. Since I'm building this database for multiple users with various levels of Access expertise this would be preferable to training them all in the finer points of multiple table queries (or doing all their work for them). And as far as critiques of lookup fields in general, if anyone has a better suggestion for how to limit values in a field to a list of 11,000 allowable values let me know. Beats typing "value1" or "value2" or "value3" ... 11,000 times in a validation rule. I tend to agree more with the response posted he http://improvingsoftware.com/2009/10...cess-are-evil/ than with most of the arguements against lookup fields in the link you've provided. It seems to me I've found a glitch in Access that there is no way to correct. If the primary key referenced in a lookup field has a text data type, the database will not check values against the lookup table when records are pasted (even if limit to list is set to yes). I tried everything I could think of to no avail, and eventually had to return the primary key to the ID field to get the behavior I'm looking for. Which means I get to do everyone's work for them. |
#6
|
|||
|
|||
Lookup Field Issues
If you want to talk about condescending, that blog post beats anything I've
seen in a while. The person writing it admits he doesn't use Access much, then dismisses every objection to lookup fields as irrelevant without investigating any of the claims, or by glossing over what he seems to acknowledge are real objections, or by acknowledging he does not understand the objection but that it is irrelevant anyhow. I use Limit To List to restrict allowable values in a combo box on a form. No validation rule is needed. I regard tables as places to store data, not as substitute forms. If I need a combo box, I build it on the form, not in the table. Preexisting data can be a problem, but sometimes there's no solution but to import it all and fix it, or fix it before importing if that makes more sense. It seems I approach database design differently than you do. I typically build databases in support of the jobs other people need to do. I don't need to know all the details of their jobs to build a database, and they should not need to know much about databases to do their jobs. If I am asking low- level users to build queries or otherwise get under the hood then I am not doing my job as developer. A good portion of my development time is spent making the interface as intuitive as is possible. But that's me, and may not be the approach others would take. esn wrote: I have to say those are pretty condescending responses. I understand the value of forms for data entry and that really doesn't address my issue. Since I've been involved with this project all data entry has utilized forms in Access, but there is a mountain of preexisting data to deal with. The data is already entered and was imported from various sources - some of it was entered in excel and some of it was in a preexisting database that was quite a mess. Needless to say, this data is rife with errors thanks to my predecessors NOT grasping the value of forms. So I can A: import the data using an import function, which will exclude all records that don't fit my rules and leave no simple way of knowing which records were excluded, B: use an append query that will do exactly the same thing, or C: copy and paste the records so that I get a paste errors table, which I can then correct as needed. As for the primer on lookup fields, the info you've provided (If it's a lookup field then it just *appears* to contain text. It doesn't.)is incorrect. If the primary key of the row source is a text field, the data is stored as text. This is beneficial in my case because queries written on the data table can use the actual text as criteria without requiring a join to the lookup table. Since I'm building this database for multiple users with various levels of Access expertise this would be preferable to training them all in the finer points of multiple table queries (or doing all their work for them). And as far as critiques of lookup fields in general, if anyone has a better suggestion for how to limit values in a field to a list of 11,000 allowable values let me know. Beats typing "value1" or "value2" or "value3" ... 11,000 times in a validation rule. I tend to agree more with the response posted he http://improvingsoftware.com/2009/10...cess-are-evil/ than with most of the arguements against lookup fields in the link you've provided. It seems to me I've found a glitch in Access that there is no way to correct. If the primary key referenced in a lookup field has a text data type, the database will not check values against the lookup table when records are pasted (even if limit to list is set to yes). I tried everything I could think of to no avail, and eventually had to return the primary key to the ID field to get the behavior I'm looking for. Which means I get to do everyone's work for them. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201001/1 |
#7
|
|||
|
|||
Lookup Field Issues
"esn" wrote in message
... I have to say those are pretty condescending responses. So your debut thread contains an insult to the folks trying to help you? Responders can only guess your level of experience from your OP and yours sounds like that of a clueless newbie, so you got appropriate responses IMO, but I doubt that insults will get you much more in the way of help. You seem hell-bent on doing things wrong, like using lookup fields and allowing end-users to build their own queries which can only add to the reasons to let you get on with it. |
#8
|
|||
|
|||
Lookup Field Issues
"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:a2199bb83543f@uwe... A good portion of my development time is spent making the interface as intuitive as is possible. But that's me, and may not be the approach others would take. It certainly *is* the approach that I take and IMO is the *only* approach. |
#9
|
|||
|
|||
Lookup Field Issues
I'm not trying to insult anyone who's trying to help me, but I don't
feel like anyone but Gina is actually making any effort to help. Any post that mentions "data entry" or "forms" is ignoring my situation. No one here has actually suggested an alternative to my methods that would work given my circumstances, unless they are really implying that I should re-renter 3 years worth of data from 3 different projects using forms. Maybe I'm confused on the use of forms for importing preexisting data and that's my issue. Would it even be possible to use forms to do what I'm trying to do? Meaning import data from various files in various formats? As for commenting on my "end-users" etc., I know them (and my situation) a lot better than you do. It's not possible for them to do their jobs without writing queries and it wouldn't be possible for me to write every possible query they might need for them. I'm working in a completely different field than most of the people who use Access, and juggling several different databases from several different projects. Making them as intuitive as possible is indeed high on my list of priorities, which is why I'm using a lookup field in the first place. The databases I've designed for my own use don't have a single lookup field, but my boss thinks they're the bees knees and doesn't want to stare at a bunch of foreign keys that don't mean anything to her. You can take it up with her if you think that makes her an idiot. For now I need a way to import preexisting data and data from other sources that will ensure that it complies with the data integrity rules I've set up. Using a lookup field is the simplest way I know of to do that. I could also import the data into new tables and use subselect queries to compare the values in the imported table to the allowable ones, but that leaves me with no simple way of correcting the errors, and I still need to copy and paste or append the data to the existing table when it's corrected. What advantages would that really have in my situation? And how could that possibly be more intuitive? I'm here looking for help with MY SITUATION, not to be your soapbox. I don't need a lecture but a couple suggestions would be nice. And don't worry, when all the data is finally brought together and I know it's accurate, I'll work on convincing my boss to let me convert that lookup to a simple foreign key, and the comboboxes I have in my forms will do their job. Getting to that point is my issue. |
#10
|
|||
|
|||
Lookup Field Issues
No insult intended.
.... but if you didn't want the folks here to use their brains and offer suggestions based on their experience, just say so. Sometimes folks just want to be told "what button to push" ... let us know. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "esn" wrote in message ... I have to say those are pretty condescending responses. I understand the value of forms for data entry and that really doesn't address my issue. Since I've been involved with this project all data entry has utilized forms in Access, but there is a mountain of preexisting data to deal with. The data is already entered and was imported from various sources - some of it was entered in excel and some of it was in a preexisting database that was quite a mess. Needless to say, this data is rife with errors thanks to my predecessors NOT grasping the value of forms. So I can A: import the data using an import function, which will exclude all records that don't fit my rules and leave no simple way of knowing which records were excluded, B: use an append query that will do exactly the same thing, or C: copy and paste the records so that I get a paste errors table, which I can then correct as needed. As for the primer on lookup fields, the info you've provided (If it's a lookup field then it just *appears* to contain text. It doesn't.)is incorrect. If the primary key of the row source is a text field, the data is stored as text. This is beneficial in my case because queries written on the data table can use the actual text as criteria without requiring a join to the lookup table. Since I'm building this database for multiple users with various levels of Access expertise this would be preferable to training them all in the finer points of multiple table queries (or doing all their work for them). And as far as critiques of lookup fields in general, if anyone has a better suggestion for how to limit values in a field to a list of 11,000 allowable values let me know. Beats typing "value1" or "value2" or "value3" ... 11,000 times in a validation rule. I tend to agree more with the response posted he http://improvingsoftware.com/2009/10...cess-are-evil/ than with most of the arguements against lookup fields in the link you've provided. It seems to me I've found a glitch in Access that there is no way to correct. If the primary key referenced in a lookup field has a text data type, the database will not check values against the lookup table when records are pasted (even if limit to list is set to yes). I tried everything I could think of to no avail, and eventually had to return the primary key to the ID field to get the behavior I'm looking for. Which means I get to do everyone's work for them. |
Thread Tools | |
Display Modes | |
|
|